query performance tuning
Hi,
Need your help tuning the following select.
INSERT INTO samim_appo_accrual_cur
SELECT a.*, 2
FROM samim_appo_accrual_all a
WHERE a.trans_year IN (2013 - 1, 2013)
AND EXISTS
(SELECT b.derived_rcv_trasanction_id
FROM samim_appo_accrual_cur b
WHERE b.code_combination_id = 1158
AND a.derived_rcv_trasanction_id =
b.derived_rcv_trasanction_id)
Explain plan of the above select is as follows.
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Hint=ALL_ROWS 1 871
COUNT STOPKEY
NESTED LOOPS SEMI 1 873 871
TABLE ACCESS BY INDEX ROWID samim_APPO_ACCRUAL_ALL 1 862 2
INDEX RANGE SCAN samim_APPO_ACCRUAL_ALL_IDX 1 2
TABLE ACCESS BY INDEX ROWID samim_APPO_ACCRUAL_CUR 1 11 869
INDEX RANGE SCAN samim_APPO_ACCRUAL_CUR_IDX 18 K 61
Indexes created on trans_year and code_combination_id columns. Query is taking around 6 hrs to complet.
Regards/Prasanth