Can somebody please help me enhance this sql statement to run faster.
Code: ( oracle8 )
ok, first it would help us a lot if you could provide the create table statements for us, it makes it easier for us to help you.
second as a general rule of thumb you want to avoid using IN and NOT IN unless the lists are very small or specific strings which you hard code in and DONT CHANGE. unless the IN list is some static strings you usually can replace an IN with a JOIN and similarly a NOT IN with an ANTI-JOIN.
And you definately want to avoid nested IN statements like the plague. Do some reading on how oracle must process an IN list and you will understand why large IN lists are a performance killer.
so while i dont have the tables and thus cannot test this out the first part of your union query would become something like this:
-
SELECT ser_id,
-
dy_id
-
FROM sequence ser,
-
dy,
-
(SELECT dy_id
-
FROM lookuptab
-
GROUP BY dy_id
-
HAVING count(*) > 1) lut
-
WHERE lut.dy_id = dy.dy_id
-
AND ser.dsy_id = dy.dy_rid
-
other than what i have written i would really need table and index create statements to see what the query is doing.
there are so many factors involved in tuning there literally are whole volumes books written on the subject. things like size of tables, poorly written SQL, are indexes being used by the RBO or hints needed. lots of stuff.