要在C1,C2两个字段建立复合所用,规则有:如果C1字段使用更频繁一些,那么复合索引的顺序应该是(C1,C2),可以使Oracle更容易用上该索引。
如果C1字段只有(1,2,3)三个离散值,C2字段具有成千上万个离散值,则复合索引的顺序应该为(C1,C2),这样做目的是如果在SQL语句的条件中没有用上C1字段时,使Oracle可以走跳跃式索引扫描
PRODUCTKEY比USERGROUPID用的更频繁,则PRODUCTKEY应该放前面
USERGROUPID只有很少的离散值,当前表里面只有1个,则USERGROUPID应该放前面
这两者好像矛盾了?如下,PRODUCTKEY放前面的执行计划更优一些
索引(USERGROUPID, PRODUCTKEY)
SQL> select USERGROUPID from sysdb.SPY_T_GRPTARIFF where USERGROUPID != -1 and USERGROUPID != -2 and PRODUCTKEY = 2000101002;
no rows selected
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 3899166571
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 52 | 69 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| PK_SPY_T_GRPTARIFF | 2 | 52 | 69 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PRODUCTKEY"=2000101002 AND "USERGROUPID"<>(-1) AND ——怎么过滤还走索引呢? "USERGROUPID"<>(-2))
索引(PRODUCTKEY, USERGROUPID)
Execution Plan
----------------------------------------------------------
Plan hash value: 1177836263
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 52 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| PK_SPY_T_GRPTARIFF | 2 | 52 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PRODUCTKEY"=2000101002)
filter("USERGROUPID"<>(-1) AND "USERGROUPID"<>(-2))