Re: Analyze makes it FULL table scan??

Or force it to use the index directly by inserting

SELECT /*+ index (table_name index_name) */ * from blah
where b = 'HELLO'

That is, if you are sure the index is faster than a full table scan. Yes, the cost-based optimizer makes mistakes.

Salaam Yitbarek

In article <82t3ke$b8b$1_at_nnrp1.deja.com>,   michael_bialik_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 13 1999 - 10:34:19 CST