将大表分割成较小的分区可以改善表的维护、备份、恢复、事务及查询性能。
①建表:test
SQL> create table test
2 (
3 nid number(10) constraint pk_test primary key,
4 idcard varchar2(18),
5 pdd date not null
6 );
Table created.
创建test表的索引:
SQL> create index idx_test on test(idcard);
Index created.
②建表test_p:
SQL> create table test_p
2 (
3 nid number(10) constraint pk_test_p primary key,idcard varchar2(18),
4 pdd date not null
5 )
6 partition by range (pdd)
7 (
8 partition part_maxvalue values less than (maxvalue)
9 );
Table created.
创建test_p索引:
SQL> create index idx_test_p on test_p(idcard) local;
Index created.
③交换分区:
SQL> alter table test_p exchange partition part_maxvalue with table test;
Table altered.
④锁定表(将新的数据库导入):
SQL> lock table test_p in EXCLUSIVE mode;
Table(s) Locked.
⑤分区分裂:
SQL> alter table test_p split partition part_maxvalue at (to_date('1940-01-01','yyyy-mm-dd')) into (partition part1940,partition part_maxvalue);
Table altered.
SQL> alter table test_p split partition part_maxvalue at (to_date('1960-01-01','yyyy-mm-dd')) into (partition part1960,partition part_maxvalue);
Table altered.
SQL> alter table test_p split partition part_maxvalue at (to_date('1980-01-01','yyyy-mm-dd')) into (partition part1980,partition part_maxvalue);
Table altered.
SQL> alter table test_p split partition part_maxvalue at (to_date('2000-01-01','yyyy-mm-dd')) into (partition part2000,partition part_maxvalue);
Table altered.
SQL> alter table test_p split partition part_maxvalue at (to_date('2020-01-01','yyyy-mm-dd')) into (partition part2020,partition part_maxvalue);
Table altered.
⑥重建索引和主键(交换分区后索引和主键已失效):
SQL> alter index idx_test_p rebuild partition part1940 tablespace users nologging online;
Index altered.
SQL> alter index idx_test_p rebuild partition part1960 tablespace users nologging online;
Index altered.
SQL> alter index idx_test_p rebuild partition part1980 tablespace users nologging online;
Index altered.
SQL> alter index idx_test_p rebuild partition part2000 tablespace users nologging online;
Index altered.
SQL> alter index idx_test_p rebuild partition part2020 tablespace users nologging online;
Index altered.
SQL> alter index pk_test_p rebuild tablespace users nologging online;
Index altered.