基於COST優化，oracle什麼情況不走索引

今天具體開發的同事提出這樣一個問題

SQL> set autotrace traceonly explain
SQL> select * from xxx  where aac001 = '111';

Execution Plan
----------------------------------------------------------

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    55 | 22275 |     3   (0)|
|   1 |  TABLE ACCESS FULL| xxx |    55 | 22275 |     3   (0)|
---------------------------------------------------------------

Note
-----

在xxx.aac001上是存在索引的，但為什麼不走索引呢？

接到這個問題，首先排除該索引是否失效了

SQL> select index_name,status from user_indexes where index_name = 'IDX_XXX_AAC
001';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_XXX_AAC001                VALID

答案是否定的。接著對錶和索引進行了分析

analyze index idx_xxx_aac001 validate structure;

analyze table xxx  delete  statistics;

analyze table xxx compute  statistics;

仍然是全表掃描，奇怪了，隱式轉換不可能，開發人員還不至於犯如此錯誤

因為是開發庫，數量變化可能沒有規律，所以重新rebuild一下索引吧

SQL> set autotrace traceonly explain
SQL> select * from xxx where aac001 = '111';

Execution Plan
----------------------------------------------------------

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    65 | 26260 |     3   (0)|
|   1 |  TABLE ACCESS FULL| xxx |    65 | 26260 |     3   (0)|
---------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

SQL>

沒有起作用

不是組合索引，該列也不可能為空，那是什麼原因呢

SQL> select count(*) from xxx;

  COUNT(*)
----------
        68

 

SQL> select count(*) from xxx where aac001 = '111';

  COUNT(*)
----------
        65

 

換個人試一下

SQL> explain plan for select * from xxx  where aac001 = '123';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

----

| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%C

PU)|

--------------------------------------------------------------------------------

----

|   0 | SELECT STATEMENT            |                 |     1 |   404 |     2

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

(0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| xxx           |     1 |   404 |     2
(0)|

|   2 |   INDEX RANGE SCAN          | IDX_xxx_AAC001 |     1 |       |     1
(0)|

--------------------------------------------------------------------------------

----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

12 rows selected.

答案出來了，就是這個問題，由於是開發庫，沒有真實的資料，所有資料均為開發人員手動造的，辛苦了

以前總是在說，基於COST優化：表資料量小，全表掃描比走索引效率高
很簡單，你的表資料可能佔用一個塊，索引也是佔用一個塊，
如果全表掃描，只需讀一個塊，而使用索引則要讀兩個塊，
顯然使用索引成本更高，因此oracle決定使用全表掃描。

但真的碰到了，還是很難想到。通過這次，oracle優化器的智慧讓我折服了，對其印象更加深刻了，並不是所有情況使用索引都會加快查詢速度，full scan table 有時會更快，尤其是當查詢的資料量佔整個表的比重較大時。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13726712/viewspace-680555/，如需轉載，請註明出處，否則將追究法律責任。