MANUALLY CREATE HISTOGRAM WITHOUT WORKLOAD OR COLUMN USAGE INFORMATION IN SYS.COL_USAGE$

MANUALLY CREATE HISTOGRAM WITHOUT WORKLOAD OR COLUMN USAGE INFORMATION IN SYS.COL_USAGE$


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

1. SH: IBA> create table test_histogram as select * from dba_objects;
2. 
   
3. Table created.

--simulating previous statistic gathering before histogram

1. SH: IBA> exec dbms_stats.gather_table_stats(null,'TEST_HISTOGRAM');
2. 
   
3. PL/SQL procedure successfully completed.

--Query column histogram information. 

1. SH: IBA> SELECT COLUMN_NAME ,NUM_DISTINCT,NUM_NULLS,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_name='TEST_HISTOGRAM';

1. COLUMN_NAME       NUM_DISTINCT  NUM_NULLS HISTOGRAM
2. ------------------------------ ------------ ---------- ---------------
3. OWNER 30     0 NONE
4. OBJECT_NAME      46108     0 NONE
5. SUBOBJECT_NAME 162 74732 NONE
6. OBJECT_ID      75273     0 NONE
7. DATA_OBJECT_ID       9540 65663 NONE
8. OBJECT_TYPE 45     0 NONE
9. CREATED 872     0 NONE
10. LAST_DDL_TIME 955     0 NONE
11. TIMESTAMP       1007     0 NONE
12. STATUS  2     0 NONE
13. TEMPORARY  2     0 NONE
14. 
    
15. COLUMN_NAME       NUM_DISTINCT  NUM_NULLS HISTOGRAM
16. ------------------------------ ------------ ---------- ---------------
17. GENERATED  2     0 NONE
18. SECONDARY  2     0 NONE
19. NAMESPACE 21     0 NONE
20. EDITION_NAME  0 75273 NONE
21. 
    
22. 15 rows selected.
23. 
    
24. SH: IBA>

--Above show histogram not created.
--Setting preference for dbms_stats

1. SH: IBA>  exec dbms_stats.set_table_prefs('SH', 'TEST_HISTOGRAM','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 254 OBJECT_TYPE');
2. 
   
3. PL/SQL procedure successfully completed.

--gather stat.

1. SH: IBA> exec dbms_stats.gather_table_stats(null,'TEST_HISTOGRAM');
2. 
   
3. PL/SQL procedure successfully completed.
4. 
   
5. SH: IBA>  SELECT COLUMN_NAME ,NUM_DISTINCT,NUM_NULLS,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_name='TEST_HISTOGRAM';
6. 
   
7. COLUMN_NAME       NUM_DISTINCT  NUM_NULLS HISTOGRAM
8. ------------------------------ ------------ ---------- ---------------
9. OWNER 30     0 NONE
10. OBJECT_NAME      46108     0 NONE
11. SUBOBJECT_NAME 162 74732 NONE
12. OBJECT_ID      75273     0 NONE
13. DATA_OBJECT_ID       9540 65663 NONE
14. OBJECT_TYPE 45     0 FREQUENCY
15. CREATED 872     0 NONE
16. LAST_DDL_TIME 955     0 NONE
17. TIMESTAMP       1007     0 NONE
18. STATUS  2     0 NONE
19. TEMPORARY  2     0 NONE
20. GENERATED  2     0 NONE
21. SECONDARY  2     0 NONE
22. NAMESPACE 21     0 NONE
23. EDITION_NAME  0 75273 NONE
24. 
    
25. 15 rows selected.
26. 
    
27. 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.

1. SH: IBA> select OWNER,SUBOBJECT_NAME from TEST_HISTOGRAM where OBJECT_NAME='TEST_HISTOGRAM' and created between sysdate and sysdate -1;
2. 
   
3. no rows selected

--gather stats, this create new histogram basic on COL_USAGE.

1. SH: IBA> exec dbms_stats.gather_table_stats(null,'TEST_HISTOGRAM');
2. 
   
3. PL/SQL procedure successfully completed.
4. 
   
5. SH: IBA> SELECT COLUMN_NAME ,NUM_DISTINCT,NUM_NULLS,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_name='TEST_HISTOGRAM';
6. 
   
7. COLUMN_NAME       NUM_DISTINCT  NUM_NULLS HISTOGRAM
8. ------------------------------ ------------ ---------- ---------------
9. OWNER 30     0 NONE
10. OBJECT_NAME      46108     0 NONE
11. SUBOBJECT_NAME 162 74732 NONE
12. OBJECT_ID      75273     0 NONE
13. DATA_OBJECT_ID       9540 65663 NONE
14. OBJECT_TYPE 45     0 FREQUENCY
15. CREATED 872     0 HEIGHT BALANCED
16. LAST_DDL_TIME 955     0 NONE
17. TIMESTAMP       1007     0 NONE
18. STATUS  2     0 NONE
19. TEMPORARY  2     0 NONE
20. GENERATED  2     0 NONE
21. SECONDARY  2     0 NONE
22. NAMESPACE 21     0 NONE
23. EDITION_NAME  0 75273 NONE
24. 
    
25. 15 rows selected.

Hope you save some time ;)