Re: analyze index and cost

The purpose of using the analyze command (deprecated from 8.1.7 in favour of the
dbms_stats package) is to tell the database engine as much as you can about the database.

However, there are lots of either factors involved in telling the truth about your database. This includes, but is not restricted to, things like:

    column X is never null

    column Y holds values unique across the table

    I can read 128 sequential blocks off a disc in     no more time than it takes to read just one block.

    I have half a GB of memory available for each     sort/merge join that I do.

    User X is allowed no more than 10% of the CPU     if the CPU is stressed.

If you mislead Oracle on ANY of this information, you can find that rule based optimisation gets a result faster than cost-based optimisation - especially when incorrect details for points 3 and 4 are supplied and persuade Oracle to use tablescans instead of available indexes.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


godmann wrote in message <95cd51c.0203032148.4e6c9df5_at_posting.google.com>...


>Howard,

>

>  How do you revert back says analyze .. compute statistics is bad? If that

>is the case what is the point of analyzing some tables or indexes with a

view


>to provide better stats. Oracle may not use it at all!

>

>  I always thought that at all times, I should do analyze to make it run

>faster!

>

>Allan W. Tham

>Oracle DBA

>