understand Oracle exec_plan

A sql is slow, but some time is fast. 

We check the created sql text, and use Oracle exec_plan to cehck, find some time it uses index we expect, but some time it uses index we never expect.

The root cause is Oracle cannot decide which index is used, so Oracle will select one, or do full_table_scan.

For example

sql is "select * from SAE_PORTFOLIO_CR_BULK where BATCH_ID={1}. and BOOKING_CENTER={2} and VALOR={3}" ( this is hsql of hibernate)

IDX_PTFL_NBR_CR_BULK on SAE_PORTFOLIO_CR_BULK (BATCH_ID, PORTFOLIO_NBR, BOOKING_CENTER, VALOR)

But if VALOR is null as condition, hibernate will create a sql, it is not we expect

select * from SAE_PORTFOLIO_CR_BULK where BATCH_ID={1}. and BOOKING_CENTER={2} 

So Oracle cannot decide to use "IDX_PTFL_NBR_CR_BULK". In case SAE_PORTFOLIO_CR_BULK is big, the performance will be poor.

In case that, we should add oracle hint

for example

select /*+index(sae_portfolio_cr_bulk,IDX_PTFL_NBR_CR_BULK) index(sae_portfolio_cr_issuer,IDX_PTFL_NBR_CR_ISSUER)*/ sum(cr_eod_exposure_in_usd) from sae_portfolio_cr_bulk where portfolio_nbr in ('286661', '986661') and booking_center='HK'  and batch_id=10000 group by booking_center,valor