Further discussion on who is in front of the two index columns in the composite index -- Practice, index --

Further discussion on who is in front of the two index columns in the composite index -- Practice, index --

Further discussion on who is in front of the two index columns in the composite index -- Practice:

The last time I talked about this example in the elder's QQ group:

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

The query to be executed is:

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

There are two indexes: Of course, there is only one of the following at any time.

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);

 

That is to say, the positions of the two index columns are reversed.

First, we need to clarify the following question:

Where create_dt> sysdate-1/1440 and id like '201312'
Such a where condition can be indexed regardless of the person whose create_dt and id are in the first place.

 

Next, we need to clarify the following issues:
When the two index columns create_dt and id are in front of each other, the above query is highly efficient? (Of course, the premise is: do not consider other queries !!!)

After actual verification, it is found that the buffer get in the preceding query is lower than the buffer get in front of create_dt.

 

Create index idx_test_pk_id_create_dt on test_pk (id, create_dt); --- bg is 6

 

Create index idx_test_pk on test_pk (create_dt, id); --- bg is 29.5

So why is the buffer get of the query statement low for the compound index of the id at the beginning?
The reason is not the id data type,
The reason is that the id representation in the where condition: id like '000000'. In comparison, the create_dt representation is create_dt> sysdate-13%.

Everyone knows:
The index is ordered, so when the id like '000000' is retrieved from the leaf block of the index, the number of leaf blocks retrieved is generally less than the number of leaf bock retrieved by the leaf block indexed by create_dt> sysdate-1/1440 (that is, before create_dt, this is the reason for the small buffer get value when the id column is in front.