RE: Simple SQL Query

From: Michael McMullen <ganstadba_at_hotmail.com>
Date: Thu, 7 Jan 2010 12:33:12 -0500
Message-ID: <SNT130-ds40D154E7842C3E8276EE4A6710_at_phx.gbl>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Pani Babu
Sent: Thursday, January 07, 2010 12:04 PM To: oracle-l_at_freelists.org
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?

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 07 2010 - 11:33:12 CST