In article <83376b$chl$1_at_nnrp1.deja.com>, yitbsal_at_statcan.ca says...
> 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.
>
Remember too, that if your index is non selective, you will have to read the whole
index file and then have to read many records from the table. This can be worse than
doing a full table scan. Let's say you have 5,000 index rows that match the criteria.
So now you have read 5,000 rows in the index and now must read 5,000 rows in the table
for a total of 10,000 rows. This is a savings only if your table is bigger than 10,000
rows. If your table is only 6,000 rows, then you read 4,000 more rows than a full table
scan would have.
Liz
--
My real address is L I Z R a t A O L d o t C O M
Received on Wed Dec 15 1999 - 09:48:33 CST
