复合索引建立原则: 列值选择性好的放在最前面

复合索引建立原则: 列值选择性好的放在最前面

所谓列值选择性好,指的是该列中值大部分不一样,只有少部分重复,我们就称之为列值选择性好,如主键,唯一键等.

表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.