Performance Tuninig of a Select Query.
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
Software/Hardware used:
Software/Hardware used:
ASKED:
November 18, 2004 8:50 PM
UPDATED:
November 19, 2004 1:59 PM
Answer Wiki:
Have you tried doing an Explain Plan on your query?
To see all answers submitted to the Answer Wiki: View Answer History.
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.
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.
Have you tried the /*+ NO_EXPAND */ Oracle hint?
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
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