Re: Analyze makes it FULL table scan??

Hi.

 When you run your query after ANALYZE TABLE blah DELETE STATISTICS  Oracle uses Rule Based Optimizer ( RBO ).  RBO uses index if one exists.
 On the other hand - Cost Based Optimizer considers your data  statistics as well ( if the number of rows in table is very small or  index selectivity is bad - it will use FULL table scan ).

 So in your example either you don't have too many rows in your table  or lots of rows ( > 10 % I think ) contain 'HELLO' in b field.

 Try inserting hint /*+ first_rows */ in your original SELECT  statemet.

 HTH. Michael.

In article <3851B6CE.2FF4E7C6_at_cc.gatech.edu>,   Paul Bennett <bennett_at_cc.gatech.edu> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Dec 11 1999 - 02:56:46 CST