Re: Why this query is SO slow?

I can think of a number of reasons. Here are the first batch.

1. You have large tables on a very slow machine.
2. You don't have the appropriate indexes on the id column in your tables.
3. You have partitioned tables and your indexes, if they exist, are global rather than local.
4. You haven't run statistics (what version do you have? ... either analyze schema or DBMS_STATS) so the optimizer isn't optimizing.
5. You could rewrite it using EXISTS as in a situation like this I would expect it to be substantially faster. Your query would look like this:

SELECT id
FROM pbase p
WHERE NOT EXISTS (
   SELECT id
   FROM pbase2 b
   WHERE p.id = b.id);

Amazingly enough this query often also runs far faster than the NOT IN as you wrote it:

SELECT DISTINCT id
FROM pbase
WHERE id NOT IN (

   SELECT id
   FROM pbase
   MINUS
   SELECT id
   FROM pbase2);

But no matter what else you do ... make sure indexes exist on both tables (I am assuming they are large) and that you create statistics and then run EXPLAIN PLAN to verify that the indexes are being used.

If you still have problems let us know.

Daniel Morgan

Max Wilson wrote:

> As subject:
>
> SELECT id
> FROM pbase
> WHERE id NOT IN
> (SELECT id FROM pbase2);
>
> It takes oven an hour to run.
>
> Thank you
> Manuel
>
> P.S. Sorry if my english not good
Received on Sun Apr 14 2002 - 18:50:54 CDT