谁动了我的索引(三)

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
对于使用不同绑定变量执行计划应该不同的情况，建议不要使用绑定变量，否则可能会产生随机的执行计划

至此，总结了一下常见了不走索引的情况，和筒子们分享了一下。其实实际的情况以及每个知识点都可以进一步深入，里面还有很多细节问题。不过为了主线清晰，没有拓展。希望大家能够深入研究，共同探讨。感谢收看

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11676357/viewspace-1052295/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11676357/viewspace-1052295/