Re: NOT IN conditions are extremly SLOW

Robert Wehofer schrieb am 06.10.2005 in <aa48e$4344cece$d4badae4$12370_at_news.chello.at>:

> Hello!

> I would like to execute following statement

> SELECT count(*) FROM TABLE_A WHERE COL_A || '|' || COL_B NOT IN
> (SELECT COL_A || '|' || COL_B FROM TABLE_B);

> TABLE_A consists of 37.000 rows. The statement takes over 10 mins.
NOT IN - statements are always slow.

> I have set analyzed indexes to perform the statement but it doesn't help
> anyway.

You should have an index on TableA(ColA,ColB)

> Is it possible to modify the statement to get the same result with much
> better execution performance?

Try MINUS instead
SELECT
  COUNT(*)
FROM
  (SELECT
     COL_A,COL_B
   FROM
     TABLE_A
   MINUS
   SELECT
     COL_A,COL_B
   FROM
     TABLE_B
  )

Please tell me the effect(s)

> Regards,
> Robert

Gruß
Andreas

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de