Performance Tuninig of a Select Query.

pts.
Tags:
Development
Oracle
SQL
Dear All I am facing a performance tuning problem on the following query: select a.col1 as OID from table2 b,table1 a where a.col1 = b.col1 and b.col2 = 1568673860 and a.col2 in (select col1 from table3 where col2 in ('SUFFIX_CODE','CONFIG_SEQ','SOUCHI_KBN','GENKA_CODE','MFG_STATUS','SALES_NO','PRODUCT_TYPE','OWNER_NAME','UPDATE','OWNER_GROUP_NAME','BU_CODE','DEVELOPMENT_CODE','PRODUCT_NO')) and a.col3 like DECODE(a.col2,-922394134,'INN%'); table1 has 20 million records table2 has 1 million records table 3 has 2000 records The above query excutes in 2 secs. But if I replace b.col2 = 1568673860 with b.col2 in (1568673860,23423424,4324324234) in the above where expression, performance degrades drastically. It takes around 8 secs for execution. This is where I need improvement. The constraints in the tables are as follows: table 2 is having col1+col2+col3 as unique key table 1 col1 is a sort of foreign key to table2.col1 (though not explicit because both these tables are having foreign key constraint on col1 of table 4) col2 is a foreign key to col1 of table 3. Our target is to achieve the excution of the above query in 4 secs with as many as 10 values for b.col2. We are using Oracle 8i. We have also tried b.col2 = 1568673860 or b.col2 = 23423424 or ..... still the execution time is over 8 secs. We can go to PL/SQL too if need be. Any kind of help will be highly appreciated. Thanks and Regards, Tanmoy

Answer Wiki

Thanks. We'll let you know when a new response is added.

Have you tried doing an Explain Plan on your query?

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • EvanOraDBA
    You haven't specified the index (if any on table1). I would suggest this should probably be on col1, col2, col3. Do you have statistics? what optimizer mode are you using? I agree that a plan would help: try running in sqlplus with "set autotrace on" and include the output in this thread.
    0 pointsBadges:
    report
  • SergLNC
    Straightfoward rewriting select a.col1 as OID from table2 b,table1 a, table3 c where a.col1 = b.col1 and b.col2 = 1568673860 and a.col2 = c.col1 and c.col2 in ('SUFFIX_CODE','CONFIG_SEQ','SOUCHI_KBN','GENKA_CODE','MFG_STATUS','SALES_NO','PRODUCT_TYPE','OWNER_NAME','UPDATE','OWNER_GROUP_NAME','BU_CODE','DEVELOPMENT_CODE','PRODUCT_NO') AND a.col2= -922394134 and a.col3 like 'INN%'; may live more freedom for optimizer.
    0 pointsBadges:
    report
  • VickiSeely
    Have you tried the /*+ NO_EXPAND */ Oracle hint?
    0 pointsBadges:
    report
  • Pgurev
    Without looking at the explain plan and knowing all existing indexes, it is hard to do any recommendation, but here is something that you can try: select a.col1 as OID from table2 b,table1 a where a.col1 = b.col1 and b.col2 = 1568673860 and a.col2 EXISTS (select 'X' from table3 where a.col2 = col1 and col2 in ('SUFFIX_CODE','CONFIG_SEQ','SOUCHI_KBN','GENKA_CODE','MFG_STATUS','SALES_NO','PRODUCT_TYPE','OWNER_NAME','UPDATE','OWNER_GROUP_NAME','BU_CODE','DEVELOPMENT_CODE','PRODUCT_NO')) and a.col3 like DECODE(a.col2,-922394134,'INN%'); Hope this helps
    0 pointsBadges:
    report
  • Pgurev
    I am sending it again, there was an extra column on my original note: Without looking at the explain plan and knowing all existing indexes, it is hard to do any recommendation, but here is something that you can try: select a.col1 as OID from table2 b,table1 a where a.col1 = b.col1 and b.col2 = 1568673860 and EXISTS (select 'X' from table3 where b,col2 = clo1 and col2 in ('SUFFIX_CODE','CONFIG_SEQ','SOUCHI_KBN','GENKA_CODE','MFG_STATUS','SALES_NO','PRODUCT_TYPE','OWNER_NAME','UPDATE','OWNER_GROUP_NAME','BU_CODE','DEVELOPMENT_CODE','PRODUCT_NO')) and a.col3 like DECODE(a.col2,-922394134,'INN%'); Hope this helps
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following