Hi,
Can anybody let me know how I can optimise following Query.
Select * from reports
where
( exists ( SELECT 1 FROM results_require d rr, item_claims_tri als 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=CHOOS E (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)
__________NESTE D LOOPS (OUTER) (Cost=459 Card=755 Bytes=50585)
____________HAS H JOIN (OUTER) (Cost=35 Card=53 Bytes=2968)
______________H ASH JOIN (OUTER) (Cost=26 Card=53 Bytes=2226)
_______________ _HASH JOIN (OUTER) (Cost=17 Card=53 Bytes=1484)
_______________ ___TABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRE D (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_REQUIRE D (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_REQUIRE D (Cost=8 Card=47 Bytes=658)
_______________ ___INDEX (RANGE SCAN) OF RR_RT_FK_I (NON-UNIQUE) (Cost=1 Card=3)
______________T ABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRE D (Cost=8 Card=47 Bytes=658)
_______________ _INDEX (RANGE SCAN) OF RR_RT_FK_I (NON-UNIQUE) (Cost=1 Card=3)
____________TAB LE ACCESS (BY INDEX ROWID) OF RESULTS (Cost=8 Card=14 Bytes=154)
______________I NDEX (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)
____________IND EX (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.202496 2378238 Card=1 Bytes=22)
__________TABLE ACCESS (BY INDEX ROWID) OF ITEM_CLAIMS_TRI ALS (Cost=2 Card=9 Bytes=72)
____________IND EX (RANGE SCAN) OF ICT_T_FK_I (NON-UNIQUE) (Cost=1 Card=9)
__________TABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRE D (Cost=13.202496 2378238 Card=1 Bytes=14)
____________BIT MAP CONVERSION (TO ROWIDS)
______________B ITMAP 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.
=============== =============== ====
Can anybody let me know how I can optimise following Query.
Select * from reports
where
( exists ( SELECT 1 FROM results_require d rr, item_claims_tri als 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=CHOOS E (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)
__________NESTE D LOOPS (OUTER) (Cost=459 Card=755 Bytes=50585)
____________HAS H JOIN (OUTER) (Cost=35 Card=53 Bytes=2968)
______________H ASH JOIN (OUTER) (Cost=26 Card=53 Bytes=2226)
_______________ _HASH JOIN (OUTER) (Cost=17 Card=53 Bytes=1484)
_______________ ___TABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRE D (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_REQUIRE D (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_REQUIRE D (Cost=8 Card=47 Bytes=658)
_______________ ___INDEX (RANGE SCAN) OF RR_RT_FK_I (NON-UNIQUE) (Cost=1 Card=3)
______________T ABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRE D (Cost=8 Card=47 Bytes=658)
_______________ _INDEX (RANGE SCAN) OF RR_RT_FK_I (NON-UNIQUE) (Cost=1 Card=3)
____________TAB LE ACCESS (BY INDEX ROWID) OF RESULTS (Cost=8 Card=14 Bytes=154)
______________I NDEX (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)
____________IND EX (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.202496 2378238 Card=1 Bytes=22)
__________TABLE ACCESS (BY INDEX ROWID) OF ITEM_CLAIMS_TRI ALS (Cost=2 Card=9 Bytes=72)
____________IND EX (RANGE SCAN) OF ICT_T_FK_I (NON-UNIQUE) (Cost=1 Card=9)
__________TABLE ACCESS (BY INDEX ROWID) OF RESULTS_REQUIRE D (Cost=13.202496 2378238 Card=1 Bytes=14)
____________BIT MAP CONVERSION (TO ROWIDS)
______________B ITMAP 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.
=============== =============== ====
Comment