联合索引疑问

乌鲁木齐-小小

联合索引id在前，name在后，物理读为72，CPU cost也不高
SQL> create index ind_name_id on t(object_id,object_name);

Index created.

Elapsed: 00:00:01.19
SQL> select object_id,object_name from t where object_id < 5;

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
         2 C_OBJ#
         3 I_OBJ#
         4 TAB$

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2897093046

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     3 |   237 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_NAME_ID |     3 |   237 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - access("OBJECT_ID"<5)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         11  recursive calls
          0  db block gets
         72  consistent gets
          1  physical reads
          0  redo size
        681  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

联合索引name在前，id在后，当我这样设置时，物理读变得特别高573
SQL> create index ind_ob_name_and_id on t(object_name,object_id);

Index created.

SQL> select object_name,object_id from t where object_id < 5;

OBJECT_NAME                                                                                                                       OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
C_OBJ#                                                                                                                                    2
I_OBJ#                                                                                                                                    3
TAB$                                                                                                                                      4

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 374587637

-------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |    14 |  1106 |   136   (1)| 00:00:02 |
|*  1 |  INDEX FAST FULL SCAN| IND_OB_NAME_AND_ID |    14 |  1106 |   136   (1)| 00:00:02 |
-------------------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"<5)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         55  recursive calls
          0  db block gets
        573  consistent gets
        475  physical reads
          0  redo size
        681  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          3  rows processed


当只有object_id作为索引时，这样和第一种情况差别不是很大
SQL> create index IDX_OBJECTS_ID idx_objects_id on t(object_id);

Index created.

SQL> select object_name,object_id from t where object_id < 5;

OBJECT_NAME                                                                                                                       OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
C_OBJ#                                                                                                                                    2
I_OBJ#                                                                                                                                    3
TAB$                                                                                                                                      4

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1638157718

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     3 |   237 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T              |     3 |   237 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_OBJECTS_ID |     3 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"<5)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         71  consistent gets
          0  physical reads
          0  redo size
        681  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

这是为什么？希望大家能给我普及下！谢谢！

乌鲁木齐-小小

其实第一种情况和第二种情况查询理论上应该比第三种要好，为什么会差那么一点点！

kobe24shou

第一个 走的是  INDEX RANGE SCAN ，第二个是 INDEX FAST FULL SCAN 第三个是 INDEX RANGE SCAN   但是有回表了。第一和第二没有回表，相对性能比有回表的更高效。

乌鲁木齐-小小

kobe24shou 发表于 2014-6-24 21:03
第一个 走的是  INDEX RANGE SCAN ，第二个是 INDEX FAST FULL SCAN 第三个是 INDEX RANGE SCAN   但是有回 ...

但为什么第二个是最没效率的，性能也最不好？

zachary

我的理解是，第二个你那种建立联合索引的方式，会导致直接先扫描object_name（INDEX FAST FULL SCAN）,在扫描其同时在查询object_id<5的条件的数据，所以性能会差。而第一种和第二种的object_id位前导列所以直接定位到object_id<5,就不在向下扫描了（索引是有序的），所以效率高。

北京郝非非

1.首先你的测试有可能不太准确，应该每次测试前保持环境一致，例如应该使用下面两条命令清一下内存，否则每次测试时环境不太一样，可能不准：
alter system flush share_pool;
alter system flush buffer_cache;
2.你的第一个测试，由于使用object_id,object_name的联合索引，并且你的SQL就检索的这两列，所以执行计划为

1. --------------------------------------------------------------------------------
   
2. | Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
   
3. --------------------------------------------------------------------------------
   
4. |   0 | SELECT STATEMENT |             |     3 |   237 |     2   (0)| 00:00:01 |
   
5. |*  1 |  INDEX RANGE SCAN| IND_NAME_ID |     3 |   237 |     2   (0)| 00:00:01 |
   
6. --------------------------------------------------------------------------------

复制代码

由于所需数据全部在索引里，就不需要再去扫描数据块，所以走的是索引范围扫描，直接返回所有满足条件的记录；
同理第三个由于只使用了object_id为索引列，所以走索引范围扫描后，还需要去扫描数据块，来获取object_name的值，所以执行计划里会多了一条：TABLE ACCESS BY INDEX ROWID
第二个测试可能理解起来复杂一些，我记得应该在哪个版本之前，如果你的查询条件不是索引的第一个列，虽然查询条件包括索引的列，但是都不会走索引，而走全表扫描（这个不是太确定啊，请供参考），后来oracle在后面版本进行了改善，只要包括索引的列，会根据实际情况来处理，这个例子是走了索引全扫描，也就是把索引全扫了一遍。

乌鲁木齐-小小

北京郝非非 发表于 2014-6-25 08:57
1.首先你的测试有可能不太准确，应该每次测试前保持环境一致，例如应该使用下面两条命令清一下内存，否则每 ...

谢谢，分析的很透彻，直接认识到了错误所在！

乌鲁木齐-小小

zachary 发表于 2014-6-25 00:18
我的理解是，第二个你那种建立联合索引的方式，会导致直接先扫描object_name（INDEX FAST FULL SCAN）,在扫 ...

嗯嗯，一下切中的要点！

kobe24shou

乌鲁木齐-小小 发表于 2014-6-24 22:07
但为什么第二个是最没效率的，性能也最不好？

联合索引（A,B）和（B，A） 然后where 又是过滤A，肯定会有不一样了。感觉联合索引是有序啊