RE: autotrace issue

Sanjay

>I have tried values
>-both within and outside the field lengths of field1
>-values actually existing in the table
>-values NOT existing in the table.

Which is the distribution of data in field1? Do you have a histogram on it?

>It seems to be happening randomly. For most values it shows an index range scan.
>For others (fewer) it shows a full table scan.

It's probably because data is not evenly distributed (I don't speak of the distribution at partition level... but the data in field1). If the following queries return very different values it could be good that the CBO generates different plans:

select count(*) from ... where field1 = 123456 select count(*) from ... where field1 = 1234561

HTH
Chris

New Features Oracle Database 10g Release 2 seminars @ www.trivadis.com

Italiano: Lugano (24-Nov)
Français: Genève (17-Nov)
Deutsch: München (20-Oct), Basel (25-Oct), Frankfurt (27-Oct), 
         Düsseldorf (23-Nov), Zürich (29-Nov), Stuttgart (13-Dec)

--
http://www.freelists.org/webpage/oracle-l