有这样一个SQLselect count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';id列选择性很高，owner选择性很低要优化它很简单，只需要在t1表上建立一个组合索引(owner,id)，在t2表上建立一个索引(id)现在要讨论的是我们应该怎么建立组合索引，哪一列(owner,id)应该放在最前面？现在来做个实验SQL> desc t1Name TypeNullable Default Comments ----------- ------------- -------- ------- -------- IDNUMBER YOBJECT_NAME VARCHAR2(128) YOWNER VARCHAR2(30) YSQL> desc t2NameType Nullable Default Comments --------- ----------- -------- ------- -------- ID NUMBERYSTATUS VARCHAR2(7) YTEMPORARY VARCHAR2(1) Y SQL> create index inx_id on t2(id);Index created.SQL> create index inx_id_owner on t1(id,owner);Index created.SQL> select count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';Elapsed: 00:00:00.02Execution Plan----------------------------------------------------------Plan hash value: 2432674005---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | |1 | 16 | 88 (2)| 00:00:02 || 1 | SORT AGGREGATE | |1 | 16 | |||* 2 | HASH JOIN | | 2416 | 38656 | 88 (2)| 00:00:02 ||* 3 | INDEX FAST FULL SCAN| INX_ID_OWNER | 2416 | 26576 | 50 (0)| 00:00:01 || 4 | INDEX FAST FULL SCAN| INX_ID | 72475 | 353K| 37 (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("T1"."ID"="T2"."ID")3 - filter("T1"."OWNER"='SCOTT')Statistics----------------------------------------------------------0 recursive calls0 db block gets392 consistent gets0 physical reads0 redo size422 bytes sent via SQL*Net to client420 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processedSQL> create index inx_owner_id on t1(owner,id);Index created.SQL> select count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';Elapsed: 00:00:00.03Execution Plan----------------------------------------------------------Plan hash value: 277464349---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | |1 | 16 | 47 (3)| 00:00:01 || 1 | SORT AGGREGATE | |1 | 16 | |||* 2 | HASH JOIN | | 2416 | 38656 | 47 (3)| 00:00:01 ||* 3 | INDEX RANGE SCAN | INX_OWNER_ID | 2416 | 26576 |9 (0)| 00:00:01 || 4 | INDEX FAST FULL SCAN| INX_ID | 72475 | 353K| 37 (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("T1"."ID"="T2"."ID")3 - access("T1"."OWNER"='SCOTT')Statistics----------------------------------------------------------0 recursive calls0 db block gets169 consistent gets0 physical reads0 redo size422 bytes sent via SQL*Net to client420 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed如果OWNER作为引导列，逻辑读由以前的392变成现在的169，并且由以前的 index fast full scan 变成index range scan如果强制指定走索引 inx_id_owner SQL> select /*+ index(t1 inx_id_owner) */ count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';Elapsed: 00:00:00.03Execution Plan----------------------------------------------------------Plan hash value: 3161475902---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | |1 | 16 | 259 (1)| 00:00:04 || 1 | SORT AGGREGATE | |1 | 16 | |||* 2 | HASH JOIN | | 2416 | 38656 | 259 (1)| 00:00:04 ||* 3 | INDEX FULL SCAN| INX_ID_OWNER | 2416 | 26576 | 221 (1)| 00:00:03 || 4 | INDEX FAST FULL SCAN| INX_ID | 72475 | 353K| 37 (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("T1"."ID"="T2"."ID")3 - access("T1"."OWNER"='SCOTT')filter("T1"."OWNER"='SCOTT')Statistics----------------------------------------------------------0 recursive calls0 db block gets387 consistent gets0 physical reads0 redo size422 bytes sent via SQL*Net to client420 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed依然要387个逻辑读。为什么要以owner为引导列？因为ID是join列，并且ID列上面没过滤条件，如果以ID列作为引导列，由于没过滤条件那么CBO只能走 index full scan,或者index fast full scan，因为引导列没过滤条件，走不了index range scan,最多走index skip scan，不过index skip scan代价过高，因为index skip scan要求 引导列选择性很低，但是ID这里选择性很高SQL> select /*+ index_ss(t1 inx_id_owner) */ count(*) from t1,t2 where t1.id=t2.id and t1.owner='SYS';Elapsed: 00:00:00.10Execution Plan----------------------------------------------------------Plan hash value: 3493079762---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | |1 | 16 | 72529 (1)| 00:14:31 || 1 | SORT AGGREGATE | |1 | 16 | |||* 2 | HASH JOIN | | 2416 | 38656 | 72529 (1)| 00:14:31 ||* 3 | INDEX SKIP SCAN| INX_ID_OWNER | 2416 | 26576 | 72491 (1)| 00:14:30 || 4 | INDEX FAST FULL SCAN| INX_ID | 72475 | 353K| 37 (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("T1"."ID"="T2"."ID")3 - access("T1"."OWNER"='SYS')filter("T1"."OWNER"='SYS')Statistics----------------------------------------------------------1 recursive calls0 db block gets387 consistent gets0 physical reads0 redo size424 bytes sent via SQL*Net to client420 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed如果owner列作为引导列，那么优化器就可以选择index range scan,这样相比index full scan, index fast full scan肯定要少扫描很多leaf block,逻辑读就会相对较少。其实到这里，是否可以总结一下建立组合索引的原则呢？引导列要选择过滤条件的列作为引导列，比如 where a.xxx='xxx' 或者 a.xxx> 或者 a.xxx<引导列的选择性越高越好，因为选择性越高，扫描的leaf block就越少，效率就越高尽量把join列放到组合索引最后面这里选择以owner列作为引导列，由于owner选择性很低，所以测试索引压缩对于性能的提升SQL> analyze index inx_owner_id validate structure;Index analyzed.SQL> select height,2 blocks,3 lf_blks,4 br_blks,5 OPT_CMPR_COUNT,6 OPT_CMPR_PCTSAVE7 from index_stats8 where name = 'INX_OWNER_ID';HEIGHTBLOCKS LF_BLKS BR_BLKS OPT_CMPR_COUNT OPT_CMPR_PCTSAVE---------- ---------- ---------- ---------- -------------- ----------------2 256 2191 126SQL> drop index inx_owner_id;Index droppedSQL> create index inx_owner_id on t1(owner,id) compress 1;Index createdSQL> analyze index inx_owner_id validate structure;Index analyzedSQL> SQL> select height,2 blocks,3 lf_blks,4 br_blks,5 OPT_CMPR_COUNT,6 OPT_CMPR_PCTSAVE7 from index_stats8 where name = 'INX_OWNER_ID';HEIGHTBLOCKS LF_BLKS BR_BLKS OPT_CMPR_COUNT OPT_CMPR_PCTSAVE---------- ---------- ---------- ---------- -------------- ----------------2 256 1611 10索引压缩之后，Leaf block 由原来的219降低到161个，节约了58个block 现在再来看一看执行计划+统计信息SQL> select count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';Elapsed: 00:00:00.03Execution Plan----------------------------------------------------------Plan hash value: 277464349---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | |1 | 16 | 45 (3)| 00:00:01 || 1 | SORT AGGREGATE | |1 | 16 | |||* 2 | HASH JOIN | | 2416 | 38656 | 45 (3)| 00:00:01 ||* 3 | INDEX RANGE SCAN | INX_OWNER_ID | 2416 | 26576 |7 (0)| 00:00:01 || 4 | INDEX FAST FULL SCAN| INX_ID | 72475 | 353K| 37 (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("T1"."ID"="T2"."ID")3 - access("T1"."OWNER"='SCOTT')Statistics----------------------------------------------------------1 recursive calls0 db block gets169 consistent gets0 physical reads0 redo size422 bytes sent via SQL*Net to client420 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed由此可见，索引压缩之后，逻辑读并没有下降，还是169，但是索引的leaf blcok显著减少了，这样减少了存储空间，能降低物理IO
