Re: Not using indexes
Date: Wed, 14 May 2008 13:16:31 -0700 (PDT)
Message-ID: <b8e5ed6a-a728-48f7-bec2-bb6104510b91@l64g2000hse.googlegroups.com>
On May 14, 2:40 pm, "astalavista" <nob..._at_nowhere.com> wrote:
> 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
Any number of reasons, I suspect:
Stale statistics
'Bad' statostics (skewed data)
'Bad' clustering factor
Absence of histograms
Which release of Oracle? What statistics gathering method are you using? No one can really say what may be 'wrong' without knowing more about the data and the statistics collected on it.
David Fitzjarrell Received on Wed May 14 2008 - 15:16:31 CDT