Re: Improve query performance

"VC" <boston103_at_hotmail.com> wrote in message news:kWuBb.349711$ao4.1171224_at_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
> >
> >
>
>

Well I'll admit that proves my statement incorrect. Although in most cases I've seen, the execution plans were identical.

In this case the not in is actually performing better and if the CBO was smarter it would/should have chosen the "not in" plan for the "not exists" query considering the join columns are "not null", which makes the two queries identical.

Anurag Received on Tue Dec 09 2003 - 22:27:00 CST