联合索引的列顺序问题

Question

问题一:
在学习索引优化的时候,网上找到的内容出现了分支:
1.联合索引，不同值越少的列，越要放在前面,因顺序不同，影响索引的选择
2.联合索引,分布不均匀的,不同值多的列,放在前面,因不同值多的列在第一位能一下子把筛选的数据量降低这两种观点正好相反,到底哪种是正确的,请高人指点.
问题二:
一个查询语句,若有5个where条件,那联合索引需要建5个字段吗?第一个字段能删选总数据量的30%,第二个能删选剩下的20%,第三个能筛选剩下的10%,第四个能筛选剩下的60%,第五个能筛选剩下的70%
如总数据量有100万,字段A能筛选出30W,字段B能筛选30W中的6W,字段C能筛选6W中的6K....
我看有分析说只需要把前三个能筛选效率高的做成组合索引集合,剩下的2个字段若加入联合索引,会增大索引的大小,反而不利于索引的使用,这种说法对吗?

Answer 1

Composite indexes can speed retrieval of data for SELECT statements in which the
WHERE clause references all or the leading portion of the columns in the composite
index. Therefore, the order of the columns used in the definition is important.
Generally, the most commonly accessed or most selective columns go first.
应该选择经常出现在where条件中且选择性较高的列作为联合索引的首列.所以问题一答案应该是2.

Answer 2

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 12月 22 22:58:11 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.
连接到: 
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining optionsSQL> set linesize 100;
SQL> create table myobj as select t.OBJECT_ID,t.OBJECT_NAME,t.OBJECT_TYPE from dba_objects t;表已创建。SQL> create index idx_id_name on myobj(object_id,object_name);索引已创建。SQL> exec dbms_stats.gather_table_stats('test','myobj',cascade => true);PL/SQL 过程已成功完成。SQL> set autot traceonly;
SQL> alter system flush buffer_cache;系统已更改。SQL> alter system flush shared_pool;系统已更改。SQL> select * from myobj t where t.object_id=46 and t.object_name='I_COL2';
执行计划
----------------------------------------------------------
Plan hash value: 2214575557-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYOBJ       |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ID_NAME |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("T"."OBJECT_ID"=46 AND "T"."OBJECT_NAME"='I_COL2')
统计信息
----------------------------------------------------------
       1146  recursive calls
          0  db block gets
        214  consistent gets
         28  physical reads
          0  redo size
        548  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         30  sorts (memory)
          0  sorts (disk)
          1  rows processedSQL> set autot off;
SQL> drop index idx_id_name;索引已删除。SQL> create index idx_name_id on myobj(object_name,object_id);索引已创建。SQL> exec dbms_stats.gather_table_stats('test','myobj',cascade => true);PL/SQL 过程已成功完成。SQL> set autot traceonly;
SQL> alter system flush buffer_cache;系统已更改。SQL> alter system flush shared_pool;系统已更改。SQL> select * from myobj t where t.object_id=46 and t.object_name='I_COL2';
执行计划
----------------------------------------------------------
Plan hash value: 3475832761-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    37 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYOBJ       |     1 |    37 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_NAME_ID |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("T"."OBJECT_NAME"='I_COL2' AND "T"."OBJECT_ID"=46)
统计信息
----------------------------------------------------------
        330  recursive calls
          0  db block gets
         53  consistent gets
         14  physical reads
          0  redo size
        548  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processedSQL>