环境介绍：
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.
我们知道，根据 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.
我们知道，根据 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
对于使用不同绑定变量执行计划应该不同的情况，建议不要使用绑定变量，否则可能会产生随机的执行计划
至此，总结了一下常见了不走索引的情况，和筒子们分享了一下。其实实际的情况以及每个知识点都可以进一步深入，里面还有很多细节问题。不过为了主线清晰，没有拓展。希望大家能够深入研究，共同探讨。感谢收看
本文转自MIKE老毕 51CTO博客，原文链接：http://blog.51cto.com/boylook/1298611，如需转载请自行联系原作者
