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 processedReceived on Wed May 14 2008 - 14:40:30 CDT