Full scan vs index 执行计划的实验

根据Oracle-L邮件列表里主题「

Full scan vs index

」的讨论而来。
1、测试环境创建
SYS@HEMESRHTDB2(1.206)> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE11.2.0.3.0Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
 
SYS@HEMESRHTDB2(1.206)> 
create table t2
    as
    with generator as (
     select --+ materialize
       rownum pk
       from all_objects
       where rownum<=4000
       )
    select
       /*+ ordered use_nl(v2)*/
     rownum pk,
     round(dbms_random.value(1,2)) a,
     round(dbms_random.value(1,5)) b,
     round(dbms_random.value(1,10)) c,
     round(dbms_random.value(1,100)) d,
     round(dbms_random.value(1,1000000)) e 
   from
     generator v1,
     generator v2
   where
     rownum<=600000
     /
Table created.
 
SYS@HEMESRHTDB2(1.206)> select * from t2 where rownum<=100;
 
PK    A       B  C     DE
---------- ---------- ---------- ---------- ---------- ----------
1    2       1  3    80   296354
2    2       3  9    47   531531
3    2       3  1    10   330623
4    2       5  2    35    21138
5    2       5  7    50   425066
6    2       3  9    75   322065
7    2       4  1    93    55360
8    2       1  8    99   378844
9    2       5  8    72   869863
10    2       5  2    63   373369
11    1       4  4    37   313221
12    1       5  8    68    40918
13    1       2  8    48   457786
14    2       3  2    83   316507
15    1       4  2    14   734118
16    1       4  7    59    47266
……
……
 
SYS@HEMESRHTDB2(1.206)> create index ix_t2_key on t2(PK,A) online nologging;
Index created.
SYS@HEMESRHTDB2(1.206)> create index ix_t2_D on t2(D) online nologging;
Index created.
SYS@HEMESRHTDB2(1.206)> alter session set statistics_level ='ALL' ; 
Session altered.
 
2、测试4种情况
索引情况。
ix_t2_key on t2(PK,A) 
ix_t2_D   on t2(D)
 
Select count(*) from T2 where pk > 520000;
select count(*) from t2 where A=1;
Select count(D) from T2 where pk > 520000;
3、测试
实验1
SYS@HEMESRHTDB2(1.206)> select count(*) from T2 where pk > 520000;
SYS@HEMESRHTDB2(1.206)> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'allstats last ADVANCED PEEKED_BINDS')); 
 
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID9c98xbdbfww9r, child number 0
-------------------------------------
Select count(*) from T2 where pk > 520000
 
Plan hash value: 2050414396
 
 
实验2
SYS@HEMESRHTDB2(1.206)> select count(*) from t2 where A=1;
 
  COUNT(*)
----------
    299737
 
SYS@HEMESRHTDB2(1.206)> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'allstats last ADVANCED PEEKED_BINDS')); 
 
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID651cjf8pmhb51, child number 0
-------------------------------------
select count(*) from t2 where A=1
 
Plan hash value: 2933116225
实验3
SYS@HEMESRHTDB2(1.206)> select count(D) from t2 where pk>=520000; 
 
  COUNT(D)
----------
     80001
 
SYS@HEMESRHTDB2(1.206)> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'allstats last ADVANCED PEEKED_BINDS')); 
 
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID41uuvuyutgn6q, child number 0
-------------------------------------
select count(D) from t2 where pk>=520000
 
Plan hash value: 3321871023
4、结论和延伸
实验1
SYS@HEMESRHTDB2(1.206)> select count(*) from T2 where pk > 520000;
按照预期走索引IX_T2_KEY。
实验2
SYS@HEMESRHTDB2(1.206)> select count(*) from t2 where A=1;
也同预期。
实验3
SYS@HEMESRHTDB2(1.206)> select count(D) from t2 where pk>=520000; 
为啥变成count(D)就完全不一样了？
 
我们加上index hint看看效果如何？
 
延伸实验 Index hint
SYS@HEMESRHTDB2(1.206)> select /*+ index(T2 IX_T2_KEY) */ count(D) from t2 where pk>=520000; 
 
  COUNT(D)
----------
     80001
 
SYS@HEMESRHTDB2(1.206)> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'allstats last ADVANCED PEEKED_BINDS')); 
 
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID4s4zzmrzdzrbt, child number 0
-------------------------------------
select /*+ index(T2 IX_T2_KEY) */ count(D) from t2 where pk>=520000
 
Plan hash value: 948933721
 
 
这儿看起来好像有些眉目了，Cost虽然比全表扫描的要大，但真正耗用的buffers根本就不大。
问题出在统计信息！！？
 
SYS@HEMESRHTDB2(1.206)> 
 
select
  column_name,
  num_distinct,
  histogram, num_buckets,
  to_char(LAST_ANALYZED, 'yyyy-mm-dd hh24:mi:ss') 
from all_tab_col_statistics 
  where upper(table_name)='T2';
 
no rows selected
 
果然咧，这时收集下统计信息。
 
SYS@HEMESRHTDB2(1.206)> 
begin
  dbms_stats.gather_table_stats(
  ownname => user,
  tabname => 'T2',
  estimate_percent =>100,
  cascade => true);
end;
/
PL/SQL procedure successfully completed.
 
「for all columns 」或者 「for all indexed columns」都OK，重要的是覆盖count(D) 字段，CBO才能够计算出正确的cost。
可以加个/*1*/之类的使执行计划重新解析，或是alter system flush shared_pool; 
嗯，随你喜欢。
 
SYS@HEMESRHTDB2(1.206)> select /*1*/ count(D) from t2 where pk>=520000;
SYS@HEMESRHTDB2(1.206)> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'allstats last ADVANCED PEEKED_BINDS')); 
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID6z1bc6xv0anrn, child number 0
-------------------------------------
select /*1*/ count(D) from t2 where pk>=520000
 
Plan hash value: 948933721
 
这个时候，便会选择正确的索引了，
当然，你也可以把D字段包含入索引IX_T2_KEY中。
 
最后：
 
如果扫描的范围再大一些？会发生什么？
10万/60万
 
SYS@HEMESRHTDB2(1.206)> select /*2*/ count(D) from t2 where pk>=500000; 
 
  COUNT(D)
----------
    100001
 
SYS@HEMESRHTDB2(1.206)> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'allstats last ADVANCED PEEKED_BINDS')); 
 
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID7shudyqdb5nbk, child number 0
-------------------------------------
select /*2*/ count(D) from t2 where pk>=500000
 
Plan hash value: 3321871023

 

 
 
posted @ 2013-10-30 13:26  Gerrard  阅读(420)  评论(0)  编辑  收藏  举报