| Alternative to NOT IN [message #595777] |
Fri, 13 September 2013 09:03  |
 |
developer12
Messages: 88
Registered: July 2013
|
Member |
|
|
Hi,
Can anyone suggest an alternative to NOT IN Oracle?
I have Table A which has 12 million records and I want to match them with records in Table B by joining on a column- ID.
If the record in Table B is missing, I want to report the column ID from Table A for which there is no record in Table B.
Now, a NOT IN clause is consuming huge overhead for the query to run, so is there any other alternatives to that??
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
 Re: Alternative to NOT IN [message #596113 is a reply to message #596112] |
Wed, 18 September 2013 02:13  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
But MINUS cannot be (efficiently) used when you want columns in one table that are not in the other one.
For instance, with the following tables:
create table tablea (id int, val int);
create table tableb (id int);
I want rows from tablea with id not in tableb.
MINUS can only return you ID, and VAL if you use it as a subquery:
select * from tablea
where id in (select id from tablea minus select id from tableb);
There OUTER JOIN is a direct alternative to NOT IN:
select a.*
from tablea a, tableb b
where a.id = b.id(+)
and b.id is null;
[Edit: english]
[Updated on: Wed, 18 September 2013 05:08] Report message to a moderator |
|
|
|
| Re: Alternative to NOT IN [message #596120 is a reply to message #596112] |
Wed, 18 September 2013 02:55 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
msol25 wrote on Wed, 18 September 2013 08:05Hi friends,
Only use of MINUS can be the best solution
Please define what you mean by 'best'
|
|
|
|
| Re: Alternative to NOT IN [message #596225 is a reply to message #596120] |
Thu, 19 September 2013 03:04 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
As Michel points out, MINUS has its problems if you want some other values.
Similarly, outer joins may have performance problems if there are typically many TABLEB rows for one TABLEA row, because you bear the cost of joining all rows before filtering.
NOT IN can be made to run fast. Typically when NOT IN is slow, it is because the optimizer cannot rewrite it as an ANTI-JOIN. This is caused by comparison columns in the outer query or the sub-query being nullable. If you make them non-nullable, ANTI-JOIN comes into play and you get same or better performance as the outer join.
SELECT *
FROM TABLEA
WHERE ID IS NOT NULL
AND ID NOT IN (
SELECT ID
FROM TABLEB
WHERE ID IS NOT NULL)
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
