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
很好,万事俱备了,索引啊索引,就看你的了!我看这次谁来动你![09.gif](http://bbs.we168.cn/bbs/static/image/smiley/coolmonkey/09.gif)
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](http://bbs.we168.cn/bbs/static/image/smiley/grapeman/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,温馨提醒:一定要尽可能地避免隐式转换。
今天就到这里啦,敬请期待《谁动了我的索引(三)》
预告:——偷窥狂人和组合兄弟。原来绑定变量也不全是优点嘛~
嗯,自从有了上次的经验,实验时建表都要把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
很好,万事俱备了,索引啊索引,就看你的了!我看这次谁来动你
![09.gif](http://bbs.we168.cn/bbs/static/image/smiley/coolmonkey/09.gif)
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](http://bbs.we168.cn/bbs/static/image/smiley/grapeman/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 |
--------------------------------------------------------------------------
纳尼?!?
![10.gif](http://bbs.we168.cn/bbs/static/image/smiley/grapeman/10.gif)
原因从执行计划的后面可以找到:
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,温馨提醒:一定要尽可能地避免隐式转换。
今天就到这里啦,敬请期待《谁动了我的索引(三)》
![19.gif](http://bbs.we168.cn/bbs/static/image/smiley/grapeman/19.gif)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11676357/viewspace-1052294/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11676357/viewspace-1052294/