嗯，自从有了上次的经验，实验时建表都要把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')
不出所料。
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,温馨提醒：一定要尽可能地避免隐式转换。
本文转自MIKE老毕 51CTO博客，原文链接：http://blog.51cto.com/boylook/1298608，如需转载请自行联系原作者
