接上文http://boylook.itpub.net/post/43144/520538,谈谈在做演示实验的时候出现了一点小状况。对于演示实验,为了能够走索引,其实我做了三点准备: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,index08.gif虽然麻烦,但是快。)
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获得了表的最新信息,做出了正确的选择,就像奥特曼打败小怪兽一样,我对它点了点头。。。
从此,王子和公主过上了幸福的生活。。。
”哼哼,本大爷哪是这么容易被你们逮住“
什么?难道”真凶“仍然逍遥法外???!!!
敬请期待《谁动了我的索引(二)》