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: FOR ALL COLUMNS -- why??

Re: FOR ALL COLUMNS -- why??

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 17 Sep 2004 22:13:30 -0700
Message-ID: <1095484479.812527@yasure>


Domenic wrote:
> Now that I'm looking at dbms_stats, what is the benefit of calculating
> histograms on columns that aren't indexed? Skew or no skew -- there
> is only one path.
>
> So if I have a "gender" column and 95% are males and 5% are females,
> if it was indexed then this makes sense:
>
> SELECT * FROM whatever WHERE gender = 'F' --> index scan
> SELECT * FROM whatever WHERE gender = 'M' --> full table scan
>
> But if there's no index, what's the point?
>
> Does anyone know of an example?
>
> Domenic.

I recall something on this by either Tom Kyte or Jonathan Lewis but as I am unable to find it in my archives I'll take a shot and hopefully be corrected if I miss.

Not all columns in a WHERE clause used for joins are necessarily indexed. The optimizer can use the statistics to determine things such as join order.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Sat Sep 18 2004 - 00:13:30 CDT

Original text of this message

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