For skew data, histogram provides better statistics based on data distribution to help optimizer "to generate accurate cardinality estimates for filter and join predicates that involve these columns."
see oracle doc:
When you cannot wait for SQL workload to be used to populate filter and join predicates information in COL_USAGE$.
After identifying the need to create histogram(probably after you created extended statistics/group column), the test method provides how to create histogram with workload been executed.
--creating test objects
- SH: IBA> create table test_histogram as select * from dba_objects;
- Table created.
--simulating previous statistic gathering before histogram
- SH: IBA> exec dbms_stats.gather_table_stats(null,'TEST_HISTOGRAM');
- PL/SQL procedure successfully completed.
--Query column histogram information.
- SH: IBA> SELECT COLUMN_NAME ,NUM_DISTINCT,NUM_NULLS,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_name='TEST_HISTOGRAM';
- COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
- ------------------------------ ------------ ---------- ---------------
- OWNER 30 0 NONE
- OBJECT_NAME 46108 0 NONE
- SUBOBJECT_NAME 162 74732 NONE
- OBJECT_ID 75273 0 NONE
- DATA_OBJECT_ID 9540 65663 NONE
- OBJECT_TYPE 45 0 NONE
- CREATED 872 0 NONE
- LAST_DDL_TIME 955 0 NONE
- TIMESTAMP 1007 0 NONE
- STATUS 2 0 NONE
- TEMPORARY 2 0 NONE
- COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
- ------------------------------ ------------ ---------- ---------------
- GENERATED 2 0 NONE
- SECONDARY 2 0 NONE
- NAMESPACE 21 0 NONE
- EDITION_NAME 0 75273 NONE
- 15 rows selected.
- SH: IBA>
--Above show histogram not created.
--Setting preference for dbms_stats
- SH: IBA> exec dbms_stats.set_table_prefs('SH', 'TEST_HISTOGRAM','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 254 OBJECT_TYPE');
- PL/SQL procedure successfully completed.
--gather stat.
- SH: IBA> exec dbms_stats.gather_table_stats(null,'TEST_HISTOGRAM');
- PL/SQL procedure successfully completed.
- SH: IBA> SELECT COLUMN_NAME ,NUM_DISTINCT,NUM_NULLS,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_name='TEST_HISTOGRAM';
- COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
- ------------------------------ ------------ ---------- ---------------
- OWNER 30 0 NONE
- OBJECT_NAME 46108 0 NONE
- SUBOBJECT_NAME 162 74732 NONE
- OBJECT_ID 75273 0 NONE
- DATA_OBJECT_ID 9540 65663 NONE
- OBJECT_TYPE 45 0 FREQUENCY
- CREATED 872 0 NONE
- LAST_DDL_TIME 955 0 NONE
- TIMESTAMP 1007 0 NONE
- STATUS 2 0 NONE
- TEMPORARY 2 0 NONE
- GENERATED 2 0 NONE
- SECONDARY 2 0 NONE
- NAMESPACE 21 0 NONE
- EDITION_NAME 0 75273 NONE
- 15 rows selected.
- SH: IBA>
--this histogram, will always be created when you executed dbms_stats accordingly.
--below show that after workload, database register predicates usage and create histogram for other columns because of "FOR ALL COLUMNS SIZE AUTO"
--run some workload.
- SH: IBA> select OWNER,SUBOBJECT_NAME from TEST_HISTOGRAM where OBJECT_NAME='TEST_HISTOGRAM' and created between sysdate and sysdate -1;
- no rows selected
--gather stats, this create new histogram basic on COL_USAGE.
- SH: IBA> exec dbms_stats.gather_table_stats(null,'TEST_HISTOGRAM');
- PL/SQL procedure successfully completed.
- SH: IBA> SELECT COLUMN_NAME ,NUM_DISTINCT,NUM_NULLS,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_name='TEST_HISTOGRAM';
- COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
- ------------------------------ ------------ ---------- ---------------
- OWNER 30 0 NONE
- OBJECT_NAME 46108 0 NONE
- SUBOBJECT_NAME 162 74732 NONE
- OBJECT_ID 75273 0 NONE
- DATA_OBJECT_ID 9540 65663 NONE
- OBJECT_TYPE 45 0 FREQUENCY
- CREATED 872 0 HEIGHT BALANCED
- LAST_DDL_TIME 955 0 NONE
- TIMESTAMP 1007 0 NONE
- STATUS 2 0 NONE
- TEMPORARY 2 0 NONE
- GENERATED 2 0 NONE
- SECONDARY 2 0 NONE
- NAMESPACE 21 0 NONE
- EDITION_NAME 0 75273 NONE
- 15 rows selected.
Hope you save some time ;)
