Re: question about histograms for CBO

In article <8pu0t8$lnu$1_at_nnrp1.deja.com>,   gdas_at_my-deja.com wrote:
> Is it possible to create a histogram that will take into account the
> distribution of data across multiple columns or just a single column
 at
> a time?
>
> I'm specifically trying to understand the following statement:
>
> analyze table test estimate statistics for columns col_1,col_2,col3
> size 10;
>
> This statement is syntactically correct and it works, but I really
> don't know what it did.
>
> Is it the same as running individual histograms on the columns:
>
> analyze table test estimate statistics for columns col_1 size 10;
> analyze table test estimate statistics for columns col_2 size 10;
> analyze table test estimate statistics for columns col_3 size 10;

You are correct. These two would do the same thing.

>
> Or by placing all columns in one statement, am I getting a different
> histogram that is somehow analyzing and bucketizing all three columns
> together?

Nope.

>
> A good example would probably be a demographic table with last_name,
> age and gender.
>
> Thanks for any help,
> Gavin
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

I think what you may be wanting to do is to build a histogram that would lay out the distribution over a concatenated index. No can do.

If you do

analyze table test estimate statistics for all index columns;

It will do a histogram for all columns which appear in the tables indexes. These stats are all used by the CBO to produce you query plan. So unless the plan is way out of wack I would not worry about it.

In which case there are other ways to get your desired results. Ie hints, outlines, other indexes, etc...

HTH......

--
Doug Coan
Oracle Certified Professional DBA


Sent via Deja.com http://www.deja.com/
Before you buy.