如果一张表数据量太大，不好管理，进行优化：方案，即大表改分区（交换分区+分区分裂）
步骤1，建立与大表完全相同表结构的分区表
步骤2，将大表与分区表进行交换分区操作
步骤3，将分区表进行分区分裂操作
步骤4，重建分区表的分区索引及主键索引
步骤5，重建大表的全局索引及主键索引
实验：
1.建立一个大表：
SCOTT@ORA11GR2>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.
——建立大表的全局索引：
SCOTT@ORA11GR2>create index idx_test on test(idcard);
Index created.
——插入大量数据：
SCOTT@ORA11GR2>insert into test select rownum,rpad(rownum,18,'0'),add_months(sysdate,0-rownum) from dual connect by rownum<=1000;
1000 rows created.
2.创建与大表表结构相同的分区表：
SCOTT@ORA11GR2>create table test_p
2 (
3 nid number(10) constraint pk_test_p primary key,
4 idcard varchar2(18),
5 pdd date not null
6 )
7 partition by range (pdd)
8 (
9 partition part_maxvalue values less than (maxvalue)
10 );
Table created.
——建立分区表的本地索引：
SCOTT@ORA11GR2>create index idx_test_p on test_p(idcard) local;
Index created.
3.大表与分区表进行交换分区操作：
SCOTT@ORA11GR2>alter table test_p exchange partition part_maxvalue with table test;
Table altered.
——查看索引状态：
SCOTT@ORA11GR2>select INDEX_NAME,STATUS from user_indexes where index_name='IDX_TEST_P';
INDEX_NAME STATUS
------------------------------ --------
IDX_TEST_P N/A
（指的是分区表索引，如果单纯的只是交换分区，则对于分区索引是不需要重建的；相反，全局索引需要重建）
SCOTT@ORA11GR2>select INDEX_NAME,STATUS from user_indexes where index_name='IDX_TEST';
INDEX_NAME STATUS
------------------------------ --------
IDX_TEST UNUSABLE （大表的索引失效）
4.将分区表置于独占模式进行操作：
SCOTT@ORA11GR2>lock table test_p in EXCLUSIVE mode;
Table(s) Locked.
——查询大表数据，因为已进行了交换分区，索引数据就变为0行记录；
SCOTT@ORA11GR2>select count(*) from test;
COUNT(*)
----------
0
——查询分区表，因为已进行了交换分区，索引数据就变为有1000行记录了
SCOTT@ORA11GR2>select count(*) from test_p;
COUNT(*)
----------
1000
5.对分区表test_p（范围分区表）进行分裂分区操作：
SCOTT@ORA11GR2>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.
SCOTT@ORA11GR2>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.
SCOTT@ORA11GR2>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.
SCOTT@ORA11GR2>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.
SCOTT@ORA11GR2>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.
6.重建分区表本地索引（因为已经进行了分裂分区操作，一变多了）
SCOTT@ORA11GR2>alter index idx_test_p rebuild partition part1940 tablespace users nologging online;
Index altered.
SCOTT@ORA11GR2>alter index idx_test_p rebuild partition part1960 tablespace users nologging online;
Index altered.
SCOTT@ORA11GR2>alter index idx_test_p rebuild partition part1980 tablespace users nologging online;
Index altered.
SCOTT@ORA11GR2>alter index idx_test_p rebuild partition part2000 tablespace users nologging online;
Index altered.
SCOTT@ORA11GR2>alter index idx_test_p rebuild partition part2020 tablespace users nologging online;
Index altered.
7.重建分区表的主键索引：（因为主键会自动创建索引）
SCOTT@ORA11GR2>alter index pk_test_p rebuild tablespace users nologging online;
Index altered.
8.重建大表的全局索引和主键索引：
SCOTT@ORA11GR2>alter index idx_test rebuild tablespace users nologging online;
Index altered.
SCOTT@ORA11GR2>alter index pk_test rebuild tablespace users nologging online;
Index altered.
——查询验证索引的有效性：
SCOTT@ORA11GR2>select INDEX_NAME,STATUS from user_indexes where index_name='IDX_TEST';
INDEX_NAME STATUS
------------------------------ --------
IDX_TEST VALID 大表索引有效
SCOTT@ORA11GR2>select INDEX_NAME,STATUS from user_indexes where index_name='IDX_TEST_P';
INDEX_NAME STATUS
------------------------------ --------
IDX_TEST_P N/A 分区表本地索引有效
9.查询验证：
分区表：
SCOTT@ORA11GR2>select count(*) from test_p partition (part1940);
COUNT(*)
----------
79
SCOTT@ORA11GR2>select count(*) from test_p partition (part1960);
COUNT(*)
----------
240
SCOTT@ORA11GR2>select count(*) from test_p partition (part1980);
COUNT(*)
----------
240
SCOTT@ORA11GR2>select count(*) from test_p partition (part2000);
COUNT(*)
----------
240
SCOTT@ORA11GR2>select count(*) from test_p partition (part2020);
COUNT(*)
----------
201
SCOTT@ORA11GR2>select 79+240+240+240+201 from dual;
79+240+240+240+201
------------------
1000
大表：
SCOTT@ORA11GR2>select * from test;
no rows selected