Using index

[julian]

Originally posted by pando
well in 8i and 9i without specifying any clause after analyze table x compute statistics generates by default histrohgram of 75 buckets which is usually enough

According to Oracle having a single level histogram is like having no histogram at all, according to bug 584092, which was closed as not a bug :-)

So, 1 Histogram = 0 Histograms :-)

Happy New Year :-)

[pando]

75 buckets != 1 Histogram

in Oracle 8 it did generate only one bucket but that behaviour has been changed in 8i and 9i

[julian]

Originally posted by pando
75 buckets != 1 Histogram

in Oracle 8 it did generate only one bucket but that behaviour has been changed in 8i and 9i

No, you must specify the FOR_CLAUSE, otherwise only one bucket will be collected. Look at this (9.2):

SQL> analyze table HIT_RATIO compute statistics;

Table analyzed.

SQL> select table_name, column_name, num_buckets from user_tab_col_statistics where rownum = 1;

TABLE_NAME COLUMN_NAME NUM_BUCKETS
------------------------------ -------------------- -----------
HIT_RATIO SID 1

SQL> analyze table HIT_RATIO compute statistics for all indexed columns;

Table analyzed.

SQL> select table_name, column_name, num_buckets from user_tab_col_statistics where rownum = 1;

TABLE_NAME COLUMN_NAME NUM_BUCKETS
------------------------------ -------------------- -----------
HIT_RATIO SID 49

[pando]

aha, right I make a pardon, the default value of SIZE is 75 in 8i and 9i and in 8 it was 1

!