Not using indexes

From: astalavista <nobody_at_nowhere.com>
Date: Wed, 14 May 2008 21:40:30 +0200
Message-ID: <482b4028$0$4281$426a34cc@news.free.fr>

Hi,

Why the query below
doesn't use the indexes ?
1 index on FORMID, FORMINDEX, SUBJECTID, VISITID on the each tables.
Thanks for your lights.
Oracle 9.2.0.7

p97> SELECT * FROM

  2              CL318886012_ECRF_DW.T_SUBJECTVISITFORM A,
  3              CL318886012_ECRF_DW.T_CUR_QUERY B,
  4              CL318886012_ECRF_DW.T_EVENT_ICLD  C
  5   WHERE
  6      B.FORMID=A.FORMID
  7      AND B.FORMINDEX=A.FORMINDEX
  8      AND B.SUBJECTID=A.SUBJECTID
  9      AND B.VISITID=A.VISITID
 10      AND  C.FORMID=A.FORMID
 11      AND     C.FORMINDEX= A.FORMINDEX
 12      AND     C.SUBJECTID=A.SUBJECTID
 13      AND     C.VISITID=A.VISITID

 14 /

371446 rows selected.

Elapsed: 00:01:10.04

Execution Plan


   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3345 Card=1 Bytes=28
          2)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_CUR_QUERY' (Cost=3 Car
          d=1 Bytes=176)

   2    1     NESTED LOOPS (Cost=3345 Card=1 Bytes=282)
   3    2       HASH JOIN (Cost=3297 Card=16 Bytes=1696)
   4    3         TABLE ACCESS (FULL) OF 'T_EVENT_ICLD' (Cost=294 Card

=1247970 Bytes=47422860)
5 3 TABLE ACCESS (FULL) OF 'T_SUBJECTVISITFORM' (Cost=53 7 Card=1247970 Bytes=84861960) 6 2 INDEX (RANGE SCAN) OF 'T_CUR_QUERY' (NON-UNIQUE) (Cost
=2 Card=1)

Statistics


          0  recursive calls
          0  db block gets
    2747596  consistent gets
      27810  physical reads
          0  redo size
   58181490  bytes sent via SQL*Net to client
     272892  bytes received via SQL*Net from client
      24765  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     371446  rows processed
Received on Wed May 14 2008 - 14:40:30 CDT

Original text of this message