关于复合索引中的2个索引列谁在前谁在后的进一步讨论--实践篇

关于复合索引中的2个索引列谁在前谁在后的进一步讨论--实践篇：

上一次在长老的QQ群里边说了这么一个例子：

create table test_pk( id varchar2(10), create_dt date);

alter table test_pk modify (id varchar2 (30 ));

insert into test_pk select object_id, sysdate from dba_objects;

commit

需要执行的查询是：

select * from test_pk where create_dt> sysdate- 1/1440 and id like '13%'

索引有如下两个：当然任意时刻，只存在下面的任意一个。

create index idx_test_pk on test_pk(create_dt, id);
create index idx_test_pk_id_create_dt on test_pk(id,create_dt);

 

也就是说：两个索引列的位置是反着的

首先要明确的一个问题：

where create_dt> sysdate- 1/1440 and id like '13%'
不论 create_dt 和id 谁在前谁在后，这样的where条件是能走索引的。

 

其次需要明确的问题：
create_dt 和id  这两个索引列，哪个在前面时，如上查询的效率高？（当然，前提是：不考虑其他查询！！！）

经过实际验证，发现id 在前面时，如上查询的buffer get要比 create_dt 在前面时的 buffer get 低。

 

create index idx_test_pk_id_create_dt on test_pk(id,create_dt); ---bg 是 6

 

 create index idx_test_pk on test_pk(create_dt,id); ---bg 是29.5

那么为啥id 在前面时的复合索引，查询语句的buffer get 要低呢？
原因不在于 id是什么数据类型的，
原因在于where条件中id的表现形式： id like '13%' ，相比而言，create_dt的表现形式是create_dt> sysdate- 1/1440

大家知道：
index是有序的，那么 id like '13%' 去索引的leaf block 检索时，检索过的leaf block的数量 一般是要比 create_dt> sysdate- 1/1440 （也就是create_dt在前面） 去索引的leaf block 检索过的leaf bock 的数量要少，正是这个原因，才导致id列在前面时的buffer get 要小。