-
Notifications
You must be signed in to change notification settings - Fork 0
/
Histograms.sql
84 lines (67 loc) · 2.61 KB
/
Histograms.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
--Creación de la tabla de trabajo
DROP TABLE T;
CREATE TABLE T
AS SELECT CASE WHEN (ROWNUM BETWEEN 1 AND 10) THEN 1
WHEN (ROWNUM BETWEEN 11 AND 110) THEN 2
WHEN (ROWNUM BETWEEN 111 AND 1110) THEN 3
WHEN (ROWNUM BETWEEN 1111 AND 11110) THEN 4
END X
FROM DUAL
CONNECT BY ROWNUM <= 11110;
--Consulta a la tabla
SELECT X, COUNT(*) FROM T GROUP BY X;
--Creación del histograma sin metodo
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,TABNAME => 'T');
END;
/
--Consulta al histograma
SET LINESIZE 150;
COLUMN TABLE_NAME FORMAT A15;
COLUMN COLUMN_NAME FORMAT A15;
COLUMN ENDPOINT_NUMBER FORMAT 99999;
COLUMN ENDPOINT_VALUE FORMAT 99999;
SELECT TABLE_NAME,NUM_DISTINCT,DENSITY,NUM_BUCKETS FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = 'T';
SELECT TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME = 'T';
--Asignación de metodo
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,TABNAME => 'T', METHOD_OPT =>'FOR ALL COLUMNS SIZE 4');
END;
/
--Conslta al histograma
SELECT TABLE_NAME,NUM_DISTINCT,DENSITY,NUM_BUCKETS,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = 'T';
SELECT TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME = 'T';
--Histograma de altura equilibrada
--Tabla de trabajo
DROP TABLE T;
CREATE TABLE T
AS SELECT ROWNUM X
FROM DUAL
CONNECT BY ROWNUM <= 11110;
UPDATE T SET X = 1 WHERE ROWNUM <= 5000;
UPDATE T SET X = 2 WHERE ROWNUM <= 3000 AND X != 1;
CREATE INDEX T_IDX ON T(X);
--Histograma creado
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,TABNAME => 'T', CASCADE =>TRUE, METHOD_OPT =>'FOR ALL COLUMNS SIZE 1');
END;
/
--Caracteristicas del histograma
SELECT TABLE_NAME,NUM_DISTINCT,DENSITY,NUM_BUCKETS FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = 'T';
SELECT TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME = 'T';
--Consulta simple sobre la tabla
EXPLAIN PLAN FOR SELECT COUNT(1) FROM T WHERE X = 1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
--Histograma correcto
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,TABNAME => 'T', CASCADE =>TRUE);
END;
/
--Caracteristicas del histograma
SELECT TABLE_NAME,NUM_DISTINCT,DENSITY,NUM_BUCKETS,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = 'T';
SELECT TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME = 'T';
--Consulta simple sobre la tabla
EXPLAIN PLAN FOR SELECT COUNT(1) FROM T WHERE X = 1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
--Comprobación
SELECT (11110/254)*113 FROM DUAL;