autotrace issue

From: Sanjay Madan <madan.sanjay_at_gmail.com>
Date: Fri, 14 Oct 2005 16:30:54 +0530
Message-ID: <9eb766040510140400o6c00b86bo6bd32014fc05239e@mail.gmail.com>

Hi ,

Oracle 9.2.0.5 <http://9.2.0.5> on AIX
Partitioned table ( list) ~ 10 mil rows in all distibuted almost equally across 5 partitions
field1 ( used in the query) is not the PK.

My autotrace ( traceonly explain) output for a particular query changes with the actual value of the input parameter.

select .......
where ...........
and field1 = 123456

autotrace output shows an Index range scan whereas

select .......
where ...........
and field1 = 1234561

autotrace output shows a full table scan (not the exact input values) ...

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.

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

Any hints as to what could be happening or where I should look to solve the problem ?

Thanks

sanjay

--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 14 2005 - 06:03:18 CDT