谁动了我的索引(二)

oracle索引学习[@more@] 接上文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)，没错~

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,温馨提醒：一定要尽可能地避免隐式转换。

今天就到这里啦，敬请期待《谁动了我的索引（三）》预告：——偷窥狂人和组合兄弟。原来绑定变量也不全是优点嘛~

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11676357/viewspace-1052294/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11676357/viewspace-1052294/