Re: Why this query is SO slow?

Be sure you have indexes on ID for both tables and ANALYZE the tables, otherwise CBO will be unable to choose appropriate access path. If your inlist is big enough, you may want to use hash or merge anti join:

select id from pbase1 where id not in
 (select /*+HASH_AJ or MERGE_AJ*/ select id from pbase2)

also try NOT EXISTS and MINUS to see if they will get you better response time. Try to add FIRST_ROWS hint to the query if you need first rows as fast as possible. Generate plans for all variants of your query (analyze tables first) and see which of them looks most optimal in your situation. Also, NOT IN returns FALSE if inlist contains NULLs and you'll receive no rows from your query if inlist subquery returns at least one NULL, while NOT EXISTS will allow you to get correct result.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Max Wilson" <me_at_home.com> wrote in message news:Awnu8.162$Tt6.22928_at_news-binary.blueyonder.co.uk...


> 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

>

>