ORACLE 复合索引在什么情况下会被用到

在论坛里看到有人问，有三个列的复合索引，查询条件中只包含两个列是不是就不会走索引了？

 

经过试验，我发现两点：

 1. 只要某查询条件中包含复合索引中的第一个列，该查询就会走索引，如果不包含，怎么样都不会走索引。

什么意思呢？比如说我创建个索引：

Create index idx1_test on test(c1,c2,c3)；

当切仅当查询条件中包含c1时，此查询就会走idx1_test索引，否则无论如何都不会走索引。

 

SQL> select * from test where c1 = '1' and c2 = '2';

 

----------------------------------------------------------------------------------------

| Id  |Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|Time     |

----------------------------------------------------------------------------------------

|   0 | SELECTSTATEMENT           |               |    1 |    48 |    0         (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |    1 |    48 |    0         (0)| 00:00:01 |

|*  2 |   INDEX RANGESCAN            | IDX_TEST |     1 |      |    0         (0)| 00:00:01 |

----------------------------------------------------------------------------------------

 

SQL> select * from test where c1 = '1' and c3 = '3';

 

----------------------------------------------------------------------------------------

| Id  |Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|Time     |

----------------------------------------------------------------------------------------

|   0 | SELECTSTATEMENT           |               |    1 |    48 |    2         (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |    1 |    48 |    2         (0)| 00:00:01 |

|*  2 |   INDEX RANGESCAN            | IDX_TEST |     1 |      |    1         (0)| 00:00:01 |

 

SQL> select * from test where c3 = '3' and c2 = '2';

 

--------------------------------------------------------------------------

| Id  |Operation          | Name | Rows | Bytes | Cost (%CPU)| Time        |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT |         |     1 |   48 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TEST |     1 |   48 |     3   (0)| 00:00:01 |

 2. 第二点，是否走索引与查询中的条件排列顺序是无关的。无论第一列在条件中排在什么位置，只要有他，就会走索引。

 

SQL> select * from test where c3 = '3' and c1 = '1';

 

----------------------------------------------------------------------------------------

| Id  |Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|Time     |

----------------------------------------------------------------------------------------

|   0 | SELECTSTATEMENT           |               |    1 |    48 |    2         (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |    1 |    48 |    2         (0)| 00:00:01 |

|*  2 |   INDEX RANGESCAN            | IDX_TEST |     1 |      |    1         (0)| 00:00:01 |

----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 -access("C1"='1' AND "C3"='3')

      filter("C3"='3')

 

 

SQL> select * from test where c1 = '1' and c3 = '3';

 

----------------------------------------------------------------------------------------

| Id  |Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|Time     |

----------------------------------------------------------------------------------------

|   0 | SELECTSTATEMENT           |               |    1 |    48 |    2         (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |    1 |    48 |    2         (0)| 00:00:01 |

|*  2 |   INDEX RANGESCAN            | IDX_TEST |     1 |      |    1         (0)| 00:00:01 |

----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 -access("C1"='1' AND "C3"='3')                         我们可以看到，对优化器来说，这两种查询对它来说是没有区别的。

      filter("C3"='3')