复合索引建立原则: 列值选择性好的放在最前面
所谓列值选择性好,指的是该列中值大部分不一样,只有少部分重复,我们就称之为列值选择性好,如主键,唯一键等.
表CHANNEL_CARD中字段lan_id只有11个不同值,serv_id基本都不同,如果建复合索引时建立为 lan_id,serv_id 与 serv_id,lan_id两种不同顺序,效率是完全不一样的.
SQL> set autotrace on;
SQL> select /*+index IDX_CHANNEL_CARD_3 */lan_id,serv_id,dn_no from CHANNEL_CARD where lan_id=1 and serv_id=1;no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 600194982
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 115 | 4255 | 14 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CHANNEL_CARD | 115 | 4255 | 14 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_CHANNEL_CARD_3 | 18 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SERV_ID"=1 AND "LAN_ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
470 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select /*+index IDX_CHANNEL_CARD_4 */lan_id,serv_id,dn_no from CHANNEL_CARD where lan_id=1 and serv_id=1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3625735485
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 115 | 4255 | 13 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CHANNEL_CARD | 115 | 4255 | 13 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_CHANNEL_CARD_4 | 18 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LAN_ID"=1 AND "SERV_ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
52 recursive calls
1 db block gets
288 consistent gets
5 physical reads
0 redo size
470 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
0 rows processed
逻辑读一个是3,另一个却为288.