Re: Performance problem - help appreciated.

"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 Received on Wed Mar 31 2004 - 20:01:42 CST