Hello All,
I've a query which, while executing, is taking a quite a lot of time. Can someone help me out to optimize it.

select column1, column2
from table1
where <some static condition>
and column3 not in ( select coulmn3
from table1
where <some static condition>
)

It is a join of table1 to itself. 'table1' is not having any index as the records are being populated from a JMS queue, which can be out of sync and duplicate too.
If I use 'not in' clause for the sub-query, it gives me correct result according to the static conditions, but takes a lot of time when the size of the table grows. Even if I fire the query from isql and not from my Java class, it takes a lot of time.
If I use 'not exists' clause, it somehow misses some records almost all the time but the answer is quick. The static conditions are noting but date comparisions.
Can someone tell me how I change my query for optimization so that I can speed up without loosing data?

Thanx in Advance.
Mahesh.


---------------------------------
Want to chat instantly with your online friends? Get the FREE Yahoo!Messenger

[Non-text portions of this message have been removed]