RE: Simple SQL Query
- From: "Michael McMullen" <ganstadba@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 7 Jan 2010 12:33:12 -0500
You can write a query with a join as a
Not in
Not exists
Outer join
The not exists has been discussed, the outer join would be
Select a.first.id
From
First a, second b
Where
a.first_id = b.second_id(+)
and
b.second_id is null
I always rewrite the queries the three different ways and explain plan all
three and look at the differences. The key is to have up to date stats and
either join on primary keys or indexed columns with not null constraints.
You want oracle to be scanning an index not doing a full table join.
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Pani Babu
Sent: Thursday, January 07, 2010 12:04 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Simple SQL Query
I have a simple SQL query which lists all the records that are not in a
second table.
select first.id from first
where first.id not in (select id from second);
As the second table is very huge, it takes a very long to process the query.
Is there a way to rewrite the query to run it quicker?
Other related posts:
