【测试】大表改分区（交换分区+分区分离）

将大表分割成较小的分区可以改善表的维护、备份、恢复、事务及查询性能。

 

①建表：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.