索引中字段顺序对访问性能影响（二）

索引中字段的顺序确实会对访问性能有所影响，不过并不像很多人想象的那么简单。

这一篇讨论选项性差的列作为前缀索引的优点。

索引中字段顺序对访问性能影响（一）：http://yangtingkun.itpub.net/post/468/509188

 

 

上一篇文章提到了对于同时包含索引所有列，且条件都是相等的查询，访问性能与前缀列的选择性没有关系。

事实上将选择性差的列作为前缀列，还会有一些优点。

考虑查询只包括一个查询条件的情况，如果是选择性强的列作为前缀列，当查询指定选择性不高的列时，Oracle不会选择索引。而对于选择性差的列作为前缀列，当查询指定选择性高的列时，Oracle可以使用索引SKIP扫描。

继续上一篇文章的例子：

SQL> exec dbms_stats.gather_table_stats(user, 'T')

PL/SQL procedure successfully completed.

SQL> select object_name
  2  from t
  3  where object_id = 1000;

OBJECT_NAME
-------------------------------------------------------
V_$BUFFER_POOL_STATISTICS

Execution Plan
----------------------------------------------------------
Plan hash value: 2801825686

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    28 |    60   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T              |     1 |    28 |    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"=1000)
       filter("OBJECT_ID"=1000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        528  consistent gets
          0  physical reads
          0  redo size
        541  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

而且选择性差的列作为前缀列还可以利用索引压缩技术来减小索引的大小：

SQL> alter index ind_t_owner_id rebuild compress 1;

Index altered.

SQL> select count(*) 
  2  from t
  3  where object_id is not null;

  COUNT(*)
----------
     76381

Execution Plan
----------------------------------------------------------
Plan hash value: 3736582513

----------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |     1 |    13 |    33   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IND_T_OWNER_ID | 89682 |  1138K|    33   (4)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID" IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         91  consistent gets
          0  physical reads
          0  redo size
        517  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

SQL> drop index ind_t_owner_id;

Index dropped.

SQL> create index ind_t_id_owner
  2  on t(owner, object_id);

Index created.

SQL> select count(*) 
  2  from t
  3  where object_id is not null;

  COUNT(*)
----------
     76381

Execution Plan
----------------------------------------------------------
Plan hash value: 3992516678

----------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |     1 |    13 |    44   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IND_T_ID_OWNER | 89682 |  1138K|    44   (3)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID" IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        125  consistent gets
          0  physical reads
          0  redo size
        517  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

显然对于这种索引全扫描的情况，压缩后的非前缀索引的逻辑读更小。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-679698/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-679698/