联合索引的经典例子

1.SQL需求，统计当天的数据量。
[coolcode]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.
[/coolcode]
2.查看其索引，以gmt_create开头。
[coolcode]
sql>create index idx_union on test_union (gmt_create,win_type) tablespace tbs_index compute statistics;
[/coolcode]
3.查看awr报表的性能，逻辑读很高，达到9700个。

[coolcode]
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
[/coolcode]
因为是只通过索引扫描，当看到返回结果集在2万左右，我们很容易估算出这个sql需要的逻辑读，(gmt_date字段7个字节+win_type字段1个字节+rowid+...）*2万，小于100个，现在很明显是偏高的。
4.调整前我们先去看数据分布。
[coolcode]
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.
[/coolcode]
5.调整索引,改为以win_type开头，为什么要以win_type开头呢？
[coolcode]
create index idx_union123 on test_union (win_type,gmt_create) tablespace tbs_index compute statistics; --新索引
[/coolcode]
6.查看其执行计划，逻辑读变成了89。
[coolcode]
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
[/coolcode]
都在说建索引一定要看数据分布，从数据分布来看，一天的的数据(gmt_create)量在22万左右，而win_type的数据量非常大，win_type为1有300万左右，为什么还要把win_type放在索引的前面呢？抛块砖，希望大家能对联合索引有更深入的理解，希望一起讨论。

http://www.taobaodba.com/html/245_union_index.html