【性能优化】大表改分区（交换分区+分区分裂）

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

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

转载于:http://blog.itpub.net/31397003/viewspace-2126146/