Databases Reference
In-Depth Information
How to do it...
The following steps will show how to represent our data in the form of histograms:
1.
Connect to
SH
schema:
CONNECT sh@TESTDB/sh
2.
Create the table
TEST_HIST
with some data from
ALL_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;
3.
Query for
FIELD1
and
FIELD2
values 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;
4.
Create histograms for column
FIELD1
of the table
TEST_HIST
:
EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'SH', -
TABNAME => 'TEST_HIST', -
METHOD_OPT => 'FOR COLUMNS SIZE 10 FIELD1');
5.
Query
USER_TAB_HISTOGRAMS
to see the values stored in the histogram
for
FIELD1
:
SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'TEST_HIST' AND COLUMN_NAME = 'FIELD1'
ORDER BY 2;
