Re: analyze index and cost

No, quite the opposite. In the absence of statistics, the optimizer uses rule-based optimization, and one of the earliest of the (I think it's 15) rules is 'if there's an index available, use it' -even if it doesn't actually make sense to do so.

The point is that by computing statistics, you give the cost-based optimizer a chance to work out whether it would *really* make sense to use the index. In your case, it would appear that the optimizer has decided it would be cheaper not to bother -though clearly, it would seem as if it is making a mistake in arriving at that conclusion.

Working out why that's happening I'll leave to others (Jonathan??!)... but it might be, for example, that db_file_multiblock_read_count is set so high that full table scans appear to the optimizer to be ridiculously cheap. But there's heaps of other potential reasons, and even Jonathan can't answer the question until he sees the precise nature of the tables, indexes and query involved.

All I wanted to point out here is that computing statistics does NOT mean 'things go faster', it means that the optimizer has some basis on which to work out an *intelligent* approach to getting at the data, rather than just blindly following a set of 15 rules that haven't changed in years. Usually that *would* mean performance improvements, but there are plenty of configuration and coding issues which can stop that happening. They need to be diagnosed and resolved -and that simply can't be done in the absence of any further information from you.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"achkar" <kachkar_at_tablimited.com.au> wrote in message
news:acb78eab.0203031619.4307b35b_at_posting.google.com...


> Hi ,

>

> The optimizer does a full table scan when I compute statistics , but

> if there is no statistics ,the optimiser uses that index , now this is

> my point Why the optimizer does that , I though it would be faster

> when I compute statistics but it is not ,

>

> Regards,

> Achkar

>

>

> damorgan <dan.morgan_at_ci.seattle.wa.us> wrote in message

news:<3C7FAAE9.C3F26803_at_ci.seattle.wa.us>...


> > Excellent advise. If that doesn't solve it post the query, the explain

plan,


> > a list of indexes on the table(s), and the number of rows.

> >

> > Are you using any hints?

> >

> > Daniel Morgan

> >

> >

> >

> > santosh sharma wrote:

> >

> > > did you compare the explain plan before and after compute stat ? It

will


> > > point you why this is happening.

> > >

> > > HTh

> > > santysharma

> > >

> > > "achkar" <kachkar_at_tablimited.com.au> wrote in message

> > > news:acb78eab.0202281707.34303a16_at_posting.google.com...

> > > > Hi All ,

> > > >

> > > > I have a big query , it takes 70 min to run , cost is 94819 , when I

> > > > create and index ( XXX ) on one of the tables , the cost drops down

to


> > > > 53770 and takes 30 min to run , but when I do this : analyze index

XXX


> > > > compute statistics , the cost goes up to 94819 again , I dont

> > > > understand why?

> > > >

> > > > thanks in advance