Re: dbms_stats default

I use indexed columns and non-indexed columns in WHERE clauses. My question is, for non indexed-columns, is gathering stats on density low val high val and distinct values going to have any affect on the access plan generated? It seems unlikely that it would since Oracle has 1 and only 1 way to access that data, through a table access. The reason I want to know this is because using the default method produces sql similar to the following:

SELECT count(*),count(col1),count(distinct col1),min(col1),max(col1),count(col2),count(distinct col2),min(col2),...

For wide tables, I saw a 3X increase in response time when gathering stats for all columns in the table vs just those that are indexed.

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E035373.FA2B0663_at_exesolutions.com...
> John Darrah wrote:
>
> > I was looking at the dbms_stats package and noticed that the
> > gather_table_stats procedure defaults to gathering statistics for all
> > columns on a table. It seems to me that the only columns I would want
to
> > gather stats on would be those that are indexed. Does anybody know if
the
> > optimizer can do anything with stats on non indexed columns? It can't
effect
> > join order but does it affect how predicates in the where clause are
> > applied? Any help would be appreciated.
> >
> > Thanks
> >
> > John
>
> Do you only use indexed columns in your WHERE clauses?
>
> Daniel Morgan
>
Received on Tue Dec 24 2002 - 17:04:14 CST