Re: Query tuning

"Yvan GALAS" <yvan.galas_at_fr.adp.com> wrote in message news:b6u75h$iup$1_at_pegase.atos-infogerance.fr...
> Hi,
>
> of course yes... it's the basis of tuning... !!!
>
> 1 - Number of rows in each tables (For the correct ordering of the FROM
> clause... t1 pow t2 is not the same as t2 pow t1)
> 2 - Distinctive data in each columns
> 3 - Put a index on these distinctive data
> etc ......

I think that Guido was after an explain plan, which is in fact the basis of SQL tuning. Meanwhile your first statement is demonstrably false.

SQL> conn niall/niall
Connected.

SQL> drop table t1;

Table dropped.

SQL> create table t1 as select * from all_objects;

Table created.

SQL> create table t2 as select * from all_objects where rownum < 100;

Table created.

SQL> create index i1 on t1(object_id);

Index created.

SQL> create index i2 on t2(object_id);

Index created.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> analyze table t2 compute statistics;

Table analyzed.

SQL> set autotrace traceonly explain
SQL> select count(*)
  2 from t1,t2
  3 where t1.object_id=t2.object_id;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=8)    1 0 SORT (AGGREGATE)

   2    1     NESTED LOOPS (Cost=8 Card=99 Bytes=792)
   3    2       INDEX (FAST FULL SCAN) OF 'I1' (NON-UNIQUE) (Cost=8 Ca
          rd=29430 Bytes=117720)

   4    2       INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE)

SQL> select count(*)
  2 from t2,t1
  3 where t1.object_id=t2.object_id;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=8)    1 0 SORT (AGGREGATE)

   2    1     NESTED LOOPS (Cost=8 Card=99 Bytes=792)
   3    2       INDEX (FAST FULL SCAN) OF 'I1' (NON-UNIQUE) (Cost=8 Ca
          rd=29430 Bytes=117720)

   4    2       INDEX (RANGE SCAN) OF 'I2' (NON-UNIQUE)

Oracle is quite smart enough to come up with the same plan when tables are in a different order in the from clause.

--
Niall Litchfield
Oracle DBA
Audit Commission UK