联合索引有一个字段用in 联合索引in怎么匹配

联合索引的使用，如果在表的两个字段上建立联合索引，在查询时如果谓词中没有出现第一个字段，仍然可以通过index skip scan的方式实现索引访问的方式。但是要注意的是不是所有的情况使用index skip scan都是最优的。当联合索引的第一个字段相同的值很多，大部分是相同的，这种情况下使用index skip scan的方式是最优的。

SQL> create table t as select 1 id, object_name from dba_objects;

Table created.

SQL> insert into t select 2, object_name from dba_objects;

50319 rows created.

SQL> insert into t select 3, object_name from dba_objects;

50319 rows created.

SQL> insert into t select 4, object_name from dba_objects;

50319 rows created.

SQL> commit ;

Commit complete.
SQL> select id,count(*) from t group by id;

        ID   COUNT(*)
---------- ----------
         1      50319
         2      50319
         4      50319
         3      50319

可以看到表t的值集中在ID为1,2,3,4几个值上面，这时选择Index Skip Scan的访问方式是最优的。分析如下：

SQL> select * from t where object_name='TEST';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3688940926

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     7 |   189 |     6   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | IND_T |     7 |   189 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - access("OBJECT_NAME"='TEST')
       filter("OBJECT_NAME"='TEST')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         14  consistent gets
         13  physical reads
          0  redo size
        388  bytes sent via SQL*Net to client
        458  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

当使用hints使其采用全表扫描的方式时

SQL> select /*+ full(t) */ * from t where object_name='TEST';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     7 |   189 |   203   (3)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    |     7 |   189 |   203   (3)| 00:00:03 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"='TEST')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        900  consistent gets
          0  physical reads
          0  redo size
        388  bytes sent via SQL*Net to client
        458  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

由上面可以看到，使用Index Skip Scan时只扫描了14个数据块，而采用全表扫描则扫描了900个数据块。

但是两外一种情况确实截然相反的，当联合索引的第一个值重复很少时，使用全表扫描的效率却要高一些。

转载于:https://blog.51cto.com/wengxs/769996