谈谈在做演示实验的时候出现了一点小状况。对于演示实验，为了能够走索引，其实我做了三点准备：1.not null约束；2.在表t1基础上添加一列r而建立表t2；3.对表进行分析。本文其实也是针对这三点展开。
到底谁动了我的索引呢？跟我一起踏上找出“真凶”的旅程吧！
case 1:
关键字：B树索引；null；聚合函数
SQL> select constraint_name,constraint_type from user_constraints where table_name = 'T1';
CONSTRAINT_NAME C
------------------------------ -
SYS_C005422 C （ 注：not null约束 ）
CONS_C_NN C （ 注：check约束 ）
SQL> select index_name from user_indexes where table_name = 'T1';
INDEX_NAME
------------------------------
IND_T1_CNN （ 注:cnn上的索引 )
下面我们删除nn上的not null约束，并且在列nn上建立B树索引。
SQL> alter table t1 drop constraint SYS_C005422;
Table altered.
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
NN NUMBER
CNN NUMBER
R ROWID
SQL> create index ind_t1_nn on t1(nn);
Index created.
根据上文的实验结果，当存在not null约束的时候，select count(*) from t1是走的索引。现在看看在去掉not null约束是什么情况。
SQL> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
SQL> set autot on exp;
SQL> select count(*) from t1;
COUNT(*)
----------
402550
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 326 (4)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 402K| 326 (4)| 00:00:04 |
-------------------------------------------------------------------
SQL> select /*+ index_ffs(t1 ind_t1_nn) */ count(*) from t1;
COUNT(*)
----------
402550
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 326 (4)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 402K| 326 (4)| 00:00:04 |
-------------------------------------------------------------------
我们看到，即使加了提示仍然没有走索引（ 尽管索引比表要小 ）。我们知道，B树索引对于索引键完全为null的行市不会建立相应的索引条目，所以索引中的行数可能并不是表中的行数。这说明优化器的选择是对的，否则可能会得到错误的答案。推而广之，如果使用索引会返回不正确的结果的话，无论如何，CBO是不会选择索引的。
case 2: “关键字”：索引真的那么好？
细心的筒子们可能会发现，在做演示实验的时候，我先创建了表t，而后用了t1做的实验。那到底背后发生了什么让我做出这样的选择呢？
让我们看看当时的”犯罪现场“：
QL> create table t(nn number not null,cnn number check(cnn is not null));
Table created.
SQL> col constraint_name for a15;
SQL> col constraint_type for a5;
SQL> col search_condition for a15;
SQL> select constraint_name,constraint_type,search_condition from user_constraints where table_name = 'T';
CONSTRAINT_NAME CONST SEARCH_CONDITIO
--------------- ----- ---------------
SYS_C005420 C "NN" IS NOT NULL
SYS_C005421 C cnn is not null
SQL> insert into t select rownum,rownum from dba_objects;
50318 rows created.
SQL> commit;
Commit complete.
SQL> create index ind_t_nn on t(nn);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select count(*) from t;
COUNT(*)
----------
402550
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 202 (5)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 402K| 202 (5)| 00:00:03 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
866 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
咦？怎么没有用到索引呢？很纳闷的情况下，我试了一下hint：
SQL> select /*+ index(t IND_T_NN) */ count(*) from t;
COUNT(*)
----------
402550
Execution Plan
----------------------------------------------------------
Plan hash value: 3926957750
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1091 (2)| 00:00:14 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IND_T_NN | 402K| 1091 (2)| 00:00:14 |
---------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1078 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
虽然走了索引，可是我发现，在index_ffs下的一致读竟然比全表扫描多了那么多！于是，我立刻：
SQL> select dump(nn),dump(rowid) from t where rownum = 1;
DUMP(NN)
--------------------------------------------------------------------------------
DUMP(ROWID)
--------------------------------------------------------------------------------
Typ=2 Len=3: 194,17,94
Typ=69 Len=10: 0,0,205,33,1,0,1,140,0,0
所以，如果此时用了索引，实际上反而会更慢。我们知道，B树索引至少保存一个rowid，而在这个case下nn+cnn的长度要小于rowid。在这种情况下，优化器的做法绝对是英明的。CBO只会在合理的时候才使用索引。So,CBO V5!!!
于是，就有了演示实验create table t2 as select这一幕。。。
case 3:
关键字：过期的分析数据 。
有些结果集起先很小，但是等到查看时，它们已经增长得非常大或者分布变化很大。现在索引就很有意义，如果此时分析这个表，就会使用索引。如果没有正确的统计信息，CBO将无法做出正确的决定。
那是在一个寒冷的冬天：
SQL> select index_name from user_indexes where table_name = 'T1';
INDEX_NAME
------------------------------
IND_T1_CNN
IND_T1_NN
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
NN NOT NULL NUMBER
CNN NUMBER
R ROWID
SQL> update t1 set nn = 1;
（注：筒子们别学我这么更新，很累的。。。create table as select,drop,alter rname,index
虽然麻烦，但是快。）
402550 rows updated.
SQL> update t1 set nn = 99 where rownum = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select nn,count(*) from t1 group by nn;
NN COUNT(*)
---------- ----------
1 402549
99 1
SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=> true);
PL/SQL procedure successfully completed.
SQL> set autot on exp;
SQL> set autot off;
SQL> set autotrace traceonly;
SQL> select * from t1 where nn = 1;
402549 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 402K| 6682K| 331 (5)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 402K| 6682K| 331 (5)| 00:00:04 |
--------------------------------------------------------------------------
在分析的数据之后，CBO根据分析数据做出了正确的选择。可是，这时候。。。。。。
SQL> update t1 set nn = 99 where nn = 1;
402549 rows updated.
SQL> update t1 set nn = 1 where rownum = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select nn,count(*) from t1 group by nn;
NN COUNT(*)
---------- ----------
1 1
99 402549
注意！！！！！表上的数据已经更改，分析数据已经。。。。。过时了！？！
SQL> set autotrace 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 | | 402K| 6682K| 331 (5)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 402K| 6682K| 331 (5)| 00:00:04 |
--------------------------------------------------------------------------
CBO面对这样的情况懵了，只要按照原来的老黄历（ 注：如果表分析过，但是分析信息过旧，这时CBO不会使用动态采样，而是使用旧的分析数据 ），殊不知，这套办法已经无法与时俱进了。。。。看着CBO那可怜的身影，让我们来拉它一把，怎么说以后还要继续共事呢。
SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade => true);
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly;
SQL> select * from t1 where nn = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1994307914
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | 17 | 4 (0)| 0
0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 17 | 4 (0)| 0
0:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T1_NN | 1 | | 3 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
再次分析之后，CBO获得了表的最新信息，做出了正确的选择，就像奥特曼打败小怪兽一样，我对它点了点头。。。
从此，王子和公主过上了幸福的生活。。。
”哼哼，本大爷哪是这么容易被你们逮住“
到底谁动了我的索引呢？跟我一起踏上找出“真凶”的旅程吧！
case 1:
关键字：B树索引；null；聚合函数
SQL> select constraint_name,constraint_type from user_constraints where table_name = 'T1';
CONSTRAINT_NAME C
------------------------------ -
SYS_C005422 C （ 注：not null约束 ）
CONS_C_NN C （ 注：check约束 ）
SQL> select index_name from user_indexes where table_name = 'T1';
INDEX_NAME
------------------------------
IND_T1_CNN （ 注:cnn上的索引 )
下面我们删除nn上的not null约束，并且在列nn上建立B树索引。
SQL> alter table t1 drop constraint SYS_C005422;
Table altered.
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
NN NUMBER
CNN NUMBER
R ROWID
SQL> create index ind_t1_nn on t1(nn);
Index created.
根据上文的实验结果，当存在not null约束的时候，select count(*) from t1是走的索引。现在看看在去掉not null约束是什么情况。
SQL> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
SQL> set autot on exp;
SQL> select count(*) from t1;
COUNT(*)
----------
402550
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 326 (4)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 402K| 326 (4)| 00:00:04 |
-------------------------------------------------------------------
SQL> select /*+ index_ffs(t1 ind_t1_nn) */ count(*) from t1;
COUNT(*)
----------
402550
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 326 (4)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 402K| 326 (4)| 00:00:04 |
-------------------------------------------------------------------
我们看到，即使加了提示仍然没有走索引（ 尽管索引比表要小 ）。我们知道，B树索引对于索引键完全为null的行市不会建立相应的索引条目，所以索引中的行数可能并不是表中的行数。这说明优化器的选择是对的，否则可能会得到错误的答案。推而广之，如果使用索引会返回不正确的结果的话，无论如何，CBO是不会选择索引的。
case 2: “关键字”：索引真的那么好？
细心的筒子们可能会发现，在做演示实验的时候，我先创建了表t，而后用了t1做的实验。那到底背后发生了什么让我做出这样的选择呢？
让我们看看当时的”犯罪现场“：
QL> create table t(nn number not null,cnn number check(cnn is not null));
Table created.
SQL> col constraint_name for a15;
SQL> col constraint_type for a5;
SQL> col search_condition for a15;
SQL> select constraint_name,constraint_type,search_condition from user_constraints where table_name = 'T';
CONSTRAINT_NAME CONST SEARCH_CONDITIO
--------------- ----- ---------------
SYS_C005420 C "NN" IS NOT NULL
SYS_C005421 C cnn is not null
SQL> insert into t select rownum,rownum from dba_objects;
50318 rows created.
SQL> commit;
Commit complete.
SQL> create index ind_t_nn on t(nn);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select count(*) from t;
COUNT(*)
----------
402550
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 202 (5)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 402K| 202 (5)| 00:00:03 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
866 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
咦？怎么没有用到索引呢？很纳闷的情况下，我试了一下hint：
SQL> select /*+ index(t IND_T_NN) */ count(*) from t;
COUNT(*)
----------
402550
Execution Plan
----------------------------------------------------------
Plan hash value: 3926957750
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1091 (2)| 00:00:14 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IND_T_NN | 402K| 1091 (2)| 00:00:14 |
---------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1078 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
虽然走了索引，可是我发现，在index_ffs下的一致读竟然比全表扫描多了那么多！于是，我立刻：
SQL> select dump(nn),dump(rowid) from t where rownum = 1;
DUMP(NN)
--------------------------------------------------------------------------------
DUMP(ROWID)
--------------------------------------------------------------------------------
Typ=2 Len=3: 194,17,94
Typ=69 Len=10: 0,0,205,33,1,0,1,140,0,0
所以，如果此时用了索引，实际上反而会更慢。我们知道，B树索引至少保存一个rowid，而在这个case下nn+cnn的长度要小于rowid。在这种情况下，优化器的做法绝对是英明的。CBO只会在合理的时候才使用索引。So,CBO V5!!!
于是，就有了演示实验create table t2 as select这一幕。。。
case 3:
关键字：过期的分析数据 。
有些结果集起先很小，但是等到查看时，它们已经增长得非常大或者分布变化很大。现在索引就很有意义，如果此时分析这个表，就会使用索引。如果没有正确的统计信息，CBO将无法做出正确的决定。
那是在一个寒冷的冬天：
SQL> select index_name from user_indexes where table_name = 'T1';
INDEX_NAME
------------------------------
IND_T1_CNN
IND_T1_NN
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
NN NOT NULL NUMBER
CNN NUMBER
R ROWID
SQL> update t1 set nn = 1;
（注：筒子们别学我这么更新，很累的。。。create table as select,drop,alter rname,index
虽然麻烦，但是快。）
402550 rows updated.
SQL> update t1 set nn = 99 where rownum = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select nn,count(*) from t1 group by nn;
NN COUNT(*)
---------- ----------
1 402549
99 1
SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=> true);
PL/SQL procedure successfully completed.
SQL> set autot on exp;
SQL> set autot off;
SQL> set autotrace traceonly;
SQL> select * from t1 where nn = 1;
402549 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 402K| 6682K| 331 (5)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 402K| 6682K| 331 (5)| 00:00:04 |
--------------------------------------------------------------------------
在分析的数据之后，CBO根据分析数据做出了正确的选择。可是，这时候。。。。。。
SQL> update t1 set nn = 99 where nn = 1;
402549 rows updated.
SQL> update t1 set nn = 1 where rownum = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select nn,count(*) from t1 group by nn;
NN COUNT(*)
---------- ----------
1 1
99 402549
注意！！！！！表上的数据已经更改，分析数据已经。。。。。过时了！？！
SQL> set autotrace 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 | | 402K| 6682K| 331 (5)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 402K| 6682K| 331 (5)| 00:00:04 |
--------------------------------------------------------------------------
CBO面对这样的情况懵了，只要按照原来的老黄历（ 注：如果表分析过，但是分析信息过旧，这时CBO不会使用动态采样，而是使用旧的分析数据 ），殊不知，这套办法已经无法与时俱进了。。。。看着CBO那可怜的身影，让我们来拉它一把，怎么说以后还要继续共事呢。
SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade => true);
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly;
SQL> select * from t1 where nn = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1994307914
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | 17 | 4 (0)| 0
0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 17 | 4 (0)| 0
0:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T1_NN | 1 | | 3 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
再次分析之后，CBO获得了表的最新信息，做出了正确的选择，就像奥特曼打败小怪兽一样，我对它点了点头。。。
从此，王子和公主过上了幸福的生活。。。
”哼哼，本大爷哪是这么容易被你们逮住“
什么？难道”真凶“仍然逍遥法外？？？！！！
本文转自MIKE老毕 51CTO博客，原文链接：http://blog.51cto.com/boylook/1298605，如需转载请自行联系原作者
