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
转载请注明出处
作者:daaprk
链接:http://www.cnblogs.com/dap570/p/3396513.html
本作品采用知识共享署名-非商业性使用-禁止演绎 3.0 未本地化版本许可协议进行许可。