SQL> show user

USER 为 "SYS"

SQL> create tablespace dept1

  2  datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLE\dept1.dbf' size 2M

  3  extent management local uniform. size 256k;

表空间已创建。

SQL> create tablespace dept2

  2  datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLE\dept2.dbf' size 2M

  3  extent management local uniform. size 256k;

表空间已创建。

SQL> create tablespace dept3

  2  datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLE\dept3.dbf' size 2M

  3  extent management local uniform. size 256k;

表空间已创建。

SQL> select tablespace_name,extent_management,allocation_type

  2  from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO

------------------------------ ---------- ---------

SYSTEM                         LOCAL      SYSTEM

UNDOTBS1                       LOCAL      SYSTEM

SYSAUX                         LOCAL      SYSTEM

TEMP                           LOCAL      UNIFORM

USERS                          LOCAL      SYSTEM

EXAMPLE                        LOCAL      SYSTEM

PERFSTAT                       LOCAL      SYSTEM

MY                             LOCAL      UNIFORM

DEPT1                          LOCAL      UNIFORM

DEPT2                          LOCAL      UNIFORM

DEPT3                          LOCAL      UNIFORM

SQL> conn scott/tiger

已连接。

SQL> create table dept_pt

  2  (deptno number(2),

  3   deptname varchar2(30))

  4   partition by range(deptno)

  5  (partition d1 values less than(10) tablespace dept1,

  6   partition d2 values less than(20) tablespace dept2,

  7   partition d3 values less than(maxvalue) tablespace dept3);

表已创建。

SQL> insert into dept_pt values(1,'ADMIN');

已创建 1 行。

SQL> insert into dept_pt values(7,'MGMT');

已创建 1 行。

SQL> insert into dept_pt values(10,'MANUF');

已创建 1 行。

SQL> insert into dept_pt values(15,'ACCT');

已创建 1 行。

SQL> insert into dept_pt values(22,'SALES');

已创建 1 行。

SQL> commit;

提交完成。

当从dept_pt表中查询时，这个表仍可以被看成一个单独的表：

SQL> select * from dept_pt;

    DEPTNO DEPTNAME

---------- ------------------------------

         1 ADMIN

         7 MGMT

        10 MANUF

        15 ACCT

        22 SALES

接下来分别查询每个分区，只从一个分区中选取数据并且只访问一个分区

SQL> select * from dept_pt partition(d1);

    DEPTNO DEPTNAME

---------- ------------------------------

         1 ADMIN

         7 MGMT

SQL> select * from dept_pt partition(d2);

    DEPTNO DEPTNAME

---------- ------------------------------

        10 MANUF

        15 ACCT

SQL> select * from dept_pt partition(d3);

    DEPTNO DEPTNAME

---------- ------------------------------

        22 SALES

SQL> select * from dept_pt partition where deptno=22;

    DEPTNO DEPTNAME

---------- ------------------------------

        22 SALES

看一下上面一条语句的执行计划：

SQL> explain plan for select * from dept_pt partition where deptno=22;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1009190036

--------------------------------------------------------------------------------

------------------

| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time

 | Pstart| Pstop |

--------------------------------------------------------------------------------

------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |         |     1 |    30 |     9   (0)| 00:00:01

 |       |       |

|   1 |  PARTITION RANGE SINGLE|         |     1 |    30 |     9   (0)| 00:00:01

 |     3 |     3 |

|*  2 |   TABLE ACCESS FULL    | DEPT_PT |     1 |    30 |     9   (0)| 00:00:01

 |     3 |     3 |

--------------------------------------------------------------------------------

------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("DEPTNO"=22)

Note

-----

   - dynamic sampling used for this statement

已选择18行。

注意：为了最大化对一个较大表的磁盘I/O，应该把表分割在多个分区上，这些分区应该放置在不同的物理磁盘上。