Explain Plan returns only one index usage

**Medhatithi** · Jun 8 '07, 01:34 PM

Originally posted by Hey_Ram

Here is the scenario:
1. IEHP003_Chk_Res ult (EHP003_Check_R esult_Log_K) - Primary Key - 500,000 rows
2. IEHP005_Violati on (EHP003_Check_R esult_Log_K) - Primary Key -
550,000 rows
3.Non Unique index available in EHP001_DEVICE_N , EHP003_Security _Condition_C,EH P004_Check_C
4.o_device_list ,o_check_list,o _violtn_list,o_ wrkflw_list are Table Type with list of Values( More than 1000 values so i cant use it in 'IN' Parameter) from Java Screen List box.

Open o_check_cnt For
Select Chk_Result.EHP0 04_Check_C,
Count(Chk_Resul t.EHP003_Check_ Result_Log_K) Cnt
From IEHP003_Chk_Res ult Chk_Result,
IEHP005_Violati on Violtn,
Table (o_device_list) Dev,
Table (o_check_list) Chk,
Table (o_violtn_list) Vstat,
Table (o_wrkflw_list) Wstat
Where Chk_Result.EHP0 03_Check_Result _Log_K = Violtn.EHP003_C heck_Result_Log _K
And Chk_Result.EHP0 04_Check_C = Chk.l_check_nam e
And Chk_Result.EHP0 01_Device_N = Dev.l_device_na me
And Chk_Result.EHP0 03_Security_Con dition_C = 'FAIL'
And Violtn.EHPL07_V iolation_Status _C = Vstat.l_violati on_status
And Violtn.EHPL06_W orkflow_Status_ C = Wstat.l_workflo w_status
And Chk_Result.EHP0 03_Last_Run_S Between NVL(i_start_dat e,'01-JAN-0001')
And NVL(i_end_date, '31-DEC-9999')
Group By Chk_Result.EHP0 04_Check_C;

This query is always using index on IEHP003_Chk_Res ult and not on IEHP005_violati on

This may be possible due to using the nested table in the join. You can use the CAST and then the DYNAMIC_SAMPLIN G hint to override this behaviour

Explain Plan returns only one index usage

Explain Plan returns only one index usage

Comment