Re: Improve query performance

From: VC <boston103_at_hotmail.com>
Date: Wed, 10 Dec 2003 01:46:56 GMT
Message-ID: <kWuBb.349711$ao4.1171224@attbi_s51>

Hello Anurag,

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:DmuBb.5933$qe6.5310_at_news02.roc.ny...
> A "not in" query might perform differently with RBO.
> With CBO and all tables analyzed, "NOT IN" is executed the same exact way
as "NOT EXISTS".
> I've tried it a lot of times and find this to be true 8i onwards.
>
> In fact Harrison notes this fact in this SQL Tuning book.
> Do you have a working example to prove this the other way?

drop table t1;
drop table t2;
create table t1 as select * from all_objects; create index t1_idx on t1(object_id);
create table t2 as select * from all_objects where rownum <= 500; analyze table t1 compute statistics;
analyze table t1 compute statistics;
set autot trace explain

select * from t2 where object_id not in (select object_id from t1);

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=132)    1 0 HASH JOIN (ANTI) (Cost=12 Card=1 Bytes=132)

   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=899 Bytes=115072)
   3    1     INDEX (FAST FULL SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=7

Card=25324 Bytes=101296)

select * from t2 where not exists (select 1 from t1 where object_id=t2.object_id);

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=45 Bytes=5760)

   1 0 FILTER

   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=45 Bytes=5760)
   3    1     INDEX (RANGE SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=1 Card =1

Bytes=4)

Rgds.

>
> Anurag
>
>
Received on Tue Dec 09 2003 - 19:46:56 CST