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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Processing
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
196 views