Skip to Main Content
  • Questions
  • Oracle SQL Index Optimization : Index Hint not being used even on forcing

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Amit.

Asked: August 12, 2019 - 11:39 am UTC

Last updated: August 22, 2019 - 4:42 pm UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

Dear Sir,

I have created a table with a composite primary key and few indexes. In one of the SQL the where clause is a combination of Index Column and non-index columns, however the query is not using the index column and instead going for Full Table Scan.

Since, it didn't use the index column I forced the query to use the index hint however still not using the index. I am getting the same result in the live session as well. I'm expecting below condition should go for the index scan
sh.vin = 'MA3FJEB1S00986676'
even though it's surrounded by OR condition.

Please enlighten.

Live SQL Link: https://livesql.oracle.com/apex/livesql/s/isz9vnmva9ugfw6qz4ua3yfkk

Query is as below:

SELECT /* +index (sh IDX_VIN_POCINV) */ sh.inv_date, 
                    sh.sales_type,   
                    sh.invoice_amt, 
                    'TV' channel,
                    sh.fin_amt,
                    DECODE(sh.exchange_flag,'Y','Y','N') exh_flag, 
                    sh.exch_reg_num,
                    sh.tenure,
                    sh.fi_cd,
                    sh.parent_group,
                    sh.dealer_map_cd, loc_CD
               FROM am_temp sh
              WHERE 
              ((sh.vin = 'MA3FJEB1S00986676') OR
                     (sh.model_cd = 'DM' AND
                       substr(sh.chassis_num,-6) = substr('986676',-6) AND
                       sh.engine_num = '2887232') OR
                     (substr(sh.chassis_num,-6) = substr('986676',-6) AND
                       sh.reg_num = 'KL52K4856')
                     )
                AND sh.inv_status = 'I'  
                AND sh.inv_date BETWEEN '01-JAN-2010' AND '01-JAN-2019';


with LiveSQL Test Case:

and Chris said...

I'm expecting below condition should go for the index scan even though it's surrounded by OR condition.

In general the optimizer struggles to use indexes when you have OR conditions. Especially so when an index column is only on one side of the condition.

Why you ask?

Because the database needs to evaluate the whole expression!

Sure, you could use the index to see if there are any entries where vin = 'MA3FJEB1S00986676'.

But for every row where this is false, you still need to check all the other predicates. None of these check VIN. So the index is useless for these other criteria.

So it goes for a full table scan.

In some cases the optimizer can combine many indexes in a BITMAP OR. But you've got no indexes on the other columns in your query. Ruling this option out.

If the full scan is taking too long, you may get some mileage out of:

- Creating indexes on the other columns in the OR condition
- Unioning the query using VIN with the other OR

Giving something like:

create index ii 
  on am_temp ( substr(chassis_num,-6) , reg_num  );
create index iii 
  on am_temp ( substr(chassis_num,-6) , model_cd, engine_num  );
  
SELECT /* index (sh IDX_VIN_POCINV) */ sh.inv_date,  
    sh.sales_type,    
    sh.invoice_amt,  
    'TV' channel, 
    sh.fin_amt, 
    DECODE(sh.exchange_flag,'Y','Y','N') exh_flag,  
    sh.exch_reg_num, 
    sh.tenure, 
    sh.fi_cd, 
    sh.parent_group, 
    sh.dealer_map_cd, loc_CD 
FROM am_temp sh 
WHERE  sh.vin = 'MA3FJEB1S00986676'
AND sh.inv_status = 'I'   
AND sh.inv_date BETWEEN '01-JAN-2010' AND '01-JAN-2019'
union 
SELECT /* index (sh IDX_VIN_POCINV) */ sh.inv_date,  
    sh.sales_type,    
    sh.invoice_amt,  
    'TV' channel, 
    sh.fin_amt, 
    DECODE(sh.exchange_flag,'Y','Y','N') exh_flag,  
    sh.exch_reg_num, 
    sh.tenure, 
    sh.fi_cd, 
    sh.parent_group, 
    sh.dealer_map_cd, loc_CD 
FROM am_temp sh 
WHERE  
(
     (sh.model_cd = 'DM' AND 
       substr(sh.chassis_num,-6) = substr('986676',-6) AND 
       sh.engine_num = '2887232') OR 
     (substr(sh.chassis_num,-6) = substr('986676',-6) AND 
       sh.reg_num = 'KL52K4856') 
     ) 
AND sh.inv_status = 'I'   
AND sh.inv_date BETWEEN '01-JAN-2010' AND '01-JAN-2019';

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

Plan hash value: 2119838223                                                 
                                                                            
-----------------------------------------------------------------           
| Id  | Operation                              | Name           |           
-----------------------------------------------------------------           
|   0 | SELECT STATEMENT                       |                |           
|   1 |  SORT UNIQUE                           |                |           
|   2 |   UNION-ALL                            |                |           
|   3 |    FILTER                              |                |           
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| AM_TEMP        |           
|   5 |      INDEX RANGE SCAN                  | IDX_VIN_POCINV |           
|   6 |    FILTER                              |                |           
|   7 |     TABLE ACCESS BY INDEX ROWID BATCHED| AM_TEMP        |           
|   8 |      BITMAP CONVERSION TO ROWIDS       |                |           
|   9 |       BITMAP OR                        |                |           
|  10 |        BITMAP CONVERSION FROM ROWIDS   |                |           
|  11 |         INDEX RANGE SCAN               | II             |           
|  12 |        BITMAP CONVERSION FROM ROWIDS   |                |           
|  13 |         INDEX RANGE SCAN               | III            |           
-----------------------------------------------------------------

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Watch out for implicit conversions

Tubby, August 13, 2019 - 9:32 pm UTC

  AND sh.inv_date BETWEEN '01-JAN-2010' AND '01-JAN-2019';


The INV_DATE is declared as a date on the table so you'd want to make sure you are comparing dates to dates and not strings.

                AND sh.inv_date BETWEEN to_date('01-JAN-2010', 'dd-mon-yyyy') AND to_date('01-JAN-2019', 'dd-mon-yyyy');


I see that's not currently your problem, but if you'd like to keep it that way make sure you're always using the correct data types :)

Cheers,
Connor McDonald
August 14, 2019 - 2:27 am UTC

Nice catch

Document Needed

Amit Chaudhary, August 14, 2019 - 9:04 am UTC

Thank You Sir for your explanation.
Everytime I came across query tuning, I discover something new. Is there any link/document/book that can help me understand entire Index, Optimizer & Hint concepts and also some insight on Query tuning.
Chris Saxon
August 14, 2019 - 10:25 am UTC

Query tuning is a huge topic! Here's some links to get you started:

My introduction to indexing video series: https://www.youtube.com/watch?v=f3U9F_wbo1I&list=PL78V83xV2fYlLA-bjMU2ZvUKQOZNrqLEa

Connor's Higher-Level Perspective on SQL Tuning Oracle Magazine series, which starts at https://blogs.oracle.com/oraclemagazine/a-higher-level-perspective-on-sql-tuning

The database performance tuning guide in the docs: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/index.html

In terms of books, I'd recommend Cary Millsap's "Optimizing Oracle Performance", Christian Antognini's "Troubleshooting Oracle Performance", and, as you get more advanced, Jonathan Lewis' "Cost-Based Oracle Fundamentals".

Should hint be /*+ , not /* + ?

lh, August 14, 2019 - 12:46 pm UTC

Hi

Is there an extra space in hint ?
Just noticing. Doubt that would help.


lh
Connor McDonald
August 15, 2019 - 2:07 am UTC

Well spotted!

A reader, August 20, 2019 - 2:06 am UTC

I know this doesn't matter to much but substr('986676',-6) can be simplify as below...

substr(sh.chassis_num,-6) = '986676'
Chris Saxon
August 20, 2019 - 9:45 am UTC

Indeed!

David, August 21, 2019 - 11:52 am UTC


There is a very good book focused on indexes : "SQL Performance Explained Everything Developers Need to Know about SQL Performance " by Markus Winand.

I read it in french, there is an american version of the book and it is a very good one.


Chris Saxon
August 22, 2019 - 4:42 pm UTC

Yep, that's a great source too.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.