Re: Scaling Oracle 8i - Histograms

ganesh_at_gtfs-gulf.com (Ganesh Raja) wrote in message news:<a8aed4.0306081930.45e2b27_at_posting.google.com>...
> Hi,
>
> I am not sure If James Morle is part of this list but i know people
> how are on par with him and who answer here .. so i Shoot ...
>
> In His Book in Scaling Oracle 8i in Page Number 278. [ He is tlaking
> here about the Cursor Head and Cursor Body and how Bind Variables are
> handled]
> <Quote>
> The Optimizer Plan can also change between executions, owing to the
> existence of histograms for the value for a column. If a values is
> supplied that can be approached in a more effeciaent way as a Reult of
> cardinality, then a new plan is created and put into a new body ....
> </Quote>
>
> All this while i was thinking that Bind variables dont use Histograms
> and i here i find a diffrent stmt. Since if we use Hard Coded Values
> then there will be two Cursor Heads since the Hash Value for them will
> be diffrent and hence we will use Histograms since the Optimizer Knows
> what value we have passed.
>
> Pls Enlighten.
>
> Thanks.
>
> Regards,
> Ganesh R

There are probably some misunderstandings somewhere...

Histograms helps the optimizer determine or estimate the selectivity of the predicate in the queries. When bind variables are used in the predicates, histograms will not be used and selectivity are estimated as follows:

1. For equality predicates : 1/NDV (# of distinct values)
2. For range predicates : 5%
3. For predicates with like operator : 25%
   • Jusung Yang