接上文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),没错~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,温馨提醒:一定要尽可能地避免隐式转换

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