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