Databases Reference
In-Depth Information
3.
Insert eight records with different values for the
ID
field:
INSERT INTO sh.MY_TEST (ID, NAME)
VALUES (2, 'ONLY THIS RECORD HAS ID=2');
INSERT INTO sh.MY_TEST (ID, NAME)
VALUES (3, 'ONLY THIS RECORD HAS ID=3');
INSERT INTO sh.MY_TEST (ID, NAME)
VALUES (4, 'ONLY THIS RECORD HAS ID=4');
INSERT INTO sh.MY_TEST (ID, NAME)
VALUES (5, 'ONLY THIS RECORD HAS ID=5');
INSERT INTO sh.MY_TEST (ID, NAME)
VALUES (6, 'ONLY THIS RECORD HAS ID=6');
INSERT INTO sh.MY_TEST (ID, NAME)
VALUES (7, 'ONLY THIS RECORD HAS ID=7');
INSERT INTO sh.MY_TEST (ID, NAME)
VALUES (8, 'ONLY THIS RECORD HAS ID=8');
INSERT INTO sh.MY_TEST (ID, NAME)
VALUES (9, 'ONLY THIS RECORD HAS ID=9');
COMMIT;
4.
Create an index on the
ID
field:
CREATE INDEX X1_MY_TEST ON MY_TEST (ID);
5.
Query the
MY_TEST
table, to see the skewed data in the
ID
field:
SELECT ID, COUNT(*)
FROM sh.MY_TEST
GROUP BY ID ORDER BY 1;
6.
Collect statistics and histograms on the
MY_TEST
table and the
ID
field:
EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'SH', -
TABNAME => 'MY_TEST', -
ESTIMATE_PERCENT => 100, -
METHOD_OPT => 'FOR COLUMNS SIZE 10 ID');
7. Query the histogram values to confirm that they reflect data distribution:
SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'MY_TEST' AND COLUMN_NAME = 'ID'
ORDER BY 2;
