一個客戶碰到的問題，由于分區(qū)維護操作，導致個別分區(qū)對應的索引處于UNUSABLE狀態(tài)，最終導致基于綁定變量的查詢無法利用索引。
通過一個具體的例子來說明這個問題：
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
創(chuàng)建分區(qū)表后，分別采用硬編碼和綁定變量的方式進行查詢：
SQL> var v_id number
SQL> exec :v_id := 5
PL/SQL procedure successfully tb 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都會選擇分區(qū)索引掃描的執(zhí)行計劃。
下面MOVE一個查詢并不會訪問的分區(qū)，使其索引狀態(tài)變?yōu)閁NUSABLE：
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是可以明確定位到要訪問的分區(qū)，因此SQL執(zhí)行計劃不受影響，仍然是索引掃描。而對于綁定變量的方式則不同，由于這個執(zhí)行計劃對于任何一個輸入值都要采用相同的計劃，因此Oracle無法判斷一個查詢是否會訪問分區(qū)索引UNUSABLE的分區(qū)，所以Oracle對于綁定變量的查詢采用了單分區(qū)的全表掃描執(zhí)行計劃。
為了解決這個問題，除了REBUILD失效的分區(qū)外，還可以采用HINT的方式，強制Oracle選擇索引掃描的執(zhí)行計劃：
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強制索引掃描，但是如果綁定變量的值指向失效的索引分區(qū)，則會導致執(zhí)行報錯。而默認的不使用HINT的語句則不會報錯。