Advice please "NOT IN" query takes ages! 3

[Welshbird]

I have two tables that I load just in order to find which records appear in one and not in the other (its purely a validation process).

I thought I was making it easier but concatenating the three columns (in each table) into one called "KEY", indexing the KEY field in each, and then running a basic

select * from BIGTABLE where key not in
(select key from SMALLERTABLE)

Each table has about 2.3 million records, but it still seems to take f'rages.

I do recall someone telling me that a 'not in' is not very efficient, so which is the best way for me to deal with this then?

Thanks chaps.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Dagon]

Do you have an index of any sort on key in smallertable ?

 

[Welshbird]

KEY is indexed in both tables.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

After 1 hour and 12 minutes (and still no result) I tried the following instead:

select master.* from master
left join teams on (master.key = teams.key)
where teams.key is null;

And I have results almost instantly.

Can someone explain to my why this happens?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Dagon]

The best thing would be to get an explain plan for both queries. The most likely scenario is that Oracle is doing the first one using nested loops full table scans i.e for each row in master it is doing a full table scan of teams.

The only drawback of a join rather than "not in" or "not exists" is that there could be duplicates in the teams table, which would result in duplicates in the output. An alternative would be to use "not exists":

select * from master m
where not exists
(select 1 from teams t
where t.key = m.key)

You should also make sure that stats are up to date on both tables.

 

[Welshbird]

Thanks for that Dagon - that ran just as quickly as the one I tried, and gave the same number of rows.

In this case I know from the data source that there cannot be duplicates, but its' good to know how I would deal with them in another situation.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[johnherman]

My DBA has told me repeatedly that the ORACLE MINUS command is very efficient.

SELECT * FROM bigtable WHERE key IN
(SELECT key FROM bigtable MINUS SELECT key FROM smallertable)


-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Dagon]

I agree:

SELECT key FROM bigtable MINUS SELECT key FROM smallertable

is usually quite efficient. I am less convinced that putting into a subquery would be a good idea.

 

[carp]

Generally speaking, the MINUS construct would be more efficient, particularly for large data sets.

Regarding the original query, you may get better results with

select * from BIGTABLE where key NOT EXISTS
(select key from SMALLERTABLE);

but the left outer join or set operation will still be more likely to give you the performance you seek.

 

[Welshbird]

Well I've never heard of a MINUS thing before. Ah the joys of a three days in Oracle once... and that was it.

Thanks guys.

's all round.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SantaMufasa]

Dave (Carp), I believe the NOT EXISTS code would have to syntactically read something like this to work:

select * from BIGTABLE x where NOT EXISTS
(select 'anything' from SMALLERTABLE y
  where x.key = y.key);

Let me know if I'm off base.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[carp]

YOU - off base? Yeah, THAT'LL be the day!
Nope - I was just doing a quick drive by and hosed up.
Thanks for the correction.

 

[Beilstwh]

LOL, the great and powerful wizard of oracle being wrong.... HA, not very darn often!!!!

Bill
Oracle DBA/Developer
New York State, USA