SQL tuning

From: dias <ydias_at_hotmail.com>
Date: 1 May 2004 18:04:11 -0700
Message-ID: <55a68b47.0405011704.3ba734c7@posting.google.com>

Hi,

This small test shows a situation when indexes are added to some tables.
The indexes are needed for other purposes The CBO choose to use the indexes, full tables scan and hash join perform better.

9.2.0.5 on windows 2000

Thanks

create table T1 as select * from all objects
create table T2 as select * from all objects
create table T3 as select * from all objects

analyze tables T1, T2 T3

select count(*) from T1
where exists (select T2.OBJECT_NAME from T2, T3 where T2.OBJECT_ID = T3.OBJECT_ID
and T2.OBJECT_ID=T1.OBJECT_ID)

Elapsed: 00:00:00.03

Execution Plan

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

   2    1     HASH JOIN (SEMI) (Cost=141 Card=25044 Bytes=400704)
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=37 Card=25044
Bytes=75132)
   4    2       VIEW OF 'VW_SQ_1' (Cost=87 Card=25046 Bytes=325598)
   5    4         HASH JOIN (Cost=87 Card=25046 Bytes=150276)
   6    5           TABLE ACCESS (FULL) OF 'T2' (Cost=37 Card=25046
Bytes=75138)
   7    5           TABLE ACCESS (FULL) OF 'T3' (Cost=37 Card=25048

Bytes=75144)

Statistics

          0  recursive calls
          0  db block gets
       1059  consistent gets
          0  physical reads
          0  redo size
        381  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


create index iT1 on T1 (object_id) compute statistics

create index iT2 on T2 (object_id) compute statistics create index iT3 on T3 (object_id) compute statistics

Elapsed: 00:00:01.04

Execution Plan

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

   2    1     FILTER
   3    2       INDEX (FAST FULL SCAN) OF 'IT1' (NON-UNIQUE) (Cost=7
Card=1252 Bytes=3756)
   4    2       MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=6)
   5    4         INDEX (RANGE SCAN) OF 'IT2' (NON-UNIQUE) (Cost=1
Card=1 Bytes=3)
   6    4         BUFFER (SORT) (Cost=1 Card=1 Bytes=3)
   7    6           INDEX (RANGE SCAN) OF 'IT3' (NON-UNIQUE) (Cost=1

Card=1 Bytes=3)

Statistics

          0  recursive calls
          0  db block gets
     100290  consistent gets
          0  physical reads
          0  redo size
        381  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
      25044  sorts (memory)
          0  sorts (disk)
          1  rows processed

Received on Sat May 01 2004 - 20:04:11 CDT