一个客户碰到的问题，由于分区维护操作，导致个别分区对应的索引处于UNUSABLE状态，最终导致基于绑定变量的查询无法利用索引。
通过一个具体的例子来说明这个问题：
SQL> CREATE TABLE t_part 2 (id NUMBER, 3 name varchar2(30)) 4 partition BY range (id) 5 (partition p1 VALUES less than (10), 6 partition p2 VALUES less than (20), 7 partition pmax VALUES less than (maxvalue)); TABLE created. SQL> CREATE INDEX ind_t_part_id ON t_part(id) LOCAL; INDEX created. SQL> INSERT INTO t_part 2 SELECT rownum, object_name 3 FROM user_objects; 94 ROWS created. SQL> commit; Commit complete. SQL> EXEC dbms_stats.gather_table_stats(USER, 'T_PART', cascade => TRUE) PL/SQL PROCEDURE successfully completed. SQL> SELECT index_name, partition_name, STATUS 2 FROM user_ind_partitions 3 WHERE index_name = 'IND_T_PART_ID'; INDEX_NAME PARTITION_NAME STATUS ------------------------------ ------------------------------ -------- IND_T_PART_ID P1 USABLE IND_T_PART_ID P2 USABLE IND_T_PART_ID PMAX USABLE |
创建分区表后，分别采用硬编码和绑定变量的方式进行查询：
SQL> var v_id NUMBER SQL> EXEC :v_id := 5 PL/SQL PROCEDURE successfully completed. SQL> SET autot ON EXP SQL> SELECT * FROM t_part WHERE id = 5; ID NAME ---------- ------------------------------ 5 WRH$_ACTIVE_SESSION_HISTORY Execution Plan ---------------------------------------------------------- Plan hash VALUE: 4087175928 -------------------------------------------------------------------------------------------- |Id|Operation |Name |ROWS|Bytes|Cost|TIME |Pstart|Pstop| -------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 1| 31| 2|00:00:01| | | | 1| PARTITION RANGE SINGLE | | 1| 31| 2|00:00:01| 1 | 1| | 2| TABLE ACCESS BY LOCAL INDEX ROWID|T_PART | 1| 31| 2|00:00:01| 1 | 1| |*3| INDEX RANGE SCAN |IND_T_PART_ID| 1| | 1|00:00:01| 1 | 1| -------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 3 - access("ID"=5) SQL> SELECT * FROM t_part WHERE id = :v_id; ID NAME ---------- ------------------------------ 5 WRH$_ACTIVE_SESSION_HISTORY Execution Plan ---------------------------------------------------------- Plan hash VALUE: 2089936139 -------------------------------------------------------------------------------------------- |Id|Operation |Name |ROWS|Bytes|Cost|TIME |Pstart|Pstop| -------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 1| 17| 2|00:00:01| | | | 1| PARTITION RANGE SINGLE | | 1| 17| 2|00:00:01| KEY | KEY| | 2| TABLE ACCESS BY LOCAL INDEX ROWID|T_PART | 1| 17| 2|00:00:01| KEY | KEY| |*3| INDEX RANGE SCAN |IND_T_PART_ID| 1| | 1|00:00:01| KEY | KEY| -------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 3 - access("ID"=TO_NUMBER(:V_ID)) |
无论采用那种方式，Oracle都会选择分区索引扫描的执行计划。
下面MOVE一个查询并不会访问的分区，使其索引状态变为UNUSABLE：
SQL> ALTER TABLE t_part move partition p2; TABLE altered. SQL> SET autot off SQL> SELECT index_name, partition_name, STATUS 2 FROM user_ind_partitions 3 WHERE index_name = 'IND_T_PART_ID'; INDEX_NAME PARTITION_NAME STATUS ------------------------------ ------------------------------ -------- IND_T_PART_ID P1 USABLE IND_T_PART_ID P2 UNUSABLE IND_T_PART_ID PMAX USABLE SQL> SET autot ON EXP SQL> SELECT * FROM t_part WHERE id = 5; ID NAME ---------- ------------------------------ 5 WRH$_ACTIVE_SESSION_HISTORY Execution Plan ---------------------------------------------------------- Plan hash VALUE: 4087175928 -------------------------------------------------------------------------------------------- |Id|Operation |Name |ROWS|Bytes|Cost|TIME |Pstart|Pstop| -------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 1| 31| 2|00:00:01| | | | 1| PARTITION RANGE SINGLE | | 1| 31| 2|00:00:01| 1 | 1| | 2| TABLE ACCESS BY LOCAL INDEX ROWID|T_PART | 1| 31| 2|00:00:01| 1 | 1| |*3| INDEX RANGE SCAN |IND_T_PART_ID| 1| | 1|00:00:01| 1 | 1| -------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 3 - access("ID"=5) SQL> SELECT * FROM t_part WHERE id = :v_id; ID NAME ---------- ------------------------------ 5 WRH$_ACTIVE_SESSION_HISTORY Execution Plan ---------------------------------------------------------- Plan hash VALUE: 1818654859 -------------------------------------------------------------------------------------------- | Id| Operation | Name | ROWS | Bytes |Cost(%CPU)| TIME | Pstart| Pstop | -------------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 | | | | 1| PARTITION RANGE SINGLE| | 1 | 17 | 2 (0)| 00:00:01 | KEY | KEY | |* 2| TABLE ACCESS FULL | T_PART | 1 | 17 | 2 (0)| 00:00:01 | KEY | KEY | -------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 2 - FILTER("ID"=TO_NUMBER(:V_ID)) |
可以看到，对应非绑定变量方式，Oracle是可以明确定位到要访问的分区，因此SQL执行计划不受影响，仍然是索引扫描。而对于绑定变量的方式则不同，由于这个执行计划对于任何一个输入值都要采用相同的计划，因此Oracle无法判断一个查询是否会访问分区索引UNUSABLE的分区，所以Oracle对于绑定变量的查询采用了单分区的全表扫描执行计划。
为了解决这个问题，除了REBUILD失效的分区外，还可以采用HINT的方式，强制Oracle选择索引扫描的执行计划：
SQL> SELECT /*+ index(t_part ind_t_part_id) */ * FROM t_part WHERE id = :v_id; ID NAME ---------- ------------------------------ 5 WRH$_ACTIVE_SESSION_HISTORY Execution Plan ---------------------------------------------------------- Plan hash VALUE: 2089936139 -------------------------------------------------------------------------------------------- |Id|Operation |Name |ROWS|Bytes|Cost|TIME |Pstart|Pstop| -------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 1| 17| 2|00:00:01| | | | 1| PARTITION RANGE SINGLE | | 1| 17| 2|00:00:01| KEY | KEY| | 2| TABLE ACCESS BY LOCAL INDEX ROWID|T_PART | 1| 17| 2|00:00:01| KEY | KEY| |*3| INDEX RANGE SCAN |IND_T_PART_ID| 1| | 1|00:00:01| KEY | KEY| -------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 3 - access("ID"=TO_NUMBER(:V_ID)) SQL> EXEC :v_id := 15 PL/SQL PROCEDURE successfully completed. SQL> SELECT /*+ index(t_part ind_t_part_id) */ * FROM t_part WHERE id = :v_id; SELECT /*+ index(t_part ind_t_part_id) */ * FROM t_part WHERE id = :v_id * ERROR at line 1: ORA-01502: INDEX 'TEST.IND_T_PART_ID' OR partition OF such INDEX IS IN unusable state SQL> SELECT * FROM t_part WHERE id = :v_id; ID NAME ---------- ------------------------------ 15 WRH$_ACTIVE_SESSION_HISTORY_PK Execution Plan ---------------------------------------------------------- Plan hash VALUE: 1818654859 -------------------------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes |Cost(%CPU)| TIME |Pstart| Pstop | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 1 | 17 | 2 (0)| 00:00:01 | KEY | KEY | |* 2 | TABLE ACCESS FULL | T_PART | 1 | 17 | 2 (0)| 00:00:01 | KEY | KEY | -------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 2 - FILTER("ID"=TO_NUMBER(:V_ID)) |
虽然使用HINT可以让Oracle强制索引扫描，但是如果绑定变量的值指向失效的索引分区，则会导致执行报错。而默认的不使用HINT的语句则不会报错。