有这样一个SQL
select count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';
id列选择性很高，owner选择性很低
要优化它很简单，只需要在t1表上建立一个组合索引(owner,id)，在t2表上建立一个索引(id)
现在要讨论的是我们应该怎么建立组合索引，哪一列(owner,id)应该放在最前面？
现在来做个实验
SQL> desc t1
Name Type Nullable Default Comments
----------- ------------- -------- ------- --------
ID NUMBER Y
OBJECT_NAME VARCHAR2(128) Y
OWNER VARCHAR2(30) Y
SQL> desc t2
Name Type Nullable Default Comments
--------- ----------- -------- ------- --------
ID NUMBER Y
STATUS VARCHAR2(7) Y
TEMPORARY 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.02
Execution 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 calls
0 db block gets
392 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> 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.03
Execution 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 calls
0 db block gets
169 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 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.03
Execution 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 calls
0 db block gets
387 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 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.10
Execution 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 calls
0 db block gets
387 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 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_PCTSAVE
7 from index_stats
8 where name = 'INX_OWNER_ID';
HEIGHT BLOCKS LF_BLKS BR_BLKS OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- ---------- -------------- ----------------
2 256 219 1 1 26
SQL> drop index inx_owner_id;
Index dropped
SQL> create index inx_owner_id on t1(owner,id) compress 1;
Index created
SQL> analyze index inx_owner_id validate structure;
Index analyzed
SQL>
SQL> select height,
2 blocks,
3 lf_blks,
4 br_blks,
5 OPT_CMPR_COUNT,
6 OPT_CMPR_PCTSAVE
7 from index_stats
8 where name = 'INX_OWNER_ID';
HEIGHT BLOCKS LF_BLKS BR_BLKS OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- ---------- -------------- ----------------
2 256 161 1 1 0
索引压缩之后，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.03
Execution 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 calls
0 db block gets
169 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
由此可见，索引压缩之后，逻辑读并没有下降，还是169，但是索引的leaf blcok显著减少了，这样减少了存储空间，能降低物理IO
