Analyze makes it FULL table scan??

From: Paul Bennett <bennett_at_cc.gatech.edu>
Date: Sat, 11 Dec 1999 02:27:57 GMT
Message-ID: <3851B6CE.2FF4E7C6@cc.gatech.edu>

I have a table with a few columns in it, a, b and c.

there is a primary index on a,b,c in that order. there is a bitmap index on b.

with the query

select * from blah where b = 'HELLO'

and with:

analyze table blah delete statistics.

I get the following show plan:

OPERATIONS                OPTIONS         OBJECT_NAME
------------------------- --------------- ------------------
  TABLE ACCESS            BY INDEX ROWID  BLAH
   INDEX                 RANGE SCAN      BLAH_B

with statistics computed, I get

OPERATIONS                OPTIONS         OBJECT_NAME
------------------------- --------------- ---------------------
  TABLE ACCESS            FULL            BLAH

why is it now deciding to do a full table scan? It is NOT faster to do a full table scan.

I was having trouble with a larger query and was able to resolve it down to the simpler example above. When I run the more complicated query without the statistics computed it runs just as slow as with it. I need to be able to resolve this issue so that I can compute the statistics and have the query running at the speed that I think it should run at instead of some cartesian product type speed that it is displaying.

Any sugestions?

• Paul

Received on Fri Dec 10 1999 - 20:27:57 CST