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,