Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: FOR ALL COLUMNS -- why??
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