Explain Plan returns only one index usage

[Hey_Ram]

Here is the scenario:
1. IEHP003_Chk_Result (EHP003_Check_Result_Log_K) - Primary Key - 500,000 rows
2. IEHP005_Violation (EHP003_Check_Result_Log_K) - Primary Key -
550,000 rows
3.Non Unique index available in EHP001_DEVICE_N, EHP003_Security_Condition_C,EHP004_Check_C
4.o_device_list,o_check_list,o_violtn_list,o_wrkfl w_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.EHP004_Check_C,
Count(Chk_Result.EHP003_Check_Result_Log_K) Cnt
From IEHP003_Chk_Result Chk_Result,
IEHP005_Violation Violtn,
Table (o_device_list) Dev,
Table (o_check_list) Chk,
Table (o_violtn_list) Vstat,
Table (o_wrkflw_list) Wstat
Where Chk_Result.EHP003_Check_Result_Log_K = Violtn.EHP003_Check_Result_Log_K
And Chk_Result.EHP004_Check_C = Chk.l_check_name
And Chk_Result.EHP001_Device_N = Dev.l_device_name
And Chk_Result.EHP003_Security_Condition_C = 'FAIL'
And Violtn.EHPL07_Violation_Status_C = Vstat.l_violation_status
And Violtn.EHPL06_Workflow_Status_C = Wstat.l_workflow_status
And Chk_Result.EHP003_Last_Run_S Between NVL(i_start_date,'01-JAN-0001')
And NVL(i_end_date,'31-DEC-9999')
Group By Chk_Result.EHP004_Check_C;

This query is always using index on IEHP003_Chk_Result and not on IEHP005_violation

[Medhatithi]

Here is the scenario:
1. IEHP003_Chk_Result (EHP003_Check_Result_Log_K) - Primary Key - 500,000 rows
2. IEHP005_Violation (EHP003_Check_Result_Log_K) - Primary Key -
550,000 rows
3.Non Unique index available in EHP001_DEVICE_N, EHP003_Security_Condition_C,EHP004_Check_C
4.o_device_list,o_check_list,o_violtn_list,o_wrkfl w_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.EHP004_Check_C,
Count(Chk_Result.EHP003_Check_Result_Log_K) Cnt
From IEHP003_Chk_Result Chk_Result,
IEHP005_Violation Violtn,
Table (o_device_list) Dev,
Table (o_check_list) Chk,
Table (o_violtn_list) Vstat,
Table (o_wrkflw_list) Wstat
Where Chk_Result.EHP003_Check_Result_Log_K = Violtn.EHP003_Check_Result_Log_K
And Chk_Result.EHP004_Check_C = Chk.l_check_name
And Chk_Result.EHP001_Device_N = Dev.l_device_name
And Chk_Result.EHP003_Security_Condition_C = 'FAIL'
And Violtn.EHPL07_Violation_Status_C = Vstat.l_violation_status
And Violtn.EHPL06_Workflow_Status_C = Wstat.l_workflow_status
And Chk_Result.EHP003_Last_Run_S Between NVL(i_start_date,'01-JAN-0001')
And NVL(i_end_date,'31-DEC-9999')
Group By Chk_Result.EHP004_Check_C;

This query is always using index on IEHP003_Chk_Result and not on IEHP005_violation

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