Query using full table scan

[shilpasharma]

Hi,
Can anybody let me know how I can optimise following Query.

Select * from reports
where
( exists ( SELECT 1 FROM results_required rr, item_claims_trials ict, results res
WHERE ict.t_t_id IN ( 3725 )
and res.rr_rr_id = rr.rr_id
AND rr.rt_rt_id = -1
and rr.ict_ict_id = ict.ict_id
and ( NVL( res.res_res_id_refers_to, res.res_id ) = report_id )
)
);

Explain Plan for this is as below:

SELECT STATEMENT Optimizer=CHOOSE (Cost=4589774 Card=208129 Bytes=31635608)
__FILTER
____HASH JOIN (OUTER) (Cost=10936 Card=208129 Bytes=31635608)
______HASH JOIN (Cost=8079 Card=14631 Bytes=2062971)
________NESTED LOOPS (OUTER) (Cost=6499 Card=10734 Bytes=1116336)
__________NESTED LOOPS (OUTER) (Cost=459 Card=755 Bytes=50585)
____________HASH JOIN (OUTER) (Cost=35 Card=53 Bytes=2968)
______________HASH JOIN (OUTER) (Cost=26 Card=53 Bytes=2226)
________________HASH JOIN (OUTER) (Cost=17 Card=53 Bytes=1484)
__________________TABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRED (Cost=8 Card=53 Bytes=742)
____________________INDEX (RANGE SCAN) OF RR_RT_FK_I (NON-UNIQUE) (Cost=1 Card=53)
__________________TABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRED (Cost=8 Card=47 Bytes=658)
____________________INDEX (RANGE SCAN) OF RR_RT_FK_I (NON-UNIQUE) (Cost=1 Card=3)
________________TABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRED (Cost=8 Card=47 Bytes=658)
__________________INDEX (RANGE SCAN) OF RR_RT_FK_I (NON-UNIQUE) (Cost=1 Card=3)
______________TABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRED (Cost=8 Card=47 Bytes=658)
________________INDEX (RANGE SCAN) OF RR_RT_FK_I (NON-UNIQUE) (Cost=1 Card=3)
____________TABLE ACCESS (BY INDEX ROWID) OF RESULTS (Cost=8 Card=14 Bytes=154)
______________INDEX (RANGE SCAN) OF RES_RR_FK_I (NON-UNIQUE) (Cost=2 Card=14)
__________TABLE ACCESS (BY INDEX ROWID) OF RESULTS (Cost=8 Card=14 Bytes=518)
____________INDEX (RANGE SCAN) OF RES_RR_FK_I (NON-UNIQUE) (Cost=2 Card=14)
________TABLE ACCESS (FULL) OF RESULTS (Cost=1385 Card=72363 Bytes=2677431)
______TABLE ACCESS (FULL) OF RESULTS (Cost=1385 Card=1483628 Bytes=16319908)
____TABLE ACCESS (BY INDEX ROWID) OF RESULTS (Cost=8 Card=1 Bytes=13)
______NESTED LOOPS (Cost=22 Card=1 Bytes=35)
________NESTED LOOPS (Cost=13.2024962378238 Card=1 Bytes=22)
__________TABLE ACCESS (BY INDEX ROWID) OF ITEM_CLAIMS_TRIALS (Cost=2 Card=9 Bytes=72)
____________INDEX (RANGE SCAN) OF ICT_T_FK_I (NON-UNIQUE) (Cost=1 Card=9)
__________TABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRED (Cost=13.2024962378238 Card=1 Bytes=14)
____________BITMAP CONVERSION (TO ROWIDS)
______________BITMAP AND
________________BITMAP CONVERSION (FROM ROWIDS)
__________________INDEX (RANGE SCAN) OF RR_ICT_FK_I (NON-UNIQUE)
________________BITMAP CONVERSION (FROM ROWIDS)
__________________INDEX (RANGE SCAN) OF RR_RT_FK_I (NON-UNIQUE)
________INDEX (RANGE SCAN) OF RES_RR_FK_I (NON-UNIQUE) (Cost=2 Card=14)


==================================
Thanks.
==================================

[chandu031]

Hi,
Can anybody let me know how I can optimise following Query.

Select * from reports
where
( exists ( SELECT 1 FROM results_required rr, item_claims_trials ict, results res
WHERE ict.t_t_id IN ( 3725 )
and res.rr_rr_id = rr.rr_id
AND rr.rt_rt_id = -1
and rr.ict_ict_id = ict.ict_id
and ( NVL( res.res_res_id_refers_to, res.res_id ) = report_id )
)
);

Explain Plan for this is as below:

SELECT STATEMENT Optimizer=CHOOSE (Cost=4589774 Card=208129 Bytes=31635608)
__FILTER
____HASH JOIN (OUTER) (Cost=10936 Card=208129 Bytes=31635608)
______HASH JOIN (Cost=8079 Card=14631 Bytes=2062971)
________NESTED LOOPS (OUTER) (Cost=6499 Card=10734 Bytes=1116336)
__________NESTED LOOPS (OUTER) (Cost=459 Card=755 Bytes=50585)
____________HASH JOIN (OUTER) (Cost=35 Card=53 Bytes=2968)
______________HASH JOIN (OUTER) (Cost=26 Card=53 Bytes=2226)
________________HASH JOIN (OUTER) (Cost=17 Card=53 Bytes=1484)
__________________TABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRED (Cost=8 Card=53 Bytes=742)
____________________INDEX (RANGE SCAN) OF RR_RT_FK_I (NON-UNIQUE) (Cost=1 Card=53)
__________________TABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRED (Cost=8 Card=47 Bytes=658)
____________________INDEX (RANGE SCAN) OF RR_RT_FK_I (NON-UNIQUE) (Cost=1 Card=3)
________________TABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRED (Cost=8 Card=47 Bytes=658)
__________________INDEX (RANGE SCAN) OF RR_RT_FK_I (NON-UNIQUE) (Cost=1 Card=3)
______________TABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRED (Cost=8 Card=47 Bytes=658)
________________INDEX (RANGE SCAN) OF RR_RT_FK_I (NON-UNIQUE) (Cost=1 Card=3)
____________TABLE ACCESS (BY INDEX ROWID) OF RESULTS (Cost=8 Card=14 Bytes=154)
______________INDEX (RANGE SCAN) OF RES_RR_FK_I (NON-UNIQUE) (Cost=2 Card=14)
__________TABLE ACCESS (BY INDEX ROWID) OF RESULTS (Cost=8 Card=14 Bytes=518)
____________INDEX (RANGE SCAN) OF RES_RR_FK_I (NON-UNIQUE) (Cost=2 Card=14)
________TABLE ACCESS (FULL) OF RESULTS (Cost=1385 Card=72363 Bytes=2677431)
______TABLE ACCESS (FULL) OF RESULTS (Cost=1385 Card=1483628 Bytes=16319908)
____TABLE ACCESS (BY INDEX ROWID) OF RESULTS (Cost=8 Card=1 Bytes=13)
______NESTED LOOPS (Cost=22 Card=1 Bytes=35)
________NESTED LOOPS (Cost=13.2024962378238 Card=1 Bytes=22)
__________TABLE ACCESS (BY INDEX ROWID) OF ITEM_CLAIMS_TRIALS (Cost=2 Card=9 Bytes=72)
____________INDEX (RANGE SCAN) OF ICT_T_FK_I (NON-UNIQUE) (Cost=1 Card=9)
__________TABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRED (Cost=13.2024962378238 Card=1 Bytes=14)
____________BITMAP CONVERSION (TO ROWIDS)
______________BITMAP AND
________________BITMAP CONVERSION (FROM ROWIDS)
__________________INDEX (RANGE SCAN) OF RR_ICT_FK_I (NON-UNIQUE)
________________BITMAP CONVERSION (FROM ROWIDS)
__________________INDEX (RANGE SCAN) OF RR_RT_FK_I (NON-UNIQUE)
________INDEX (RANGE SCAN) OF RES_RR_FK_I (NON-UNIQUE) (Cost=2 Card=14)


==================================
Thanks.
==================================

Hi,

Have you collected the statistics on this table?