Re: Physical reads on table very high.

"Ethan Post" <epost1NOepSPAM_at_yahoo.com.invalid> wrote in message news:02eace00.04b29b5e_at_usw-ex0105-037.remarq.com...
> Ok here is execution plan:
>
> SQL> SELECT * FROM SVMB733.F986110 WHERE ( JCJOBQUE
> = 'QB7331 '
> 2 AND JCJOBSTS = 'D ' AND
> 3 JCEXEHOST = 'hq-asprd '
> 4 AND JCFUNO = 'UBE '
> 5 AND JCPRTQ = '7100 ' );
>
> Execution Plan
> ----------------------------------------------------------
> 0
> SELECT STATEMENT Optimizer=CHOOSE (Cost=831 Card=3112
> Bytes=1191896)
>
>
> 1 0
> TABLE ACCESS (FULL) OF 'F986110' (Cost=831 Card=3112
> Bytes=1191896)
>
> So it is obviously using full table scan but here is the index I
> created:
>
> create index jdeF986110_01 on F986110 (JCJOBQUE, JCJOBSTS,
> JCEXEHOST, JCFUNO, JCPRTQ)
> tablespace svmb733i;
>
> Same columns as in the SQL statement why doesn't the optimizer
> see that this is the best way to go???
>
> Thanks,
> Ethan
>
>
> -----------------------------------------------------------
>
> Got questions? Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com
>

There's one strange thing that strikes me: the trailing spaces in the literals in the where clauses. What's the datatype of that column. Not varchar2 I hope? In that case there is a chance Oracle pads the table column to compare and hence doesn't use the index.
A different set of reasons might be
- the index is not selective enough
- the most discriminating column in the query is not the leading column in the index.
- There are no statistics on the index.
- Multiblock_read_count is so high, it is always more advanteageous *not* to use the index.

Food for thought, hopefully.

Regards,

Sybrand Bakker, Oracle DBA Received on Thu Jul 13 2000 - 00:00:00 CDT