In this recipe, we will see how to use histograms on tables to provide a detailed estimate of value distribution inside a column.
The following steps will show how to represent our data in the form of histograms:
Connect to
SHschema:CONNECT sh@TESTDB/shCreate the table
TEST_HISTwith some data fromALL_OBJECTS:CREATE TABLE sh.TEST_HIST AS SELECT ROWNUM AS ID, OBJECT_NAME AS NAME, MOD(ROWNUM, 10) AS FIELD1, TRUNC(MOD(ROWNUM, 10)/9) AS FIELD2 FROM ALL_OBJECTS;
Query for
FIELD1andFIELD2values grouped to see the data distribution:SELECT FIELD1, COUNT(*) FROM TEST_HIST GROUP BY FIELD1 ORDER BY 1; SELECT FIELD2, COUNT(*) FROM TEST_HIST GROUP BY FIELD2 ORDER BY 1;
Create histograms for column
FIELD1of the tableTEST_HIST:EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'SH', - TABNAME => 'TEST_HIST', - METHOD_OPT => 'FOR COLUMNS SIZE 10 FIELD1');
Query
USER_TAB_HISTOGRAMSto see the values stored in the histogram forFIELD1...