Re: Simple SQL Query
- From: Adam Musch <ahmusch@xxxxxxxxx>
- To: panibabu.mail@xxxxxxxxx
- Date: Fri, 8 Jan 2010 09:46:42 -0600
Without knowing the cardinality of each table and whether the columns
being referenced are indexed and/or not null, tuning it remotely isn't
really possible -- all the internet can give you is options.
My assumptions are that both first.id and second.id are non-null. If
that's not true, then some of these queries can produce incorrect
results.
The three basic ones:
1. Left outer join, which would probably prefer a hash outer join.
As you've written it is fine, as it's relationally equal to:
select first.id from first left join second on first.id = second.id
where secord.id is null;
2. Correlated subquery, which will almost certainly be a nested loops
operation. It could be fastest if first is "small" and second.id is
indexed.
select first.id from first where not exists (select 'x' from second
where second.id = first.id);
3. Minus, which may perform a merge-sort:
select first.id from first minus select second.id from second;
What are the explain plans for each of those queries in your system?
On Thu, Jan 7, 2010 at 11:03 AM, Pani Babu <panibabu.mail@xxxxxxxxx> wrote:
> 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?
>
--
Adam Musch
ahmusch@xxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
