如题。

比如索引是基于col_a,col_b建立。如果where col_a=xx and col_b=yy, 自然会用到索引。但据说，如果where col_b=yy and col_a=xx就不会使用索引。我想，ORACLE的优化器就这么呆板么。通过试验，发现好像不是那么回事。请专家指点一二：

create table tbtbtb (id1 integer,id2 integer);

insert into tbtbtb select level,mod(level,100) from dual connect by level<10001;

commit;

create index idx_tbtbtb on tbtbtb(id1,id2);

Index created.

SQL> set autotrace traceonly;

按照索引次序加条件
SQL> select * from tbtbtb where id1=4567 and id2=19;

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 515734133

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

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

1 - access("ID1"=4567 AND "ID2"=19)


不按索引次序加条件，似乎也用到了索引
SQL> select * from tbtbtb where id2=19 and id1=4567;

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 515734133

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

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

1 - access("ID1"=4567 AND "ID2"=19)