Oracle not using indexes if queried in wrong order

user_4097707 0
Viewed 127

I have tried to find similar issue, but didn't find it. If this is a duplicate, please point me to correct topic.

Oracle version 19 Standard Edition 2, version 19.3.

I have a table BOOKING. Some of the columns:

COLUMN_NAME DATA_TYPE NULLABLE
ID NUMBER(38,0) No
CLASSID NUMBER(5,0) Yes
PARENTFOREIGNKEY NUMBER(38,0) Yes
BOOKINGNO NUMBER(4,0) Yes

It has following indexes (that are important for this question):

INDEX_NAME UNIQUENESS STATUS INDEX_TYPE COLUMNS
BOOKING UNIQUE VALID NORMAL ID
BOOKING_511_2 NONUNIQUE VALID NORMAL PARENTFOREIGNKEY, CLASSID, ID

executing the query SELECT * FROM BOOKING WHERE (CLASSID=511) AND PARENTFOREIGNKEY=31647961 ORDER BY BOOKINGNO; takes around 40-45 seconds. whereas SELECT * FROM BOOKING WHERE PARENTFOREIGNKEY=31647961 AND (CLASSID=511) ORDER BY BOOKINGNO; takes only around 0.013 second.

It is obvious that first query is not using indexes, where the second on is. Explain plans are identical.

|   0 | SELECT STATEMENT                     |                           |     4 |   648 |     9  (12)| 00:00:01 |
|   1 |  SORT ORDER BY                       |                           |     4 |   648 |     9  (12)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BOOKING     |     4 |   648 |     8   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | BOOKING_511_2 |     4 |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("PARENTFOREIGNKEY"=31647961 AND "CLASSID"=511)

Can someone tell me what is going on? Why aren't both queries equivalent in the execution?

Thank you, GG

EDIT: Explain plans (SELECT * FROM TABLE(dbms_xplan.display_cursor(format => 'BASIC +PREDICATE'));):

EXPLAINED SQL STATEMENT:
------------------------
SELECT * FROM BOOKING WHERE (CLASSID=:"SYS_B_0") AND 
PARENTFOREIGNKEY=:"SYS_B_1" ORDER BY BOOKINGNO
 
Plan hash value: 1769702959
 
----------------------------------------------------
| Id  | Operation          | Name                  |
----------------------------------------------------
|   0 | SELECT STATEMENT   |                       |
|   1 |  SORT ORDER BY     |                       |
|*  2 |   TABLE ACCESS FULL| BOOKING               |
----------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("PARENTFOREIGNKEY"=:SYS_B_1 AND "CLASSID"=:SYS_B_0))

"Fast" query:

EXPLAINED SQL STATEMENT:
------------------------
SELECT * FROM BOOKING WHERE PARENTFOREIGNKEY=:"SYS_B_0" 
AND (CLASSID=:"SYS_B_1")  ORDER BY BOOKINGNO
 
Plan hash value: 3357352015
 
--------------------------------------------------------------------------
| Id  | Operation                            | Name                      |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |
|   1 |  SORT ORDER BY                       |                           |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BOOKING                   |
|*  3 |    INDEX RANGE SCAN                  | BOOKING_511_2             |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("PARENTFOREIGNKEY"=:SYS_B_0 AND "CLASSID"=:SYS_B_1)
indexing oracle
1 Answers

The order of columns in your search predicate needs to match the order of the columns in the index. From "How to Create and Use Indexes in Oracle Database":

Oracle Database reads an index starting with its leftmost (“first”) column. Then it goes onto the second, third, etc. So if you have an index on:

create index i on tab ( col1, col2, col3 );

And your where clause is:

where col3 = 'value'

To use the index the database either has to wade through all the values in col1 and col2. Or, more likely, read the whole thing to find those matching your conditions.

In your case, the problem is the third column in your index. If the index were only on the classid and parentforeignkey columns then the order of the predicates wouldn't make any difference. With the addition of the id column to the index and the change in order of the predicates, the optimizer determines that it is easier to just scan the entire table once than to use the index, which it would have to scan twice in its entirety. There are examples of this in the article which are to lengthy to duplicate here.

user_129134910
Related