[丁原]联合索引的经典例子

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.

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

因为是只通过索引扫描，当看到返回结果集在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.

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 ptimizer=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放在索引的前面呢？抛块砖，希望大家能对联合索引有更深入的理解，希望一起讨论。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1384/viewspace-611231/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/1384/viewspace-611231/