單個分割槽索引失效導致繫結變數查詢無法使用索引
一個客戶碰到的問題，由於分割槽維護操作，導致個別分割槽對應的索引處於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的語句則不會報錯。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-734264/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 分割槽表分割槽索引查詢效率探究索引
- 索引失效系列——繫結變數引起的peeking索引變數
- ddl 導致分割槽表全域性索引unusable索引
- 分割槽表 全域性索引與本地索引失效測試索引
- 分割槽表、分割槽索引和全域性索引部分總結索引
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- SQL SERVER中什麼情況會導致索引查詢變成索引掃描SQLServer索引
- Oracle分割槽表全域性索引新增分割槽時不會失效Oracle索引
- 關於 Oracle 分割槽索引的失效和重建Oracle索引
- oracle 索引使用及索引失效總結Oracle索引
- Oracle分割槽之五：建立分割槽索引總結Oracle索引
- Mysql 會導致索引失效的情況MySql索引
- 字元校驗集問題導致索引無法正常使用字元索引
- 全域性分割槽索引和區域性分割槽索引索引
- 繫結變數，組合查詢方式，導致CBO錯誤一例變數
- SQL優化案例-分割槽索引之無字首索引（六）SQL優化索引
- Oracle優化案例-分割槽索引之無字首索引（六）Oracle優化索引
- oracle索引詳解 分割槽索引Oracle索引
- 【學習筆記】分割槽表和分割槽索引——管理索引分割槽（四）筆記索引
- Oracle索引分割槽Oracle索引
- Oracle索引或這類索引的分割槽處於不可用狀態 查詢Oracle索引
- 全面學習分割槽表及分割槽索引(17)--其它索引分割槽管理操作索引
- Oracle分割槽表及分割槽索引Oracle索引
- 轉個分割槽表Local索引Rebuild的總結索引Rebuild
- SQL最佳化案例-分割槽索引之無字首索引（六）SQL索引
- ORACLE 分割槽索引UNUSABLE導致的DML操作失敗引起的血案Oracle索引
- oracle分割槽表的常規操作導致對索引的影響Oracle索引
- 建立索引後，速度變快原因?以及索引失效總結索引
- 全面學習分割槽表及分割槽索引(16)--增加和刪除索引分割槽索引
- 查詢繫結變數的值變數
- oracle 查詢未使用繫結變數的sqlOracle變數SQL
- MySQL 分割槽建索引MySql索引
- oracle分割槽索引（二）Oracle索引
- oracle分割槽索引（一）Oracle索引
- Laravel 對於 Mysql 欄位string型別查詢，當使用數字對這個欄位進行查詢，PHP弱型別語言導致索引失效LaravelMySql型別PHP索引
- rebuild分割槽表分割槽索引的方法Rebuild索引
- 分割槽表及分割槽索引建立示例索引
- Oracle隱式型別轉換導致索引失效Oracle型別索引