Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Question about Column data...

Re: Question about Column data...

From: <sergey_s_at_my-deja.com>
Date: Sat, 26 Aug 2000 00:33:01 GMT
Message-ID: <8o737s$vn$1@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 Fri Aug 25 2000 - 19:33:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US