SEO

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

On May 14, 2:40*pm, "astalavista" <nob...@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

On Wed, 14 May 2008 21:40:30 +0200, astalavista wrote:

> 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.



--
http://mgogala.freehostia.com

On Wed, 14 May 2008 13:16:31 -0700, fitzjarrell@cox.net wrote:

> Which release of Oracle?

He listed that, 9.2.0.7.



--
http://mgogala.freehostia.com

On May 14, 4:05*pm, Mladen Gogala <mgog...@yahoo.com> wrote:
> 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