Managing Partitioned Tables and Indexes

分区表及其索引......

SQL> create table t_part(object_id int,object_name varchar2(30),create_date date )
2 partition by range(create_date)
3 (partition p1 values less than(to_date('20010101','yyyymmdd')),
4 partition p2 values less than(to_date('20020101','yyyymmdd')),
5 partition p3 values less than(maxvalue));

Table created.

Elapsed: 00:00:00.04
SQL> desc t_part;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
OBJECT_ID NUMBER(38)
OBJECT_NAME VARCHAR2(30)
CREATE_DATE DATE

SQL> insert into t_part
2 values(1001,'aaaa',sysdate - 365*7);

1 row created.

Elapsed: 00:00:00.01
SQL> insert into t_part
2 values(1002,'bbbb',sysdate - 365*7);

1 row created.

Elapsed: 00:00:00.00
SQL> insert into t_part
2 values(2001,'cccc',sysdate -365*6);

1 row created.

Elapsed: 00:00:00.00
SQL> insert into t_part
2 values(3001,'dddd',sysdate);

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select * from t_part;

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
1001 aaaa 2000-03-21 11:46:59
1002 bbbb 2000-03-21 11:47:23
2001 cccc 2001-03-21 11:48:08
3001 dddd 2007-03-20 11:48:25

Elapsed: 00:00:00.00

SQL> select * from t_part partition(p1);

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
1001 aaaa 2000-03-21 11:46:59
1002 bbbb 2000-03-21 11:47:23

Elapsed: 00:00:00.00

SQL> select * from t_part partition(p2);

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
2001 cccc 2001-03-21 11:48:08

Elapsed: 00:00:00.00
SQL> select * from t_part partition(p3);

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
3001 dddd 2007-03-20 11:48:25

Elapsed: 00:00:00.00
SQL> alter table t_part drop partition p3;

Table altered.

Elapsed: 00:00:00.07
SQL> select * from t_part;

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
1001 aaaa 2000-03-21 11:46:59
1002 bbbb 2000-03-21 11:47:23
2001 cccc 2001-03-21 11:48:08

Elapsed: 00:00:00.00
SQL> alter table t_part add partition p3 values less than(to_date('20030101','yyyymmdd'));

Table altered.

Elapsed: 00:00:00.02
SQL> insert into t_part values('3001','dddd',sysdate -365*5);

1 row created.

Elapsed: 00:00:00.02
SQL> insert into t_part values('3002','eeee',sysdate - 365*5);

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select * from t_part;

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
1001 aaaa 2000-03-21 11:46:59
1002 bbbb 2000-03-21 11:47:23
2001 cccc 2001-03-21 11:48:08
3001 dddd 2002-03-21 13:37:53
3002 eeee 2002-03-21 13:38:29

Elapsed: 00:00:00.00
SQL> select * from t_part partition(p3);

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
3001 dddd 2002-03-21 13:37:53
3002 eeee 2002-03-21 13:38:29

Elapsed: 00:00:00.00
Exchanging Partitions

SQL> create table t_part_test as select * from t_part where rownum <1;

Table created.

Elapsed: 00:00:00.02
SQL> alter table t_part exchange partition p3 with t_part_test;
alter table t_part exchange partition p3 with t_part_test
*
ERROR at line 1:
ORA-00966: missing TABLE keyword

Elapsed: 00:00:00.00
SQL> alter table t_part exchange partition p3 with table t_part_test;

Table altered.

Elapsed: 00:00:00.00
SQL> select * from t_part_test;

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
3001 dddd 2002-03-21 13:37:53
3002 eeee 2002-03-21 13:38:29

Elapsed: 00:00:00.00

Merging Range Partitions
SQL> select * from t_part partition(p1);

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
1001 aaaa 2000-03-21 11:46:59
1002 bbbb 2000-03-21 11:47:23

Elapsed: 00:00:00.00
SQL> select * from t_part partition(p2);

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
2001 cccc 2001-03-21 11:48:08

Elapsed: 00:00:00.00
SQL> select * from t_part partition(p3);

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
3001 eeee 2002-03-21 13:56:52
3002 ffff 2002-03-21 13:56:54
3003 gggg 2002-03-21 13:56:55

Elapsed: 00:00:00.00
SQL> alter table t_part merge partitions p1,p2 into partition p2;

Table altered.

Elapsed: 00:00:00.02
SQL> select * from t_part partition(p1);
select * from t_part partition(p1)
*
ERROR at line 1:
ORA-02149: Specified partition does not exist

Elapsed: 00:00:00.00
SQL> select * from t_part partition(p2);

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
1001 aaaa 2000-03-21 11:46:59
1002 bbbb 2000-03-21 11:47:23
2001 cccc 2001-03-21 11:48:08

Elapsed: 00:00:00.00

Renaming Partitions
SQL> alter table t_part rename partition p2 to p1;

Table altered.

Elapsed: 00:00:00.00
SQL> select * from t_part partition(p1);

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
1001 aaaa 2000-03-21 11:46:59
1002 bbbb 2000-03-21 11:47:23
2001 cccc 2001-03-21 11:48:08

Elapsed: 00:00:00.00
SQL> alter table t_part rename partition p3 to p2;

Table altered.

Elapsed: 00:00:00.00
SQL> select * from t_part partition(p2);

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
3001 eeee 2002-03-21 13:56:52
3002 ffff 2002-03-21 13:56:54
3003 gggg 2002-03-21 13:56:55

Elapsed: 00:00:00.00

Splitting a Partition of a Range-Partitioned Table
SQL> select * from t_part partition (p1);

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
1001 aaaa 2000-02-20 11:46:59
1002 bbbb 2000-03-21 11:47:23
2001 cccc 2001-03-21 11:48:08

Elapsed: 00:00:00.00
SQL> alter table t_part split partition p1 at(to_date('200103','yyyymm'))
2 into (partition p11 ,
3 partition p12 );

Table altered.

Elapsed: 00:00:00.02
SQL> select * from t_part partition (p11);

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
1001 aaaa 2000-02-20 11:46:59
1002 bbbb 2000-03-21 11:47:23

Elapsed: 00:00:00.00
SQL> select * from t_part partition(p12);

OBJECT_ID OBJECT_NAME CREATE_DATE
---------- ------------------------------ -------------------
2001 cccc 2001-03-21 11:48:08

Elapsed: 00:00:00.00
Truncating Partitions
SQL> alter table t_part truncate partition p12;

Table truncated.

Elapsed: 00:00:00.00
SQL> select * from t_part partition(p12);

no rows selected

Elapsed: 00:00:00.00

Global&Local index

SQL> create index idx_part_global on t_part(create_date)
2 global partition by range(create_date)
3 (partition p1 values less than(to_date('20020101','yyyymmdd')),
4 partition p2 values less than(maxvalue))
5 ;

Index created.
SQL> drop index idx_part_global;

Index dropped.

Elapsed: 00:00:00.00
Elapsed: 00:00:00.00
SQL> create index idx_part_local on t_part(create_date)
2 local(partition p11,
3 partition p12,
4 partition p2);

Index created.

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

转载于:http://blog.itpub.net/45259/viewspace-134915/