oracle索引学习[@more@]
环境介绍:
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
NN NOT NULL NUMBER
CNN NUMBER
R ROWID
SQL> select count(distinct nn) a,count(distinct cnn) b,count(*) c from t1;
A B C
---------- ---------- ----------
2 201275 402550
SQL> select nn,count(*) from t1 group by nn;
NN COUNT(*)
---------- ----------
1 1
99 402549
SQL> select index_name from user_indexes where table_name = 'T1';
no rows selected
case 7:
关键字:组合索引,谓词,索引列。
我们在使用一个B树索引,而且谓词中没有使用索引的最前列。如果是这种情况,可以假设有一个表T,在T(x,y)上有个索引,我们要如下查询:select * from t where y = 5。此时,CBO就不打算使用T(x,y)上的索引,因为谓词不涉及x列。在这种情况下,如果使用索引,可能必须查看每一个索引条目(稍后讨论 index skip scan),而CBO通常倾向于对T做一个全表扫描。
SQL> create index idx_t1_cnn_nn on t1(cnn,nn);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);
PL/SQL procedure successfully completed.
SQL> set autot traceonly;
SQL> select * from t1 where nn = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 329 (4)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 17 | 329 (4)| 00:00:04 |
--------------------------------------------------------------------------
当然,这并不完全排除使用索引。如果:
SQL> select nn,cnn from t1 where nn = 1;
优化器就会注意到,它不必全扫描表来得到nn,cnn,对索引本身做一个index fast full scan会更合适,因为这个索引一般比底层表小得多。如下:
Execution Plan
----------------------------------------------------------
Plan hash value: 3065609956
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 8 | 244 (5)| 00:0
0:03 |
|* 1 | INDEX FAST FULL SCAN| IDX_T1_CNN_NN | 1 | 8 | 244 (5)| 00:0
0:03 |
--------------------------------------------------------------------------------
另一种情况CBO也会使用T(x,y)上的索引,这就是index skip scan。当且仅当索引的最前列的选择性很低(只有很少的几个不同值):
我们知道nn的选择性很低,所以,我们重新建立索引。
SQL> drop index idx_t1_cnn_nn;
Index dropped.
SQL> create index idx_t1_nn_cnn on t1(nn,cnn);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);
PL/SQL procedure successfully completed.
SQL> set autot traceonly;
当做以下查询时(cnn不是索引的第一列):
SQL> select * from t1 where cnn = 100;
结果如下:
Execution Plan
----------------------------------------------------------
Plan hash value: 1275931045
--------------------------------------------------------------------------------
-------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |
--------------------------------------------------------------------------------
-------------
| 0 | SELECT STATEMENT | | 2 | 34 | 5 (0
)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 34 | 5 (0
)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_T1_NN_CNN | 2 | | 3 (0
)| 00:00:01 |
--------------------------------------------------------------------------------
-------------
index skip scan告诉Oracle姚跳跃式扫描这个索引,查找nn值有改变的地方,并且那里开始向下读树,然后扫描索引查找cnn =100。
总结:考虑查询只包括一个查询条件的情况,如果是选择性强的列作为前缀列,当查询指定选择性不高的列时,Oracle不会选择索引。而对于选择性差的列作为前缀列,当查询指定选择性高的列时,Oracle可以使用索引SKIP扫描。
case 8:
关键字:绑定变量。
在实际应用中,为了减少语句的解析(十分消耗资源),要常常使用到绑定变量。那它是不是十全十美的呢?让我们peek里面的秘密吧:
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter session set tracefile_identifier = 'test';
Session altered.
SQL> alter session set sql_trace = true;
Session altered.
SQL> variable vn number;(←定义一个绑定变量)
SQL> exec :vn := 99;
PL/SQL procedure successfully completed.
SQL> select * from t1 where nn = :vn;
********************************************************************************
select *
from
t1 where nn = :vn
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1755 0.05 0.51 96 1836 0 26311
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1757 0.07 0.52 96 1836 0 26311
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
26311 TABLE ACCESS FULL T1 (cr=1836 pr=96 pw=0 time=130246 us)
********************************************************************************
这时优化器的选择是全表扫描,是OK的。接下来,改变绑定变量的值:
SQL> exec :vn := 1;
PL/SQL procedure successfully completed.
我们知道,根据http://bbs.we168.cn/bbs/forum.php?mod=viewthread&tid=63&extra=page%3D1里的case 3的结论,应该是走索引的,那来执行一下:
SQL> select * from t1 where nn = :vn;
********************************************************************************
select *
from
t1 where nn = :vn
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.31 1.78 1345 1446 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.31 1.78 1345 1446 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL T1 (cr=1446 pr=1345 pw=0 time=65 us)
********************************************************************************
敏感词,*&……*&……*%&……¥&%……*……&*,和谐词,*&*((*)
这不是我们所期望的,我们期望的是INDEX RANGE SCAN。单显然优化器使用了全表扫描。这个查询的就像它有谓词where nn = 99一样。这就是绑定变量窥视(bind peeking)了。查询第一次是硬解析(Misses in library cache during parse: 1),优化器考虑绑定变量的值并生成执行计划。当再次查询的时候又进行了全表扫描,直接使用了第一次硬解析生成的执行计划(Misses in library cache during parse: 0)。也就是bind peeking只是在hard parse发生,任何重用这个特定计划的查询都将使用全表扫描。
下面,颠倒执行顺序来验证这一结论:
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter session set sql_trace = true;
Session altered.
SQL> exec :vn := 1;
PL/SQL procedure successfully completed.
SQL> select * from t1 where nn = :vn;
********************************************************************************
select *
from
t1 where nn = :vn
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 24 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 24 5 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T1 (cr=5 pr=24 pw=0 time=2184 us)
1 INDEX RANGE SCAN IDX_T1_NN_CNN (cr=4 pr=16 pw=0 time=1314 us)(object id 52618)
********************************************************************************
SQL> exec :vn := 99;
PL/SQL procedure successfully completed.
SQL> select * from t1 where nn = :vn;
********************************************************************************
select *
from
t1 where nn = :vn
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 178 0.01 0.02 104 2841 0 2656
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 180 0.01 0.02 104 2841 0 2656
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
2656 TABLE ACCESS BY INDEX ROWID T1 (cr=2841 pr=104 pw=0 time=31930 us)
2656 INDEX RANGE SCAN IDX_T1_NN_CNN (cr=185 pr=8 pw=0 time=10659 us)(object id 52618)
********************************************************************************
当SQL第一次执行的时候,优化器会根据绑定变量来确定执行计划(如果存在柱状图)。BIND PEEKING只有当该SQL第一次执行的时候,进行HARD PARSE的时候才进行,第二次调用该SQL,就不会再次进行BIND PEEKING。这种情况下,就存在另外一个风险,如果某个列的倾斜性很厉害,那么使用BIND PEEKING就是不安全的,因为不同的参数代入,只能走第一次执行时的执行计划,那么执行计划就像掷色子一样,要靠运气了。碰到这种情况,应用就不应该使用绑定变量,而应该改为直接值了。
So,温馨提示:使用绑定变量的时候可以有效的减少PARSE
对于使用不同绑定变量执行计划应该不同的情况,建议不要使用绑定变量,否则可能会产生随机的执行计划
至此,总结了一下常见了不走索引的情况,和筒子们分享了一下。其实实际的情况以及每个知识点都可以进一步深入,里面还有很多细节问题。不过为了主线清晰,没有拓展。希望大家能够深入研究,共同探讨。感谢收看
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
NN NOT NULL NUMBER
CNN NUMBER
R ROWID
SQL> select count(distinct nn) a,count(distinct cnn) b,count(*) c from t1;
A B C
---------- ---------- ----------
2 201275 402550
SQL> select nn,count(*) from t1 group by nn;
NN COUNT(*)
---------- ----------
1 1
99 402549
SQL> select index_name from user_indexes where table_name = 'T1';
no rows selected
case 7:
关键字:组合索引,谓词,索引列。
我们在使用一个B树索引,而且谓词中没有使用索引的最前列。如果是这种情况,可以假设有一个表T,在T(x,y)上有个索引,我们要如下查询:select * from t where y = 5。此时,CBO就不打算使用T(x,y)上的索引,因为谓词不涉及x列。在这种情况下,如果使用索引,可能必须查看每一个索引条目(稍后讨论 index skip scan),而CBO通常倾向于对T做一个全表扫描。
SQL> create index idx_t1_cnn_nn on t1(cnn,nn);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);
PL/SQL procedure successfully completed.
SQL> set autot traceonly;
SQL> select * from t1 where nn = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 329 (4)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 17 | 329 (4)| 00:00:04 |
--------------------------------------------------------------------------
当然,这并不完全排除使用索引。如果:
SQL> select nn,cnn from t1 where nn = 1;
优化器就会注意到,它不必全扫描表来得到nn,cnn,对索引本身做一个index fast full scan会更合适,因为这个索引一般比底层表小得多。如下:
Execution Plan
----------------------------------------------------------
Plan hash value: 3065609956
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 1 | 8 | 244 (5)| 00:0
0:03 |
|* 1 | INDEX FAST FULL SCAN| IDX_T1_CNN_NN | 1 | 8 | 244 (5)| 00:0
0:03 |
--------------------------------------------------------------------------------
另一种情况CBO也会使用T(x,y)上的索引,这就是index skip scan。当且仅当索引的最前列的选择性很低(只有很少的几个不同值):
我们知道nn的选择性很低,所以,我们重新建立索引。
SQL> drop index idx_t1_cnn_nn;
Index dropped.
SQL> create index idx_t1_nn_cnn on t1(nn,cnn);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);
PL/SQL procedure successfully completed.
SQL> set autot traceonly;
当做以下查询时(cnn不是索引的第一列):
SQL> select * from t1 where cnn = 100;
结果如下:
Execution Plan
----------------------------------------------------------
Plan hash value: 1275931045
--------------------------------------------------------------------------------
-------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |
--------------------------------------------------------------------------------
-------------
| 0 | SELECT STATEMENT | | 2 | 34 | 5 (0
)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 34 | 5 (0
)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_T1_NN_CNN | 2 | | 3 (0
)| 00:00:01 |
--------------------------------------------------------------------------------
-------------
index skip scan告诉Oracle姚跳跃式扫描这个索引,查找nn值有改变的地方,并且那里开始向下读树,然后扫描索引查找cnn =100。
总结:考虑查询只包括一个查询条件的情况,如果是选择性强的列作为前缀列,当查询指定选择性不高的列时,Oracle不会选择索引。而对于选择性差的列作为前缀列,当查询指定选择性高的列时,Oracle可以使用索引SKIP扫描。
case 8:
关键字:绑定变量。
在实际应用中,为了减少语句的解析(十分消耗资源),要常常使用到绑定变量。那它是不是十全十美的呢?让我们peek里面的秘密吧:
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter session set tracefile_identifier = 'test';
Session altered.
SQL> alter session set sql_trace = true;
Session altered.
SQL> variable vn number;(←定义一个绑定变量)
SQL> exec :vn := 99;
PL/SQL procedure successfully completed.
SQL> select * from t1 where nn = :vn;
********************************************************************************
select *
from
t1 where nn = :vn
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1755 0.05 0.51 96 1836 0 26311
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1757 0.07 0.52 96 1836 0 26311
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
26311 TABLE ACCESS FULL T1 (cr=1836 pr=96 pw=0 time=130246 us)
********************************************************************************
这时优化器的选择是全表扫描,是OK的。接下来,改变绑定变量的值:
SQL> exec :vn := 1;
PL/SQL procedure successfully completed.
我们知道,根据http://bbs.we168.cn/bbs/forum.php?mod=viewthread&tid=63&extra=page%3D1里的case 3的结论,应该是走索引的,那来执行一下:
SQL> select * from t1 where nn = :vn;
********************************************************************************
select *
from
t1 where nn = :vn
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.31 1.78 1345 1446 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.31 1.78 1345 1446 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL T1 (cr=1446 pr=1345 pw=0 time=65 us)
********************************************************************************
敏感词,*&……*&……*%&……¥&%……*……&*,和谐词,*&*((*)
这不是我们所期望的,我们期望的是INDEX RANGE SCAN。单显然优化器使用了全表扫描。这个查询的就像它有谓词where nn = 99一样。这就是绑定变量窥视(bind peeking)了。查询第一次是硬解析(Misses in library cache during parse: 1),优化器考虑绑定变量的值并生成执行计划。当再次查询的时候又进行了全表扫描,直接使用了第一次硬解析生成的执行计划(Misses in library cache during parse: 0)。也就是bind peeking只是在hard parse发生,任何重用这个特定计划的查询都将使用全表扫描。
下面,颠倒执行顺序来验证这一结论:
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter session set sql_trace = true;
Session altered.
SQL> exec :vn := 1;
PL/SQL procedure successfully completed.
SQL> select * from t1 where nn = :vn;
********************************************************************************
select *
from
t1 where nn = :vn
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 24 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 24 5 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T1 (cr=5 pr=24 pw=0 time=2184 us)
1 INDEX RANGE SCAN IDX_T1_NN_CNN (cr=4 pr=16 pw=0 time=1314 us)(object id 52618)
********************************************************************************
SQL> exec :vn := 99;
PL/SQL procedure successfully completed.
SQL> select * from t1 where nn = :vn;
********************************************************************************
select *
from
t1 where nn = :vn
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 178 0.01 0.02 104 2841 0 2656
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 180 0.01 0.02 104 2841 0 2656
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
2656 TABLE ACCESS BY INDEX ROWID T1 (cr=2841 pr=104 pw=0 time=31930 us)
2656 INDEX RANGE SCAN IDX_T1_NN_CNN (cr=185 pr=8 pw=0 time=10659 us)(object id 52618)
********************************************************************************
当SQL第一次执行的时候,优化器会根据绑定变量来确定执行计划(如果存在柱状图)。BIND PEEKING只有当该SQL第一次执行的时候,进行HARD PARSE的时候才进行,第二次调用该SQL,就不会再次进行BIND PEEKING。这种情况下,就存在另外一个风险,如果某个列的倾斜性很厉害,那么使用BIND PEEKING就是不安全的,因为不同的参数代入,只能走第一次执行时的执行计划,那么执行计划就像掷色子一样,要靠运气了。碰到这种情况,应用就不应该使用绑定变量,而应该改为直接值了。
So,温馨提示:使用绑定变量的时候可以有效的减少PARSE
对于使用不同绑定变量执行计划应该不同的情况,建议不要使用绑定变量,否则可能会产生随机的执行计划
至此,总结了一下常见了不走索引的情况,和筒子们分享了一下。其实实际的情况以及每个知识点都可以进一步深入,里面还有很多细节问题。不过为了主线清晰,没有拓展。希望大家能够深入研究,共同探讨。感谢收看
![12.gif](http://bbs.we168.cn/bbs/static/image/smiley/grapeman/12.gif)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11676357/viewspace-1052295/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11676357/viewspace-1052295/