<p> 组合索引(concatenated index)：由多个列构成的索引，如create index idx_emp on emp(col1, col2, col3, ……)，则我们称idx_emp索引为组合索引。 </p> <p> 在组合索引中有一个重要的概念：引导列(leading column)，在上面的例子中，col1列为引导列。当我们进行查询时可以使用”where col1 = ? ”，也可以使用”where col1 = ? and col2 = ?”，这样的限制条件都会使用索引，但是”where col2 = ? ”查询就不会使用该索引。所以限制条件中包含先导列时，该限制条件才会使用该组合索引。 </p> <p> <br /> </p> <p> 有这样一个SQL&nbsp; <br /> select count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';&nbsp; <br /> id列选择性很高，owner选择性很低&nbsp; <br /> 要优化它很简单，只需要在t1表上建立一个组合索引(owner,id)，在t2表上建立一个索引(id)&nbsp; <br /> 现在要讨论的是我们应该怎么建立组合索引，哪一列(owner,id)应该放在最前面？&nbsp; <br /> &nbsp; <br /> 现在来做个实验&nbsp; <br /> &nbsp; <br /> SQL&gt; desc t1&nbsp; <br /> Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Nullable Default Comments&nbsp;&nbsp; <br /> ----------- ------------- -------- ------- --------&nbsp;&nbsp; <br /> ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUMBER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br /> OBJECT_NAME VARCHAR2(128) Y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br /> OWNER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VARCHAR2(30)&nbsp; Y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br /> &nbsp; <br /> SQL&gt; desc t2&nbsp; <br /> Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Nullable Default Comments&nbsp;&nbsp; <br /> --------- ----------- -------- ------- --------&nbsp;&nbsp; <br /> ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUMBER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br /> STATUS&nbsp;&nbsp;&nbsp; VARCHAR2(7) Y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br /> TEMPORARY VARCHAR2(1) Y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br /> &nbsp; <br /> &nbsp; <br /> SQL&gt; create index inx_id on t2(id);&nbsp; <br /> &nbsp; <br /> Index created.&nbsp; <br /> &nbsp; <br /> SQL&gt; create index inx_id_owner on t1(id,owner);&nbsp; <br /> &nbsp; <br /> Index created.&nbsp; <br /> &nbsp; <br /> SQL&gt; select count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';&nbsp; <br /> &nbsp; <br /> Elapsed: 00:00:00.02&nbsp; <br /> &nbsp; <br /> Execution Plan&nbsp; <br /> ----------------------------------------------------------&nbsp; <br /> Plan hash value: 2432674005&nbsp; <br /> &nbsp; <br /> ---------------------------------------------------------------------------------------&nbsp; <br /> | Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; <br /> ---------------------------------------------------------------------------------------&nbsp; <br /> |&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp; 16 |&nbsp;&nbsp;&nbsp; 88&nbsp;&nbsp; (2)| 00:00:02 |&nbsp; <br /> |&nbsp;&nbsp; 1 |&nbsp; SORT AGGREGATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp; 16 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; <br /> |*&nbsp; 2 |&nbsp;&nbsp; HASH JOIN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 2416 | 38656 |&nbsp;&nbsp;&nbsp; 88&nbsp;&nbsp; (2)| 00:00:02 |&nbsp; <br /> |*&nbsp; 3 |&nbsp;&nbsp;&nbsp; INDEX FAST FULL SCAN| INX_ID_OWNER |&nbsp; 2416 | 26576 |&nbsp;&nbsp;&nbsp; 50&nbsp;&nbsp; (0)| 00:00:01 |&nbsp; <br /> |&nbsp;&nbsp; 4 |&nbsp;&nbsp;&nbsp; INDEX FAST FULL SCAN| INX_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 72475 |&nbsp;&nbsp; 353K|&nbsp;&nbsp;&nbsp; 37&nbsp;&nbsp; (0)| 00:00:01 |&nbsp; <br /> ---------------------------------------------------------------------------------------&nbsp; <br /> &nbsp; <br /> Predicate Information (identified by operation id):&nbsp; <br /> ---------------------------------------------------&nbsp; <br /> &nbsp; <br /> &nbsp;&nbsp; 2 - access("T1"."ID"="T2"."ID")&nbsp; <br /> &nbsp;&nbsp; 3 - filter("T1"."OWNER"='SCOTT')&nbsp; <br /> &nbsp; <br /> &nbsp; <br /> Statistics&nbsp; <br /> ----------------------------------------------------------&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; recursive calls&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; db block gets&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 392&nbsp; consistent gets&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; physical reads&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; redo size&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 422&nbsp; bytes sent via SQL*Net to client&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 420&nbsp; bytes received via SQL*Net from client&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp; SQL*Net roundtrips to/from client&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; sorts (memory)&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; sorts (disk)&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp; rows processed&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br /> SQL&gt; create index inx_owner_id on t1(owner,id);&nbsp; <br /> &nbsp; <br /> Index created.&nbsp; <br /> &nbsp; <br /> SQL&gt; select count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';&nbsp; <br /> &nbsp; <br /> Elapsed: 00:00:00.03&nbsp; <br /> &nbsp; <br /> Execution Plan&nbsp; <br /> ----------------------------------------------------------&nbsp; <br /> Plan hash value: 277464349&nbsp; <br /> &nbsp; <br /> ---------------------------------------------------------------------------------------&nbsp; <br /> | Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; <br /> ---------------------------------------------------------------------------------------&nbsp; <br /> |&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp; 16 |&nbsp;&nbsp;&nbsp; 47&nbsp;&nbsp; (3)| 00:00:01 |&nbsp; <br /> |&nbsp;&nbsp; 1 |&nbsp; SORT AGGREGATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp; 16 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; <br /> |*&nbsp; 2 |&nbsp;&nbsp; HASH JOIN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 2416 | 38656 |&nbsp;&nbsp;&nbsp; 47&nbsp;&nbsp; (3)| 00:00:01 |&nbsp; <br /> |*&nbsp; 3 |&nbsp;&nbsp;&nbsp; INDEX RANGE SCAN&nbsp;&nbsp;&nbsp; | INX_OWNER_ID |&nbsp; 2416 | 26576 |&nbsp;&nbsp;&nbsp;&nbsp; 9&nbsp;&nbsp; (0)| 00:00:01 |&nbsp; <br /> |&nbsp;&nbsp; 4 |&nbsp;&nbsp;&nbsp; INDEX FAST FULL SCAN| INX_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 72475 |&nbsp;&nbsp; 353K|&nbsp;&nbsp;&nbsp; 37&nbsp;&nbsp; (0)| 00:00:01 |&nbsp; <br /> ---------------------------------------------------------------------------------------&nbsp; <br /> &nbsp; <br /> Predicate Information (identified by operation id):&nbsp; <br /> ---------------------------------------------------&nbsp; <br /> &nbsp; <br /> &nbsp;&nbsp; 2 - access("T1"."ID"="T2"."ID")&nbsp; <br /> &nbsp;&nbsp; 3 - access("T1"."OWNER"='SCOTT')&nbsp; <br /> &nbsp; <br /> &nbsp; <br /> Statistics&nbsp; <br /> ----------------------------------------------------------&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; recursive calls&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; db block gets&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 169&nbsp; consistent gets&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; physical reads&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; redo size&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 422&nbsp; bytes sent via SQL*Net to client&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 420&nbsp; bytes received via SQL*Net from client&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp; SQL*Net roundtrips to/from client&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; sorts (memory)&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; sorts (disk)&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp; rows processed&nbsp; <br /> &nbsp; <br /> 如果OWNER作为引导列，逻辑读由以前的392变成现在的169，并且由以前的 index fast full scan 变成index range scan&nbsp; <br /> &nbsp; <br /> 如果强制指定走索引 inx_id_owner&nbsp;&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br /> SQL&gt;&nbsp; select /*+ index(t1 inx_id_owner) */ count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';&nbsp; <br /> &nbsp; <br /> Elapsed: 00:00:00.03&nbsp; <br /> &nbsp; <br /> Execution Plan&nbsp; <br /> ----------------------------------------------------------&nbsp; <br /> Plan hash value: 3161475902&nbsp; <br /> &nbsp; <br /> ---------------------------------------------------------------------------------------&nbsp; <br /> | Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; <br /> ---------------------------------------------------------------------------------------&nbsp; <br /> |&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp; 16 |&nbsp;&nbsp; 259&nbsp;&nbsp; (1)| 00:00:04 |&nbsp; <br /> |&nbsp;&nbsp; 1 |&nbsp; SORT AGGREGATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp; 16 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; <br /> |*&nbsp; 2 |&nbsp;&nbsp; HASH JOIN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 2416 | 38656 |&nbsp;&nbsp; 259&nbsp;&nbsp; (1)| 00:00:04 |&nbsp; <br /> |*&nbsp; 3 |&nbsp;&nbsp;&nbsp; INDEX FULL SCAN&nbsp;&nbsp;&nbsp;&nbsp; | INX_ID_OWNER |&nbsp; 2416 | 26576 |&nbsp;&nbsp; 221&nbsp;&nbsp; (1)| 00:00:03 |&nbsp; <br /> |&nbsp;&nbsp; 4 |&nbsp;&nbsp;&nbsp; INDEX FAST FULL SCAN| INX_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 72475 |&nbsp;&nbsp; 353K|&nbsp;&nbsp;&nbsp; 37&nbsp;&nbsp; (0)| 00:00:01 |&nbsp; <br /> ---------------------------------------------------------------------------------------&nbsp; <br /> &nbsp; <br /> Predicate Information (identified by operation id):&nbsp; <br /> ---------------------------------------------------&nbsp; <br /> &nbsp; <br /> &nbsp;&nbsp; 2 - access("T1"."ID"="T2"."ID")&nbsp; <br /> &nbsp;&nbsp; 3 - access("T1"."OWNER"='SCOTT')&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; filter("T1"."OWNER"='SCOTT')&nbsp; <br /> &nbsp; <br /> &nbsp; <br /> Statistics&nbsp; <br /> ----------------------------------------------------------&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; recursive calls&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; db block gets&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 387&nbsp; consistent gets&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; physical reads&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; redo size&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 422&nbsp; bytes sent via SQL*Net to client&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 420&nbsp; bytes received via SQL*Net from client&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp; SQL*Net roundtrips to/from client&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; sorts (memory)&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; sorts (disk)&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp; rows processed&nbsp; <br /> &nbsp; <br /> 依然要387个逻辑读。&nbsp; <br /> &nbsp; <br /> 为什么要以owner为引导列？因为ID是join列，并且ID列上面没过滤条件，如果以ID列作为引导列，由于没过滤条件&nbsp; <br /> 那么CBO只能走 index full scan,或者index fast full scan，因为引导列没过滤条件，走不了index range scan,&nbsp; <br /> 最多走index skip scan，不过index skip scan代价过高，因为index skip scan要求 引导列选择性很低，但是ID这里选择性很高&nbsp; <br /> &nbsp; <br /> SQL&gt; select /*+ index_ss(t1 inx_id_owner) */ count(*) from t1,t2 where t1.id=t2.id and t1.owner='SYS';&nbsp; <br /> &nbsp; <br /> Elapsed: 00:00:00.10&nbsp; <br /> &nbsp; <br /> Execution Plan&nbsp; <br /> ----------------------------------------------------------&nbsp; <br /> Plan hash value: 3493079762&nbsp; <br /> &nbsp; <br /> ---------------------------------------------------------------------------------------&nbsp; <br /> | Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; <br /> ---------------------------------------------------------------------------------------&nbsp; <br /> |&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp; 16 | 72529&nbsp;&nbsp; (1)| 00:14:31 |&nbsp; <br /> |&nbsp;&nbsp; 1 |&nbsp; SORT AGGREGATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp; 16 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; <br /> |*&nbsp; 2 |&nbsp;&nbsp; HASH JOIN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 2416 | 38656 | 72529&nbsp;&nbsp; (1)| 00:14:31 |&nbsp; <br /> |*&nbsp; 3 |&nbsp;&nbsp;&nbsp; INDEX SKIP SCAN&nbsp;&nbsp;&nbsp;&nbsp; | INX_ID_OWNER |&nbsp; 2416 | 26576 | 72491&nbsp;&nbsp; (1)| 00:14:30 |&nbsp; <br /> |&nbsp;&nbsp; 4 |&nbsp;&nbsp;&nbsp; INDEX FAST FULL SCAN| INX_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 72475 |&nbsp;&nbsp; 353K|&nbsp;&nbsp;&nbsp; 37&nbsp;&nbsp; (0)| 00:00:01 |&nbsp; <br /> ---------------------------------------------------------------------------------------&nbsp; <br /> &nbsp; <br /> Predicate Information (identified by operation id):&nbsp; <br /> ---------------------------------------------------&nbsp; <br /> &nbsp; <br /> &nbsp;&nbsp; 2 - access("T1"."ID"="T2"."ID")&nbsp; <br /> &nbsp;&nbsp; 3 - access("T1"."OWNER"='SYS')&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; filter("T1"."OWNER"='SYS')&nbsp; <br /> &nbsp; <br /> &nbsp; <br /> Statistics&nbsp; <br /> ----------------------------------------------------------&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp; recursive calls&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; db block gets&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 387&nbsp; consistent gets&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; physical reads&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; redo size&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 424&nbsp; bytes sent via SQL*Net to client&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 420&nbsp; bytes received via SQL*Net from client&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp; SQL*Net roundtrips to/from client&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; sorts (memory)&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; sorts (disk)&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp; rows processed&nbsp; <br /> &nbsp; <br /> 如果owner列作为引导列，那么优化器就可以选择index range scan,这样相比index full scan, index fast full scan&nbsp; <br /> 肯定要少扫描很多leaf block,逻辑读就会相对较少。&nbsp; <br /> &nbsp; <br /> 其实到这里，是否可以总结一下建立组合索引的原则呢？&nbsp; <br /> 引导列要选择过滤条件的列作为引导列，比如 where a.xxx='xxx' 或者 a.xxx&gt; 或者 a.xxx&lt;&nbsp; <br /> 引导列的选择性越高越好，因为选择性越高，扫描的leaf block就越少，效率就越高&nbsp; <br /> 尽量把join列放到组合索引最后面&nbsp; <br /> &nbsp; <br /> 这里选择以owner列作为引导列，由于owner选择性很低，所以测试索引压缩对于性能的提升&nbsp; <br /> &nbsp; <br /> SQL&gt; analyze&nbsp; index inx_owner_id validate structure;&nbsp; <br /> &nbsp; <br /> Index analyzed.&nbsp; <br /> &nbsp; <br /> SQL&gt; select height,&nbsp; <br /> &nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; blocks,&nbsp; <br /> &nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; lf_blks,&nbsp; <br /> &nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; br_blks,&nbsp; <br /> &nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OPT_CMPR_COUNT,&nbsp; <br /> &nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OPT_CMPR_PCTSAVE&nbsp; <br /> &nbsp; 7&nbsp;&nbsp;&nbsp; from index_stats&nbsp; <br /> &nbsp; 8&nbsp;&nbsp; where name = 'INX_OWNER_ID';&nbsp; <br /> &nbsp; <br /> &nbsp;&nbsp;&nbsp; HEIGHT&nbsp;&nbsp;&nbsp;&nbsp; BLOCKS&nbsp;&nbsp;&nbsp; LF_BLKS&nbsp;&nbsp;&nbsp; BR_BLKS OPT_CMPR_COUNT OPT_CMPR_PCTSAVE&nbsp; <br /> ---------- ---------- ---------- ---------- -------------- ----------------&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 256&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 219&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 26&nbsp; <br /> &nbsp;&nbsp; <br /> SQL&gt; drop index inx_owner_id;&nbsp; <br /> &nbsp; <br /> Index dropped&nbsp; <br /> &nbsp; <br /> SQL&gt; create index inx_owner_id on t1(owner,id) compress 1;&nbsp; <br /> &nbsp; <br /> Index created&nbsp; <br /> &nbsp; <br /> SQL&gt; analyze&nbsp; index inx_owner_id validate structure;&nbsp; <br /> &nbsp; <br /> Index analyzed&nbsp; <br /> &nbsp; <br /> SQL&gt;&nbsp;&nbsp; <br /> SQL&gt; select height,&nbsp; <br /> &nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; blocks,&nbsp; <br /> &nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; lf_blks,&nbsp; <br /> &nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; br_blks,&nbsp; <br /> &nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OPT_CMPR_COUNT,&nbsp; <br /> &nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OPT_CMPR_PCTSAVE&nbsp; <br /> &nbsp; 7&nbsp;&nbsp;&nbsp; from index_stats&nbsp; <br /> &nbsp; 8&nbsp;&nbsp; where name = 'INX_OWNER_ID';&nbsp; <br /> &nbsp; <br /> &nbsp;&nbsp;&nbsp; HEIGHT&nbsp;&nbsp;&nbsp;&nbsp; BLOCKS&nbsp;&nbsp;&nbsp; LF_BLKS&nbsp;&nbsp;&nbsp; BR_BLKS OPT_CMPR_COUNT OPT_CMPR_PCTSAVE&nbsp; <br /> ---------- ---------- ---------- ---------- -------------- ----------------&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 256&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 161&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br /> 索引压缩之后，Leaf block 由原来的219降低到161个，节约了58个block 现在再来看一看执行计划+统计信息&nbsp; <br /> &nbsp; <br /> SQL&gt; select count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';&nbsp; <br /> &nbsp; <br /> Elapsed: 00:00:00.03&nbsp; <br /> &nbsp; <br /> Execution Plan&nbsp; <br /> ----------------------------------------------------------&nbsp; <br /> Plan hash value: 277464349&nbsp; <br /> &nbsp; <br /> ---------------------------------------------------------------------------------------&nbsp; <br /> | Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; <br /> ---------------------------------------------------------------------------------------&nbsp; <br /> |&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp; 16 |&nbsp;&nbsp;&nbsp; 45&nbsp;&nbsp; (3)| 00:00:01 |&nbsp; <br /> |&nbsp;&nbsp; 1 |&nbsp; SORT AGGREGATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp; 16 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; <br /> |*&nbsp; 2 |&nbsp;&nbsp; HASH JOIN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 2416 | 38656 |&nbsp;&nbsp;&nbsp; 45&nbsp;&nbsp; (3)| 00:00:01 |&nbsp; <br /> |*&nbsp; 3 |&nbsp;&nbsp;&nbsp; INDEX RANGE SCAN&nbsp;&nbsp;&nbsp; | INX_OWNER_ID |&nbsp; 2416 | 26576 |&nbsp;&nbsp;&nbsp;&nbsp; 7&nbsp;&nbsp; (0)| 00:00:01 |&nbsp; <br /> |&nbsp;&nbsp; 4 |&nbsp;&nbsp;&nbsp; INDEX FAST FULL SCAN| INX_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 72475 |&nbsp;&nbsp; 353K|&nbsp;&nbsp;&nbsp; 37&nbsp;&nbsp; (0)| 00:00:01 |&nbsp; <br /> ---------------------------------------------------------------------------------------&nbsp; <br /> &nbsp; <br /> Predicate Information (identified by operation id):&nbsp; <br /> ---------------------------------------------------&nbsp; <br /> &nbsp; <br /> &nbsp;&nbsp; 2 - access("T1"."ID"="T2"."ID")&nbsp; <br /> &nbsp;&nbsp; 3 - access("T1"."OWNER"='SCOTT')&nbsp; <br /> &nbsp; <br /> &nbsp; <br /> Statistics&nbsp; <br /> ----------------------------------------------------------&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp; recursive calls&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; db block gets&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 169&nbsp; consistent gets&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; physical reads&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; redo size&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 422&nbsp; bytes sent via SQL*Net to client&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 420&nbsp; bytes received via SQL*Net from client&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp; SQL*Net roundtrips to/from client&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; sorts (memory)&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; sorts (disk)&nbsp; <br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp; rows processed&nbsp; <br /> &nbsp; <br /> 由此可见，索引压缩之后，逻辑读并没有下降，还是169，但是索引的leaf blcok显著减少了，这样减少了存储空间，能降低物理IO&nbsp; </p>