Not using indexes
astalavista
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
ora...@msn.com
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
Mladen Gogala
> Why the query below
> doesn't use the indexes ?
> 1 index on FORMID, FORMINDEX, SUBJECTID, VISITID on the each tables.
> Thanks for your lights.
Indexes are not used because you don't have any limiting conditions.
You have to read all rows from all the tables. Optimizer has concluded,
probably correctly, that it will get the task done faster if it uses
the full table scan.
Mladen Gogala
> Which release of Oracle?
He listed that, 9.2.0.7.
ora...@msn.com
> On Wed, 14 May 2008 13:16:31 -0700, fitzjarr...@cox.net wrote:
> > Which release of Oracle?
>
> He listed that, 9.2.0.7.
>
> --http://mgogala.freehostia.com
Yes, he did. I missed it. Silly me.
David Fitzjarrell