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>
14 /
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
