Avoiding the use of NOT IN ! Anyway out
-
Hi all,
I am trying to avoid the use of (not in) clause simply because the query optimizer doesnt use an index, instead it has to use a table scan.
Select * from personproFile where personurn not in (591, 595)
Does anyone have a solution to this problem ?
Thanks in advance
-
How many rows do you have in the table?
How many different ids do you have?
How much data do you expect this query to return?
-
This doesn't look like a production query as you have hard coded the numbers so speed shouldn't be an issue. A solution would be ... where personnum 595 or personnum between 592 and 594 ... but i doubt that it would help performance ! In general, you need to look at the whole scenario and the overall strategy. Using 'not in' is not good in principle but sometimes you're stuck with it.
-
John,
If the optimizer thinks that it is going to return a significant portion of a table, it may (correctly) decide that it would be faster to scan the table than to use the index.
hth jg
-
You could try:
where (personurn <= 590) or (personurn between 592 and 594) or (personurn >= 596)
but I don't know if it would be much more efficient
-
Still forces a scan...
As I said to him in PV. Sometimes you're stuck with it
!.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply