Re: Performance problem - help appreciated.

Hello Anurag,

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:aKKac.25746$u_2.22606_at_nwrddc01.gnilink.net...
>
> "VC" <boston103_at_hotmail.com> wrote in message
news:XaIac.148494$1p.1884679_at_attbi_s54...
> > Hello,
> >
> >
> > "Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message
> > news:uvvl60lm23pa3e4hasvjmdkdq7lhp6n3ac_at_4ax.com...
> > > On Tue, 30 Mar 2004 22:38:43 GMT, "VC" <boston103_at_hotmail.com> wrote:
> > >
> > > >I am sorry to say that but the order of tables in the FROM clause is
> > simply
> > > >irrelevant since the CBO re-arranges them as it sees fit -- ther is
no
> > > >notion of the 'drivng' table under CBO. The order does matter under
the
> > RBO.
> > >
> > > I'm sorry to say my experience is to the contrary.
> > > The order does matter.
> >
> > Would you be able to post a query which would confirm your statement ?
I am
> > genuinely curious.
> >
> > Thanks.
> >
> > VC
> >
> > >
> > >
> > > --
> > > Sybrand Bakker, Senior Oracle DBA
> >
> >
>
> Some times the order does matter ... only when the tables are very similar
and have similar stats ..
> i.e. when oracle cannot decide conclusively. Then the tables are joined
left to right (opposite of
> RBO).
>
> But this is usually a *RARE* case and most probably does not apply in OP's
case.
>
> The following example shows it (shown in Oracle 9.2.0.3):
>
> SQL> create table t1 as select table_name c1, 'X' c2 from user_tables
where rownum < 100;
>
> Table created.
>
> SQL> create table t2 as select * from t1;
>
> Table created.
>
> SQL> create unique index t1_idx on t1(c1);
>
> Index created.
>
> SQL> create unique index t2_idx on t2(c1);
>
> Index created.
>
> SQL> exec dbms_stats.gather_table_stats(user,'T1', cascade=>true);
>
> PL/SQL procedure successfully completed.
>
> SQL> exec dbms_stats.gather_table_stats(user,'T2', cascade=>true);
>
> PL/SQL procedure successfully completed.
>
>
> SQL> select * from t1, t2 where t1.c1 = t2.c1;
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=99 Bytes=3762)
> 1 0 HASH JOIN (Cost=5 Card=99 Bytes=3762)
> 2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=99 Bytes=1881)
> 3 1 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=99 Bytes=1881)
>
>
>
> SQL> select * from t2, t1 where t1.c1 = t2.c1;
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=99 Bytes=3762)
> 1 0 HASH JOIN (Cost=5 Card=99 Bytes=3762)
> 2 1 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=99 Bytes=1881)
> 3 1 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=99 Bytes=1881)
>
>
>
> SQL> select * from t1, t2 where t1.c1 = t2.c1 and t1.c1 ='XYZ';
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=38)
> 1 0 NESTED LOOPS (Cost=2 Card=1 Bytes=38)
> 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=1 Card=1
Bytes=19)
> 3 2 INDEX (UNIQUE SCAN) OF 'T2_IDX' (UNIQUE)
> 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1 Card=1
Bytes=19)
> 5 4 INDEX (UNIQUE SCAN) OF 'T1_IDX' (UNIQUE)
>
>
>
> SQL> select * from t2, t1 where t1.c1 = t2.c1 and t1.c1 ='XYZ';
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=38)
> 1 0 NESTED LOOPS (Cost=2 Card=1 Bytes=38)
> 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1 Card=1
Bytes=19)
> 3 2 INDEX (UNIQUE SCAN) OF 'T1_IDX' (UNIQUE)
> 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=1 Card=1
Bytes=19)
> 5 4 INDEX (UNIQUE SCAN) OF 'T2_IDX' (UNIQUE)
>
>
>
> I guess this is what Sybrand is talking about.
>
> Anurag

Thank you for the example. It's not quite what I expected since as you correctly noticed the order in this case is immaterial and the optimizer can as well use the tables in the order (reverse) they are specified. What I am interested in would be the case where the order you specify (without the hint, naturally) would matter and the optimizer would heed it.

Thanks.

VC
>
>
Received on Thu Apr 01 2004 - 06:23:38 CST