1.SQL需求，统计当天的数据量。
SQL> SELECT count(*) FROM test_union WHERE win_type=1 AND gmt_create >= trunc(sysdate,'dd') and gmt_create <= trunc(sysdate,'dd')+1;
COUNT(*)
----------
20063
1 row selected.
COUNT(*)
----------
20063
1 row selected.
2.查看其索引，以gmt_create开头。
sql>create index idx_union on test_union (gmt_create,win_type) tablespace tbs_index compute statistics;
3.查看awr报表的性能，逻辑读很高，达到9700个。
Buffer Gets Executions Gets per Exec %Total Time Time (s) Hash Value
--------------- ---------- -------------- ------ -------- --------- ------
205,157,987 21,236 9,660.9 34.5 6733.21 7568.58 1532799124
Module: java@app12345 (TNS V1-V3)
SELECT count(*) FROM test_union WHERE win_type=1 AND gmt_create >= trunc(sysdate,'dd') and gmt_create <= trunc(sysdate,'dd')+1
--------------- ---------- -------------- ------ -------- --------- ------
205,157,987 21,236 9,660.9 34.5 6733.21 7568.58 1532799124
Module: java@app12345 (TNS V1-V3)
SELECT count(*) FROM test_union WHERE win_type=1 AND gmt_create >= trunc(sysdate,'dd') and gmt_create <= trunc(sysdate,'dd')+1
因为是只通过索引扫描，当看到返回结果集在2万左右，我们很容易估算出这个sql需要的逻辑读，(gmt_date字段7个字节+win_type字段1个字节+rowid+…）*2万，小于100个，现在很明显是偏高的。
4.调整前我们先去看数据分布。
SQL> select win_type,count(*) from test_union group by win_type; --按win_type分组
win_type count(*)
---------- ----------
0 8583162
1 2725424
2 765237
3 2080156
4 2090871
5 3568682
SQL> select count(*) from test_union where gmt_create >= trunc(sysdate,'dd') and gmt_create <= trunc(sysdate,'dd')+1; --按gmt_create统计，一天数据量在22万左右
COUNT(*)
----------
229857
1 row selected.
win_type count(*)
---------- ----------
0 8583162
1 2725424
2 765237
3 2080156
4 2090871
5 3568682
SQL> select count(*) from test_union where gmt_create >= trunc(sysdate,'dd') and gmt_create <= trunc(sysdate,'dd')+1; --按gmt_create统计，一天数据量在22万左右
COUNT(*)
----------
229857
1 row selected.
5.调整索引,改为以win_type开头，为什么要以win_type开头呢？
create index idx_union123 on test_union (win_type,gmt_create) tablespace tbs_index compute statistics; --新索引
6.查看其执行计划，逻辑读变成了89。
sql>set auto traceonly
sql> select count(*) from test_union
where win_type=1 and gmt_create >= trunc(sysdate,'dd') and gmt_create <= trunc(sysdate,'dd')+1;
Elapsed: 00:00:07.17
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 INDEX (RANGE SCAN) OF 'idx_union123' (NO
N-UNIQUE) (Cost=7 Card=1 Bytes=9)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
89 consistent gets
0 physical reads
0 redo size
493 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sql> select count(*) from test_union
where win_type=1 and gmt_create >= trunc(sysdate,'dd') and gmt_create <= trunc(sysdate,'dd')+1;
Elapsed: 00:00:07.17
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 INDEX (RANGE SCAN) OF 'idx_union123' (NO
N-UNIQUE) (Cost=7 Card=1 Bytes=9)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
89 consistent gets
0 physical reads
0 redo size
493 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
都在说建索引一定要看数据分布，从数据分布来看，一天的的数据(gmt_create)量在22万左右，而win_type的数据量非常 大，win_type为1有300万左右，为什么还要把win_type放在索引的前面呢？抛块砖，希望大家能对联合索引有更深入的理解，希望一起讨论。
--EOF--
帖子下的几个评论也很有意思：
XML/HTML代码
- 1. 1棉花糖ONE
- hehe，所以我在itpub上经常建议人家如果是建复合索引的话，把范围查询的谓词尽量放在后面，这样能增加木匠说的有效索引选择度，对oracle执行计划的判断有意义，当然更主要的是这样创建索引确实是减少了索引扫描的范围,不知道楼主的环境还在不在，根据数据来看，这２个字段是相关的，而且楼主恰好查询的是 count(*)，可以不扫描表，走复合索引就比较正常，如果是select * from … ,这样很可能就不会选择走索引，就11g以前除了动态采样，加hint，统计信息上应该是搞不对，我测试了11g的扩展列的统计信息，感觉这玩意效果没有想象中的理想
- Comment on Jan 10th, 2009 at 9:58 am
- 2. 2木匠
- 如果没有搞清楚SQL optimizer engine and Run time engine怎么工作,怎么使用composite index,
- 你的观测结果只是表面现象,永远也不知道为什么.
- 查看数据分布是对的,
- 但是这种情况是需要寻根问底的. my CA$0.02 + tax, 我的一点建议.
- 木匠不辞劳苦,再重复一遍SQL optimizer and runtime engine 是怎么工作的:
- as soon as we have a range scan on a column used somewhere in the
- middle of an index definition or fail to supply a test on such a column, the predicates on later columns do not restrict the selection of index leaf blocks that we have to examine.
- In this case, the effective index selectivity has to be calculated from the predicate on just the “gmt_create” column. Because the test on “gmt_create” is range-based, the predicates on “win_type” do not restrict the number of index leaf blocks we have to walk.
- 关键字: effective index selectivity.
- Comment on Jan 10th, 2009 at 1:49 am
- 3. 3丁原
- 这个案例已经很久了。
- 实际上是没有打算写的，这个例子一直有人在问为什么，问的多了，我干脆就邮件中的内容整理出来，大家 讨论加深印象。
- 我尝试把线上的数据拉下来做测试，发觉太大了根本拉不下来，只能是拼拼凑凑，gmt_create的索引已经drop掉了，部分测试数据可能不那么准确，但是数据还是在范围之内，不会偏差很大。
- 逻辑读怎么算？
- select (7+1+rowid+..)*20000/8192，差不多就是我们要的逻辑读。
- to木匠：cost不一定准确的，我更多的是查看数据分布，结果集，以逻辑读来判断。
- to棉花糖：回复有审核的，防止很多垃圾广告。
- Comment on Jan 9th, 2009 at 5:11 pm
- 4. 4carcase
- win_type，gmt_create 扫描的索引块少多了，确定了win_type=1的范围后，再确定了是当天的数据
- （索引是win_type,gmt_create 排序的，范围扫描马上能定位到当天的数据）也就扫描了2万多就够了 ，速度得到了提升。
- 和
- gmt_create，win_type 扫描的索引块多了很多，相当于扫描了 gmt_create当天所有的数据了，22万多数据都要扫描一遍，过滤出win_type=1 的数据
- Comment on Jan 9th, 2009 at 11:05 am
- 5. 5棉花糖ONE
- Your comment is awaiting moderation.
- 到底咋回事啊
- Comment on Jan 9th, 2009 at 10:45 am
- 6. 6棉花糖ONE
- 逻辑读89是不是搞错了
- Comment on Jan 9th, 2009 at 10:45 am
- 7. 7jlttt
- 我们很容易估算出这个sql需要的逻辑读，(gmt_date字段7个字节+win_type字段1个字节+rowid+…）*2万，小于100个
- （7＋1＋10＋…）×2W 所读的块怎么算出小于100的？
- Comment on Jan 9th, 2009 at 9:46 am
- 8. 8棉花糖ONE
- to 木匠:
- cardinality=1可能和9i有关,sysdate包含函数计算的时候,没有直方图的情况下,范围查询的选择度是按5%算的,2个5%*5%
- Comment on Jan 9th, 2009 at 9:42 am
- 9. 9棉花糖ONE
- 如果把范围的放前面,索引的第二个字段只是起到filter的作用,并没有减少索引扫描的块,从索引的成本计算也能看出这一点
- Comment on Jan 9th, 2009 at 9:36 am
- 10. 10木匠
- 对不起,再唠叨一句.
- 除了比较cost,还要比较Cardinality, 第二个SQL,index scan这一步的Card=1.
- 但是我觉得index scan这一步的Cardinality应该大于一, 不知道哪里出了问题.
- 每一个Oracle版本的Cost and Card会有不同的结果.9.2.0.6 and 10.1.0.4或更高版本会提供更有用的信息.
- (木匠真够粗心大意的, 请包涵)
- Comment on Jan 9th, 2009 at 2:26 am
- 11. 11木匠
- In this case, the effective index selectivity has to be calculated from the predicate on just the “gmt_create” column. Because the test on “gmt_create” is range-based, the predicates on “win_type” do not restrict the number of index leaf blocks we have to walk.
- 照搬老刘(Lewis)的原话, 改一下列名就行了. :)
- 上一个评论中的cost 指的是整个SQL(with index scan)的cost.
- 另外, 你忘了记录走第一个索引的SQL执行计划,可以比较一下 INDEX (RANGE SCAN) 的 cost.
- INDEX (RANGE SCAN) OF ‘idx_union123′ (NON-UNIQUE) (Cost=7 Card=1 Bytes=9), 我们看到走第二个索引的(index range scan) cost=7.
- Comment on Jan 9th, 2009 at 2:03 am
- 12. 12木匠
- Q: 为什么还要把win_type放在索引的前面呢？
- A: leaf_blocks * effective index selectivity
- 第一个索引, range scan on gmt_create, first column on index.
- Because as soon as we have a range scan on a column used somewhere in the
- middle of an index definition or fail to supply a test on such a column, the predicates on later columns do not restrict the selection of index leaf blocks that we have to examine.
- 参考:
- cost =
- blevel +
- ceiling(leaf_blocks * effective index selectivity) +
- ceiling(clustering_factor * effective table selectivity)
- a well-known guideline for arranging the columns in a multicolumn
- index. Columns that usually appear with range-based tests should generally appear later in the index than columns that usually appear with equality tests. Unfortunately, changing the column ordering in an index can have other contrary effects, which we will examine in Chapter 5.
- Now you got test case. very well ! ^_^
- Reference: page 74,62,67 of book Cost-Based Oracle Fundamentals
- Comment on Jan 9th, 2009 at 1:52 am
#1