Re: Not using indexes

From: <fitzjarrell_at_cox.net>
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

Original text of this message