索引中字段的顺序确实会对访问性能有所影响，不过并不像很多人想象的那么简单。
这一篇讨论选项性低的列作为前缀索引时执行范围扫描的情况。
索引中字段顺序对访问性能影响（一）：http://yangtingkun.itpub.net/post/468/509188
索引中字段顺序对访问性能影响（二）：http://yangtingkun.itpub.net/post/468/509265
索引中字段顺序对访问性能影响（三）：http://yangtingkun.itpub.net/post/468/509328
上一篇文章将选择性高的字段作为索引的前缀字段，这篇讨论选择性低的字段作为索引前缀的情况。
SQL> drop index ind_t_id_owner;
Index dropped.
SQL> create index ind_t_owner_id
2 on t (owner, object_id);
Index created.
SQL> select object_name
2 from t
3 where object_id > 45000
4 and wner = 'U1';
OBJECT_NAME
--------------------------------
T_BIG
S1
T
Execution Plan
----------------------------------------------------------
Plan hash value: 2014292028
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 35 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OWNER_ID | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='U1' AND "OBJECT_ID">45000 AND "OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
583 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
如果是后缀字段的范围扫描，那么上一篇文章介绍的情况没有什么区别。只不过对于选择性不高的索引而言，由于返回的结果相对较多，因此产生的逻辑读也会较大。
SQL> select object_name
2 from t
3 where owner > 'S'
4 and object_id = 50000;
OBJECT_NAME
------------------------------------------
MGMT_CURRENT_METRIC_ERRORS
Execution Plan
----------------------------------------------------------
Plan hash value: 2801825686
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 25 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 35 | 25 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IND_T_OWNER_ID | 1 | | 24 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER">'S' AND "OBJECT_ID"=50000 AND "OWNER" IS NOT NULL)
filter("OBJECT_ID"=50000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
542 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
当选择性差的索引作为前缀字段，且执行范围扫描，而后缀选择性好的字段进行等值扫描时，执行计划选择了索引跳扫。这就与上一篇文章描述的情况有所区别了。
当前缀字段的DISTINCT值不是很多的情况下，INDEX SKIP SCAN扫描成为可能，这种情况下，Oracle扫描前缀字段每种可能性对应的后缀字段的值。
观察Predicate Information可以发现，OWNER > ‘S’这个前缀条件也出现在ACCESS条件中。这说明什么问题呢，为了更好的说明这个问题，修改一下SQL语句：
SQL> select object_name
2 from t
3 where owner || '' > 'S'
4 and object_id = 50000;
OBJECT_NAME
----------------------------
MGMT_CURRENT_METRIC_ERRORS
Execution Plan
----------------------------------------------------------
Plan hash value: 2801825686
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 60 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 35 | 60 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IND_T_OWNER_ID | 1 | | 59 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=50000)
filter("OBJECT_ID"=50000 AND "OWNER"||''>'S')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
37 consistent gets
0 physical reads
0 redo size
542 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
执行计划没有改变，但是逻辑读却相差很多，原因仍然是ACCESS和FILTER导致的。在前一个语句中，前缀字段的限制条件出现在ACCESS中，说明在执行索引跳扫的时候，会根据前缀索引的条件来确定扫描的范围，因此索引跳扫只扫描前缀字段大于’S’，且不为空的可能性上。而对于而一个SQL语句，前缀字段的查询条件出现在FILTER处，因此索引跳扫需要访问前缀字段的每一种的可能性，产生的逻辑读当然要大得多。
SQL> select object_name
2 from t
3 where owner > 'S'
4 and object_id in (50000, 50001);
OBJECT_NAME
-----------------------------------------------------
MGMT_CURRENT_METRIC_ERRORS
MGMT_TARGET_ASSOC
Execution Plan
----------------------------------------------------------
Plan hash value: 2014292028
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 49 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 35 | 49 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OWNER_ID | 1 | | 48 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER">'S' AND "OWNER" IS NOT NULL)
filter("OBJECT_ID"=50000 OR "OBJECT_ID"=50001)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
612 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
最后看一下前缀字段范围扫描，后缀字段不再是简单相等的情况。这时执行计划与上一篇文章中介绍的情况又恢复了一致。原因是无论后缀字段是IN语句还是范围查询，都使得索引跳扫执行计划不可能成立。显然Oracle不会生成INLIST INDEX SKIP SCAN这种复杂的执行计划。
这时只有使用选择性不高的前缀字段的范围扫描，因此产生的逻辑读显然高得多。
如果将语句修改一下，变成两个UNION ALL的子查询，则语句仍然可以利用索引跳扫：
SQL> select object_name
2 from t
3 where owner > 'S'
4 and object_id = 50000
5 union all
6 select object_name
7 from t
8 where owner > 'S'
9 and object_id = 50001;
OBJECT_NAME
------------------------------
MGMT_CURRENT_METRIC_ERRORS
MGMT_TARGET_ASSOC
Execution Plan
----------------------------------------------------------
Plan hash value: 2279076093
--------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 2 | 70 | 51 (51)| 00:00:01 |
| 1| UNION-ALL | | | | | |
| 2| TABLE ACCESS BY INDEX ROWID| T | 1 | 35 | 25 (0)| 00:00:01 |
|* 3| INDEX SKIP SCAN | IND_T_OWNER_ID | 1 | | 24 (0)| 00:00:01 |
| 4| TABLE ACCESS BY INDEX ROWID| T | 1 | 35 | 25 (0)| 00:00:01 |
|* 5| INDEX SKIP SCAN | IND_T_OWNER_ID | 1 | | 24 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OWNER">'S' AND "OBJECT_ID"=50000 AND "OWNER" IS NOT NULL)
filter("OBJECT_ID"=50000)
5 - access("OWNER">'S' AND "OBJECT_ID"=50001 AND "OWNER" IS NOT NULL)
filter("OBJECT_ID"=50001)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
612 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
就像文章开头说的，索引字段顺序对于访问性能的影响并不像想象中的那么简单，事实上问题的复杂性也超出我的预期。本来打算通过两篇文章描述一下这个问题，后来发现两篇文章说不清楚，等到第三篇的时候发现，如果还仿照前两篇的架构来说明文章，恐怕很容易乱，于是就又分了两篇文章。
这篇文章并没有打算说明选择性高字段作为索引前缀好，还是选择性的字段作为索引前缀好。和很多特性一样，如何做选择要看在什么情况下使用。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-680315/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-680315/
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
