- Notifications
You must be signed in to change notification settings - Fork 849
/
Copy pathcheck_hist.sql
88 lines (82 loc) · 2.88 KB
/
check_hist.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
85
86
87
88
PROMPT ======================================================================================
PROMPT Check histograms for all (sub)partitions
PROMPT ======================================================================================
set serveroutput on
set verify off
set feedback off
set linesize 1000
set trims on
set serveroutput on
column column_name format a40
column table_name format a40
column partition_name format a40
column subpartition_name format a40
accept ptable prompt 'Enter the name of the partitioned table: '
prompt
prompt Table-level histograms:
prompt
select column_name
from user_tab_col_statistics
where histogram !='NONE'
and table_name =upper('&ptable')
order by column_name;
prompt
prompt Partition columns that have histograms not present at table-level:
prompt
break on partition_name
select partition_name,column_name
from user_part_col_statistics
where histogram !='NONE'
and table_name =upper('&ptable')
and column_name not in (select column_name
from user_tab_col_statistics
where histogram is not null
and histogram !='NONE'
and table_name =upper('&ptable'))
order by partition_name,column_name;
clear breaks
prompt
prompt Subpartition columns that have histograms not present at table-level:
prompt
break on subpartition_name
select subpartition_name,column_name
from user_subpart_col_statistics
where histogram !='NONE'
and table_name =upper('&ptable')
and column_name not in (select column_name
from user_tab_col_statistics
where histogram is not null
and histogram !='NONE'
and table_name =upper('&ptable'))
order by subpartition_name,column_name;
clear breaks
prompt
prompt Partition columns missing histograms that exist at table-level:
prompt
break on partition_name
select partition_name, column_name
from user_part_col_statistics
where histogram ='NONE'
and table_name =upper('&ptable')
and column_name in (select column_name
from user_tab_col_statistics
where histogram is not null
and histogram !='NONE'
and table_name =upper('&ptable'))
order by partition_name,column_name;
clear breaks
prompt
prompt Subpartition columns missing histograms that exist at table-level:
prompt
break on subpartition_name
select subpartition_name, column_name
from user_subpart_col_statistics
where histogram ='NONE'
and table_name =upper('&ptable')
and column_name in (select column_name
from user_tab_col_statistics
where histogram is not null
and histogram !='NONE'
and table_name =upper('&ptable'))
order by subpartition_name,column_name;
clear breaks
