【IT168 技术】作者看到有网友问关于符复合区索引状态的探索，引起了兴趣，因为到目前为止，作者用到的复合分区的情况比较少。遂测试了一下。为了得到测试目的，作者建立了一个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>
请大家要注意。
