Re: Question about Column data...

Sergey,

I believe the Oracle documentation is kind of misleading up until the doc for Version 8.1.6. The latter, correct documentation, available at http://technet.oracle.com/doc/oracle8i_816/server.816/a76992/hints.htm#13547 has something prepended to that sentence: "Barring the use of frequency histograms, the number of occurrences of each distinct column value is not available to the optimizer..."

Yong Huang
yhuang_at_indigopool.com

<sergey_s_at_my-deja.com> wrote in message news:8o737s$vn$1_at_nnrp1.deja.com...
> I beleive the following answers my question. I found this in Oracle
> on-line docs. Sounds like in Oracle there is no way to find out how the
> data is distributed in a column other than by select count(distinct ...)
>
> "... The number of occurrences of each distinct column value is not
> available to the optimizer. The cost-based approach assumes that each
> value has an equal probability of appearing in each row. For a column
> having only two distinct values, the optimizer assumes each value
> appears in 50% of the rows, so the cost-based approach is likely to
> choose a full table scan rather than an index scan.
>
> If you know that the value in the WHERE clause of your query appears in
> a very small percentage of the rows, you can use the INDEX hint to force
> the optimizer to choose an index scan ..."
>
> Sergey
>
> In article <8o5ur7$kg7$1_at_nnrp1.deja.com>,
> sergey_s_at_my-deja.com wrote:
> > I found that histograms tell the cost based optimizer how the data is
> > distributed within a column (meaning what percentage of rows in a
> > table have a certain value within a column). I also found out which
> > dictionary views are being used to hold histogram information:
> >
> > DBA_HISTOGRAMS, USER_TAB_COLUMNS (maybe some others as well)
> >
> > Now, can anyone tell me how do I query these views to find out myself
> > what the data distribution in a column is? I looked at the views, read
> > some books and found nothing helpful so far.
> >
> > Thank you!
> >
> > Sergey
> >
> > In article <8nhvgq$6oe$1_at_nnrp1.deja.com>,
> > sergey_s_at_my-deja.com wrote:
> > > How can I find out how the values in a column are distributed? Say
 there
> > > are two values in a column. I would like to know how many rows would
 be
> > > returned if I constrained on one value or the other. The table is
 huge
> > > so count will take forever, is there a view I can query?
> > >
> > > Thank you!
> > >
> > > Sergey
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Aug 28 2000 - 12:22:44 CDT