单个分区索引失效导致绑定变量查询无法使用索引

一个客户碰到的问题，由于分区维护操作，导致个别分区对应的索引处于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的语句则不会报错。