创建分区表及分区索引(本地)

创建表空间：

SQL> create tablespace myTableSpace_1 datafile '/dat/DBData/oradata/NACEC/myTableSpace1.dbf' size 100m;

Tablespace created.

SQL> create tablespace myTableSpace_2 datafile '/dat/DBData/oradata/NACEC/myTableSpace2.dbf' size 100m;

Tablespace created.

SQL> create tablespace myTableSpace_3 datafile '/dat/DBData/oradata/NACEC/myTableSpace3.dbf' size 100m;

Tablespace created.

创建分区：

SQL>  create table MyTest

 (

        id number,

        name nvarchar2(100)

 )

 partition by range(id)

 (

  partition T1 values less than (100) tablespace myTableSpace_1,

  partition T2 values less than (1000) tablespace myTableSpace_2,

  partition T3 values less than (maxvalue) tablespace myTableSpace_3);

Table created.

插入数据：

SQL> insert into MyTest values (10,'f1111');

  insert into MyTest values (20,'f1112');

  insert into MyTest values (100,'f1112');

  insert into MyTest values (200,'f1112');

  insert into MyTest values (1001,'f1112');

SQL> commit;

Commit complete.

查询分区表 ：

SQL>  select * from MyTest;

        ID

----------

NAME

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

        10

f1111

        20

f1112

       100

f1112

        ID

----------

NAME

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

       200

f1112

SQL>  select * from myTest partition(T1);

        ID

----------

NAME

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

        10

f1111

        20

f1112

SQL>  select * from myTest partition(T2);

        ID

----------

NAME

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

       100

f1112

       200

f1112

SQL> select * from myTest partition(T3);

        ID

----------

NAME

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

      1001

f1112

删除一个有表分区，同时些表分区的数据也将删除

ALTER TABLE myTest DROP PARTITION T3;

插入一个表分区，插入时必须是在最大的表分区住下插入

alter table myTest add partition T3 values less than (10000) tablespace myTableSpace_3;

创建一个Local索引，注意这里可以将不同分区的索引指定创建到不同的表空间，本次测试和原来的表空间相同：

SQL> CREATE INDEX mytest_idx ON mytest (id) LOCAL

(partition T1 tablespace myTableSpace_1,

  partition T2 tablespace myTableSpace_2,

  partition T3 tablespace myTableSpace_3);

SQL> set autotrace traceonly

SQL> select * from mytest where id=20;

Execution Plan

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

Plan hash value: 65850102

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

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

| Id  | Operation                          | Name       | Rows  | Bytes | Cost (

%CPU)| Time     | Pstart| Pstop |

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

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

|   0 | SELECT STATEMENT                   |            |     1 |   165 |     3

  (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE SINGLE            |            |     1 |   165 |     3

  (0)| 00:00:01 |     1 |     1 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| MYTEST     |     1 |   165 |     3

  (0)| 00:00:01 |     1 |     1 |

|*  3 |    INDEX RANGE SCAN                | MYTEST_IDX |     1 |       |     2

  (0)| 00:00:01 |     1 |     1 |

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

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

Predicate Information (identified by operation id):

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

   3 - access("ID"=20)

Note

-----

   - dynamic sampling used for this statement

Statistics

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

          9  recursive calls

          0  db block gets

         29  consistent gets

          0  physical reads

          0  redo size

        465  bytes sent via SQL*Net to client

        400  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31448824/viewspace-2137541/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31448824/viewspace-2137541/