誰動了我的索引(二)

接上文http://boylook.itpub.net/post/43144/520539；
嗯，自從有了上次的經驗，實驗時建表都要把rowid拉進來，排除幹擾：
SQL> create table t2(ctn varchar2(10),num number,r rowid);

Table created.

SQL> insert into t2 select rownum,rownum,r from t1 where rownum <10001;

10000 rows created.

SQL> commit;

Commit complete.

SQL> create index idx_t2_ctn on t2(ctn);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T2',cascade=>true);

PL/SQL procedure successfully completed.

哼哼，索引建好了，表也分析好了，在看看結果集如何。
SQL> select count(*) from t2 where ctn like '987%';

COUNT(*)
----------
11

SQL> select count(*) from t2 where ctn like '%987';

COUNT(*)
----------
10
很好，萬事俱備了，索引啊索引，就看你的了！我看這次誰來動你

case 4：
關鍵字：!=,<>,like '%str',etc我不是關鍵字）。

SQL> select * from t2 where ctn like '987%';

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601730159

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |

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

| 0 | SELECT STATEMENT | | 10 | 180 | 4 (0)|
00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 10 | 180 | 4 (0)|
00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_T2_CTN | 10 | | 2 (0)|
00:00:01 |

--------------------------------------------------------------------------------
嗯，很順利，可是，這時。。。。。。
SQL> select * from t2 where ctn like '%987';

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 9000 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 500 | 9000 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------

沒走索引？！嘿嘿，以為我怕了嗎？NoNoNo,自從在經曆了上文的case 2之後，我可是很相信CBO的。它這麼選擇，一定是有它的道理。其實想想也不難，假設走索引的話會是什麼樣的情況呢？先通通掃描匹配索引，然後再根據rowid去拿資料。。。好痛苦。。所以啊，CBO還是很好相處的，只要你嘗試著去瞭解它。
啊？你說建了索引不用多白費啊。。。鬱悶了。。。好吧，1，2，3走著：
SQL> select ctn from t2 where ctn like '%987';

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 708488173

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

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

| 0 | SELECT STATEMENT | | 500 | 2500 | 7 (0)| 00:00:0
1 |

|* 1 | INDEX FAST FULL SCAN| IDX_T2_CTN | 500 | 2500 | 7 (0)| 00:00:0
1 |

--------------------------------------------------------------------------------
”呃，這是什麼情況，怎麼又走索引了？“
B樹索引可不是只儲存rowid 的哦，快去翻書！相信我CBO)，沒錯~650) this.width=650;" style="margin:0px;padding:0px;" src="http://bbs.we168.cn/bbs/static/image/smiley/grapeman/08.gif" border="0" alt="08.gif" />

case 5:
關鍵字：函數索引。

“嘿嘿，我想到一個辦法，也能走索引。你看啊，select * from t2 where ctn like 'string%'不是走索引嘛。那我來一招select * from t2 where reverse(ctn) like '789%'。嘿嘿，怎麼樣！?"

SQL> select '123',reverse('123') from dual;

'12 REV
--- ---
123 321

SQL> select * from t2 where reverse(ctn) like '789%';

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 9000 | 10 (10)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 500 | 9000 | 10 (10)| 00:00:01 |
--------------------------------------------------------------------------

”呃，ctn列上的索引沒用上啊“原因是這個列上使用了函數，而我們是對列的值建立的索引，而不是對函數這裡是reverse(ctn)）的值建立的索引，So。。。嗯嗯。那麼怎麼做，你該清楚了吧：
SQL> create index idx_t2_fctn on t2(reverse(ctn));

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T2',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autot traceonly;
SQL> select * from t2 where reverse(ctn) like '789%';

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3119201724

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |

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

| 0 | SELECT STATEMENT | | 1 | 19 | 4 (0)|
00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 19 | 4 (0)|
00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_T2_FCTN | 1 | | 2 (0)|
00:00:01 |

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

case 6:
關鍵字：隱式轉換。

俗話說的好，明槍易躲暗箭難防，這也是個暗箭的故事，不過只要注意規範，就OK啦：
SQL> select * from t2 where ctn = 5;


Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 19 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------

納尼？！？這咋整的？！
原因從執行計畫的後面可以找到：
Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_NUMBER("CTN")=5)

這裡對這個資料庫列應用了一個隱式函數。ctn儲存的字串必須轉換成為一個數字，之後才能與值5進行比較。而這樣一來由於應用了函數，類似上面的case），就無法使用索引來快速的尋找這一行了。如果只是執行串與串的比較：

SQL> select * from t2 where ctn = '5';


Execution Plan
----------------------------------------------------------
Plan hash value: 1601730159

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |

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

| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)|
00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 19 | 2 (0)|
00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_T2_CTN | 1 | | 1 (0)|
00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("CTN"='5')

不出所料。
So,溫馨提醒：一定要儘可能地避免隱式轉換。

今天就到這裡啦，敬請期待《誰動了我的索引三）》預告：——偷窺狂人和組合兄弟。原來綁定變數也不全是優點嘛~

本文出自 “MIKE老畢的部落格” 部落格，請務必保留此出處http://boylook.blog.51cto.com/7934327/1298608