Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

query performance tuning

user11989619Apr 10 2013 — edited Apr 10 2013
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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 8 2013
Added on Apr 10 2013
#access, #general-database-discussions, #index, #performance, #query, #sql, #table, #tuning
9 comments
201 views