forked from fatdba/Oracle-Database-Scripts
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathwhattypeof_histogram.sql
More file actions
84 lines (74 loc) · 2.63 KB
/
whattypeof_histogram.sql
File metadata and controls
84 lines (74 loc) · 2.63 KB
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
--------------------------------
-- Oracle provided script
--Modified: 6-29-16 - Added Substitution variable for table name
--Doc ID 2143577.1
---------------------------------
--Drop Table
drop table &&test_table;
--Create Table
create table &&test_table (id number, retired number);
set serveroutput on
SET LINESIZE 32000;
SET PAGESIZE 40000;
SET LONG 50000;
set echo off
set feed off
declare
counter number;
counter1 number;
ndv number;
tc number;
n number;
p number;
tnv number;
v_column_name varchar2(100);
v_sample_size number;
v_num_nulls number;
v_num_distinct number;
v_histogram varchar2(50);
v_num_buckets number;
begin
select &distinct_counts into ndv from dual;
select (ndv*100)/(100-&popular_data_percentage) into tc from dual;
for counter in 1..tc loop
insert into &&test_table values(counter,0);
end loop;
commit;
for counter1 in 1..ndv loop
update &&test_table set retired=counter1 where id=counter1;
end loop;
commit;
select &num_buckets into n from dual;
-- Calculate tnv and p
select (1-(1/n))*100 into p from dual;
select ((tc-ndv)/tc)*100 into tnv from dual;
-- Create Histograms
DBMS_STATS.GATHER_TABLE_STATS(UPPER('&OWNER'),UPPER('&&test_table'), METHOD_OPT => 'FOR COLUMNS retired size '||n||', id size 1', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE);
SELECT column_name, sample_size, num_nulls, num_distinct, histogram, num_buckets
into v_column_name, v_sample_size, v_num_nulls, v_num_distinct, v_histogram, v_num_buckets
FROM user_tab_col_statistics
WHERE table_name=UPPER('&&test_table') and column_name='RETIRED';
dbms_output.put_line('=====================================================================');
if (ndv>n) then
if (tnv>=p) then
dbms_output.put_line('Historgram To Be Created As Per Algoritm : TOP-FREQUENCY');
else
dbms_output.put_line('Historgram To Be Created As Per Algoritm : HYBRID');
end if;
else
if (ndv=n) then
dbms_output.put_line('Histogram To Be Created As Per Algorithm : NOT CLEAR');
else
dbms_output.put_line('Historgram To Be Created As Per Algorithm : FREQUENCY');
end if;
end if;
dbms_output.put_line('====================================================================');
dbms_output.put_line('NDV (ndv) : '||ndv);
dbms_output.put_line('Buckets Specified (n) : '||n);
dbms_output.put_line('Buckets Calculated : '||v_num_buckets);
dbms_output.put_line('Internal Threashold (p) : '||p);
dbms_output.put_line('TOP N Frequent Values (tnv) : '||tnv);
dbms_output.put_line('Histogram Type : '||v_histogram);
dbms_output.put_line('===================================================================');
end;
/