Understanding histograms

If I am running dbms_stats.gather_schema_stats(user, 'TIME', method_opt=> 'for all indexed columns size skewonly', cascade => true ); Why would a single column, primary key, of varchar2 datatype get a histogram generated. That could explain a lot to me. I thought if a column is unique it would only need 1 bucket. Is that not true with varchar2 columns. If, on a smaller scale, my column looks like:

10-245
10-456
10-984
10-999
15-875
50-3000
50-450
50-451
50-780
90-800

That is a unique column but would it get histograms generated if I used "for all
indexed columns size skewonly"? I see that there are clusters of values that look similar for the first 3 and 4 positions, is that enough to make it a candidate for a histogram?

On the other hand, I also have a char(1) column that is either Y or N. Most of the 100,000 records have an N but yet I only get 1 bucket for this column. The Y's are mixed throughout in no specific pattern. I wouldn't think that is sufficient.

example:
N
N
N
Y
N
N
N
N
N
Y
Y
N
N

I am only getting a 1 bucket histogram for a column like this, of course on a
much larger scale.

If one value out of two possible makes up less than 5% or 10% are histograms not
created? In my example I have 3.3 million N's and only 100 thousand Y's.

I've also posted this on asktom, but I think he needs a better example than what I am able to produce. He asked me to use mod and rowid to generate a data set that we can use, I just don't know how to go about doing that.

Thanks for any responses. Received on Tue Mar 14 2006 - 07:49:59 CST