Re: Tune query with "NOT IN"
From: Ashish Mittal <mittalashish_at_yahoo.com>
Date: Fri, 12 Apr 2002 15:07:11 GMT
Message-ID: <zKCt8.13307$CH1.8791_at_sccrnsc02>
where b.f1 is not null
)
Date: Fri, 12 Apr 2002 15:07:11 GMT
Message-ID: <zKCt8.13307$CH1.8791_at_sccrnsc02>
select f1,f2,f5 from t1 where (f1,f2) not in (select f3,f4 from t2)
can be rewritten as
select f1,f2,f5 from
(
select distinct rwd,f1,f2,f5 from
(
select a.rowid rwd,b.f2 f1,b.f3 f2,a.f5
from t1,t2 where t1.f1=t2.f3(+) and t1.f2=t2.f4(+)
)
where b.f1 is not null
)
This may be faster for you.
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:a20d28ee.0204120639.4074eba1_at_posting.google.com...
> brotherhou_at_yahoo.com (konghou) wrote in message
news:<c19678a1.0204120116.66434be8_at_posting.google.com>...
> > How to tune the query:
> > select .... where (f1, f2) not in (select f3, f4 from ... )
> > A "not exist" seems working as slow.
>
> try using
> ... not in (select /*+ hash_aj(<tablename or alias>) f3, f4 ...)
>
> --
> Hth
>
> Sybrand Bakker
> Senior Oracle DBA
Received on Fri Apr 12 2002 - 17:07:11 CEST