Hi,
I am on 11.2.0.3.
I created a table, inserted data in it, indexes are there and gathred stats on the table. Here are the SQLs of my activity.
Question is, why histograms were not created in first 2 attempts (i.e. with default method_opt and SIZE AUTO) ?
SQL> select index_name, column_name from user_ind_columns where table_name='TABLE1';
INDEX_NAME COLUMN_NAME
------------------------------ -----------
TABLE1_IDX2 ATTR_NUM --- Skewed column
TABLE1_IDX1 ID
TABLE1_IDX1 ATTR_NUM
TABLE1_IDX1 SEQ_ID
SQL> select count(*) from table1;
COUNT(*)
----------
159998
-- First try
SQL> begin
2 dbms_stats.gather_table_stats( ownname => USER,
3 tabname => 'TABLE1',
4 cascade => TRUE);
5 end;
6 /
-- No histograms created
SQL> select table_name, column_name, histogram from user_tab_col_statistics where table_name='TABLE1';
TABLE_NAME COLUMN_NAME HISTOGRAM
------------------------------ ------------------------------ ---------------
TABLE1 ID NONE
TABLE1 ATTR_NUM NONE
TABLE1 START_DATE NONE
TABLE1 SEQ_ID NONE
TABLE1 ATTR_VALUE NONE
TABLE1 ATTR_STATUS NONE
TABLE1 END_DATE NONE
TABLE1 EX_DATE NONE
-- Second try
SQL> begin
2 dbms_stats.gather_table_stats( ownname => USER,
3 tabname => 'TABLE1',
4 cascade => TRUE,
5 method_opt => 'FOR ALL COLUMNS SIZE AUTO');
6 end;
7 /
-- Still no histograms created
SQL> select table_name, column_name, histogram from user_tab_col_statistics where table_name='TABLE1';
TABLE_NAME COLUMN_NAME HISTOGRAM
------------------------------ ------------------------------ ---------------
TABLE1 ID NONE
TABLE1 ATTR_NUM NONE
TABLE1 START_DATE NONE
TABLE1 SEQ_ID NONE
TABLE1 ATTR_VALUE NONE
TABLE1 ATTR_STATUS NONE
TABLE1 END_DATE NONE
TABLE1 EX_DATE NONE
-- Third try
SQL> begin
2 dbms_stats.gather_table_stats( ownname => USER,
3 tabname => 'TABLE1',
4 cascade => TRUE,
5 method_opt => 'FOR ALL COLUMNS SIZE 254');
6 end;
7 /
-- Finally histograms created, but look at the sample size
SQL> select table_name, column_name, num_buckets, sample_size, histogram
2 from user_tab_col_statistics where table_name='TABLE1';
TABLE_NAME COLUMN_NAME NUM_BUCKETS SAMPLE_SIZE HISTOGRAM
--------------- --------------- ----------- ----------- ---------------
TABLE1 ID 254 5357 HEIGHT BALANCED
TABLE1 ATTR_NUM 25 5357 FREQUENCY
TABLE1 START_DATE 254 5357 HEIGHT BALANCED
TABLE1 SEQ_ID 5 5358 FREQUENCY
TABLE1 ATTR_VALUE 255 5357 HEIGHT BALANCED
TABLE1 ATTR_STATUS 1 5357 FREQUENCY
TABLE1 END_DATE 1 5357 FREQUENCY
TABLE1 EX_DATE 0 NONE
Thanks in advance