Re: Rule Based Optimization Going Away?

This could be one of those things which happen so easily - forgetting which version of Oracle does what, when and how !

You will find that the figures used for an object without statistics vary, possibly on each run of the query. This is because Oracle knows where the HWM (since it has the segment
header information available) and will recalculate the statistics based on a couple of formulae which involve the number of blocks. (Breitling has determined empirically a formula that Oracle uses for column selectivity based on number of blocks - I've already mentioned that the number of rows is derived from an assumed typical column size). This "estimate by checking the HWM" may be what the author had in mind
when making that comment.

--
Regards

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

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

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





Norman Dunbar wrote in message ...


>Comments in-line ...

>

><QUOTE>

>it is important that statistics be generated for all objects in all the

>application schemas. This is because the presence of partial statistics

>could cause the server process servicing the SQL statement to estimate

>statistics on the objects without statistics only for the execution of

>that SQL statement. Such dynamic runtime sampling of statistics in not

>permanently stored in the data dictionary and hence is repeated for

>every run of the same query. This can and will cause performance

>degredation.

></QUOTE>

>