(转)关于复合分区索引状态的研究

为了得到测试目的，我建立了一个range_list的复合分区表zrp_part。建表语句如下：

SQL> create table zrp_part (
2 region number(3),
3 proc_no number(2)
4 )
5 partition by range (region)
6 subpartition by list (proc_no)
7 subpartition template
8 ( subpartition proc_1 values(1),
9 subpartition proc_2 values(2),
10 subpartition proc_max values(DEFAULT)
11 )
12 ( partition zrp_part_010 values less than(20),
13 partition zrp_part_020 values less than (30),
14 partition zrp_part_max values less than (MAXVALUE)
15 );

表已创建。

SQL>
然后再建一个LOCAL索引
SQL> create index inx_zrp_test on zrp_part (region,proc_no)
2 LOCAL
3 (PARTITION zrp_part_010,
4 PARTITION zrp_part_020,
5 PARTITION zrp_part_max
6 );

索引已创建。

SQL>

查看此时的索引状态
1）dba_indexes 视图

SQL> select index_name,status from dba_indexes where index_name='INX_ZRP_TEST';

INX_ZRP_TEST N/A

2) dba_ind_partitions视图
SQL> select index_name,partition_name,status from user_ind_partitions where index_name='INX_ZRP_TEST';

INX_ZRP_TEST ZRP_PART_010 N/A
INX_ZRP_TEST ZRP_PART_020 N/A
INX_ZRP_TEST ZRP_PART_MAX N/A

3）dba_ind_subpartitions视图

SQL> select index_name ,partition_name,subpartition_name,status from user_ind_subpartitions where index_name='INX_ZRP_TEST';

INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_1 USABLE
INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_2 USABLE
INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_MAX USABLE
INX_ZRP_TEST ZRP_PART_020 ZRP_PART_020_PROC_1 USABLE
INX_ZRP_TEST ZRP_PART_020 ZRP_PART_020_PROC_2 USABLE
INX_ZRP_TEST ZRP_PART_020 ZRP_PART_020_PROC_MAX USABLE
INX_ZRP_TEST ZRP_PART_MAX ZRP_PART_MAX_PROC_1 USABLE
INX_ZRP_TEST ZRP_PART_MAX ZRP_PART_MAX_PROC_2 USABLE
INX_ZRP_TEST ZRP_PART_MAX ZRP_PART_MAX_PROC_MAX USABLE

SQL>

插入几条数据验证一下
SQL> insert into zrp_part values(10,1);

已创建 1 行。

SQL> insert into zrp_part values(10,2);

已创建 1 行。

SQL> insert into zrp_part values(20,1);

已创建 1 行。

SQL> insert into zrp_part values(20,2);

已创建 1 行。

SQL> commit;

提交完成。

提交完成。

SQL> select * from zrp_part;

10 1
10 2
20 1
20 2

SQL>
下面我们看看数据都存到哪个分区里去了（这是为了让后面的测试更有说服力）
SQL> select * from zrp_part partition(zrp_part_010);

10 1
10 2

SQL> select * from zrp_part partition(zrp_part_020);

20 1
20 2

SQL> select * from zrp_part partition(zrp_part_max);

未选定行

SQL>
SQL> select * from zrp_part subpartition (zrp_part_010_proc_1);

10 1

SQL> select * from zrp_part subpartition (zrp_part_010_proc_2);

10 2

SQL> select * from zrp_part subpartition (zrp_part_020_proc_1);

20 1

SQL> select * from zrp_part subpartition (zrp_part_020_proc_2);

20 2

SQL> select * from zrp_part subpartition (zrp_part_max_proc_1);

未选定行

SQL> select * from zrp_part subpartition (zrp_part_max_proc_2);

未选定行

SQL>

可以看到，我们的数据按照预期的分区分别插入到了正确的地方。

下面我们做一个数据检索，看看Oracle 的执行计划是如何的

SQL> select * from zrp_part where region=10 and proc_no=1;

SQL> select * from zrp_part where region=10 and proc_no=1;

执行计划
----------------------------------------------------------
Plan hash value: 61450464

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 26 | 1 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION LIST SINGLE| | 1 | 26 | 1 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX RANGE SCAN | INX_ZRP_TEST | 1 | 26 | 1 (0)| 00:00:01 | 1 | 1 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("REGION"=10 AND "PROC_NO"=1)

Note
-----
- dynamic sampling used for this statement

SQL>

可以看到此时用上了分区索引，并进行了索引范围扫描。

下面我手工将索引的状态修改为UNUSABLE，模拟索引破坏的情况（比如移动分区的情况）
SQL> alter index inx_zrp_test unusable;

索引已更改。

SQL>

再看索引的状态
1）dba_indexes 视图

SQL> select index_name,status from dba_indexes where index_name='INX_ZRP_TEST';

INX_ZRP_TEST N/A

2) dba_ind_partitions视图
SQL> select index_name,partition_name,status from user_ind_partitions where index_name='INX_ZRP_TEST';

INX_ZRP_TEST ZRP_PART_010 N/A
INX_ZRP_TEST ZRP_PART_020 N/A
INX_ZRP_TEST ZRP_PART_MAX N/A

3）dba_ind_subpartitions视图

SQL> select index_name ,partition_name,subpartition_name,status from user_ind_subpartitions where index_name='INX_ZRP_TEST';

INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_1 UNUSABLE
INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_2 UNUSABLE
INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_MAX UNUSABLE
INX_ZRP_TEST ZRP_PART_020 ZRP_PART_020_PROC_1 UNUSABLE
INX_ZRP_TEST ZRP_PART_020 ZRP_PART_020_PROC_2 UNUSABLE
INX_ZRP_TEST ZRP_PART_020 ZRP_PART_020_PROC_MAX UNUSABLE
INX_ZRP_TEST ZRP_PART_MAX ZRP_PART_MAX_PROC_1 UNUSABLE
INX_ZRP_TEST ZRP_PART_MAX ZRP_PART_MAX_PROC_2 UNUSABLE
INX_ZRP_TEST ZRP_PART_MAX ZRP_PART_MAX_PROC_MAX UNUSABLE

SQL>

再做数据检索的验证（还是刚才那个检索语句）

SQL> select * from zrp_part where region=10 and proc_no=1;

执行计划
----------------------------------------------------------
Plan hash value: 3999291197
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 27 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 26 | 27 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION LIST SINGLE| | 1 | 26 | 27 (0)| 00:00:01 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | ZRP_PART | 1 | 26 | 27 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("REGION"=10)

Note
-----
- dynamic sampling used for this statement

SQL>

很明显，此时只能是走全表扫描了。

下面依次按照子分区来修复索引
SQL> alter index INX_ZRP_TEST rebuild subpartition ZRP_PART_010_PROC_1;

索引已更改。

SQL> select * from zrp_part where region=10 and proc_no=1;

执行计划
----------------------------------------------------------
Plan hash value: 3999291197

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 27 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 26 | 27 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION LIST SINGLE| | 1 | 26 | 27 (0)| 00:00:01 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | ZRP_PART | 1 | 26 | 27 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("REGION"=10)

Note
-----
- dynamic sampling used for this statement

SQL>

SQL> select * from zrp_part subpartition (zrp_part_010_proc_1);
10 1

SQL> select index_name ,partition_name,subpartition_name,status from user_ind_subpartitions where index_name='INX_ZRP_TEST' and status='USABLE';

INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_1 USABLE

SQL>

SQL> alter index inx_zrp_test rebuild subpartition zrp_part_010_proc_2;

索引已更改。

SQL> select * from zrp_part where region=10 and proc_no=1;

执行计划
----------------------------------------------------------
Plan hash value: 3999291197

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 27 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 26 | 27 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION LIST SINGLE| | 1 | 26 | 27 (0)| 00:00:01 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | ZRP_PART | 1 | 26 | 27 (0)| 00:00:01 | 1 | 1 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("REGION"=10)

Note
-----
- dynamic sampling used for this statement

SQL> alter index inx_zrp_test rebuild subpartition zrp_part_010_proc_max;

索引已更改。

SQL> select * from zrp_part where region=10 and proc_no=1;

执行计划
----------------------------------------------------------
Plan hash value: 61450464

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 26 | 1 (0)| 00:00:01 | 1 | 1 |
| 2 | PARTITION LIST SINGLE| | 1 | 26 | 1 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX RANGE SCAN | INX_ZRP_TEST | 1 | 26 | 1 (0)| 00:00:01 | 1 | 1 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("REGION"=10 AND "PROC_NO"=1)

Note
-----
- dynamic sampling used for this statement

SQL>

SQL> select index_name ,partition_name,subpartition_name,status from user_ind_subpartitions where index_name='INX_ZRP_TEST' and status='USABLE';

INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_1 USABLE
INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_2 USABLE
INX_ZRP_TEST ZRP_PART_010 ZRP_PART_010_PROC_MAX USABLE

SQL>

到这个时候，第一个分区的所有子分区的索引全部rebuild 了，所以索引就用了，而其他分区的还不行，还需要修复后才可以。我就不写了。累死我了。

小结一下吧：根据测试的情况来看，复合分区索引和一般的分区索引性质是一样的，只不过大家访问的时候需要换成另外的视图而已了。

值得一提的是：
SQL> alter index inx_zrp_test rebuild partition zrp_part_010;
alter index inx_zrp_test rebuild partition zrp_part_010
*
第 1 行出现错误:
ORA-14287: 不能 REBUILD (重建) 组合范围分区的索引的分区
SQL>

请大家要注意。