[Home] [Help]
PROCEDURE: SYS.DBMS_FEATURE_ZMAP
Source
1 PROCEDURE dbms_feature_zmap
2 (is_used OUT number, zmap_ratio OUT number, clob_rest OUT clob)
3 AS
4 BEGIN
5 -- initialize
6 is_used := 0;
7 zmap_ratio := 0;
8 clob_rest := '{ "data": [';
9
10 FOR crec IN (SELECT
11 -- zone map existent
12 DECODE(xx.sumobj#,NULL,'N','Y') zmap_used,
13 '{ ' ||
14 -- zone map existent
15 CASE WHEN xx.sumobj# IS NOT NULL THEN
16 '"zmap": { ' ||
17 -- '"zmap":"' || DECODE(xx.sumobj#,NULL,'N','Y') ||'",' ||
18 '"zm_w_attrcl":"' || DECODE(bitand(xx.cflags,8),8,'Y','N') ||'",' ||
19 '"joined_zmap":"' || DECODE(bitand(xx.xpflags,137438953472),137438953472,'Y','N') || '",' ||
20 '"zm_scale":' || zmapscale || ','||
21 '"num_joins":' || numjoins || ','||
22 '"num_tabs":' || numdetailtab || ','||
23 '"num_cols":' || (numaggregates - numjoins-1)/2 || ','||
24 '"refresh_mode":"'|| DECODE(bitand(mflags,65536),65536,'C',
25 DECODE(bitand(mflags,34359738368+68719476736),34359738368+68719476736,'L+M',
26 34359738368,'L', 68719476736,'M','D')) || '",'||
27 '"disabled":"' || DECODE(bitand(mflags,4),4,'Y','N') || '",'||
28 '"unusable":"' || DECODE(bitand(mflags,17179869184),17179869184,'Y','N') || '",'||
29 '"invalid":"' || DECODE(bitand(mflags,64),64,'Y','N') || '",'||
30 '"stale":"' || DECODE(bitand(mflags,16+32),0,'Y','N') || '"},'
31 END ||
32 -- attribute clustering existent
33 CASE WHEN xx.clstobj# IS NOT NULL THEN
34 '"attrcl":{ ' ||
35 -- '"attrcl":"' || DECODE(xx.clstobj#,NULL,'N','Y') ||'",' ||
36 '"clst_type":"' || DECODE(clstfunc,1,'I','L') || '",'||
37 '"clst_mode":"' || DECODE(bitand(xx.cflags,1+2),1+2,'L+M',1,'L',2,'M','OFF') || '",'||
38 '"clst_dim":' || (SELECT COUNT(*) FROM clstdimension$ d
39 WHERE d.clstobj#(+)=xx.clstobj#) || ','||
40 '"clst_grp":' || (SELECT COUNT(DISTINCT groupid) FROM clstkey$ k
41 WHERE k.clstobj#(+)=xx.clstobj#) || ','||
42 '"clst_cols":' || (SELECT COUNT(*) FROM clstkey$ k
43 WHERE k.clstobj#(+)=xx.clstobj#) || ','||
44 '"clst_invalid":"' || DECODE(bitand(xx.flags,4),4,'Y','N') || '"},'
45 END ||
46 '"tab":{' ||
47 CASE
48 WHEN bitand(xx.property,32) = 32 THEN
49 '"parttab":"Y"'
50 -- nonpartitioned tables with existing segments
51 WHEN s.block# IS NOT NULL THEN
52 '"parttab":"N",' ||
53 '"imc_prio":"' ||
54 DECODE(bitand(s.spare1, 4294967296), 4294967296,
55 DECODE(bitand(s.spare1, 34359738368), 34359738368,
56 DECODE(bitand(s.spare1, 3848290697216), 549755813888,'LOW',
57 1099511627776,'MEDIUM',
58 2199023255552,'HIGH',
59 3298534883328,'CRITICAL','NONE'),'NONE'),'-') || '",'||
60 '"imc_dist":"' ||
61 DECODE(bitand(s.spare1, 4294967296), 4294967296,
62 DECODE(bitand(s.spare1, 8589934592),0,'DUPLICATE','AUTO DIST'),'-') || '",'||
63 '"imc_comp":"' ||
64 DECODE(bitand(s.spare1, 4294967296), 4294967296,
65 DECODE(bitand(s.spare1, 274877906944), 0,
66 DECODE(bitand(s.spare1,17179869184),0,'BASIC','QUERY'),
67 DECODE(bitand(s.spare1,17179869184),0,'CAP LOW','CAP HIGH')),'-') ||'"'
68 ELSE
69 '"parttab":"N"'
70 END
71 || '} }' zmap_obj
72 FROM (SELECT *
73 FROM (SELECT sumobj#, clstobj#, detailobj#, mflags, c.flags as cflags, clstfunc, numjoins,
74 numaggregates, numdetailtab, zmapscale, xpflags
75 FROM ( SELECT sumobj#, detailobj#, mflags, numjoins, numaggregates, numdetailtab,
76 zmapscale, xpflags
77 FROM sum$ s
78 RIGHT OUTER JOIN sumdetail$ sd
79 ON (s.obj# = sd.sumobj#)
80 WHERE bitand(s.xpflags,34359738368)=34359738368
81 AND bitand(sd.flags,2) = 2
82 ) zm
83 -- to accommodate either-or for zone maps or attribute clustering
84 FULL OUTER JOIN clst$ c
85 ON (zm.detailobj# = c.clstobj#)
86 ) x
87 JOIN tab$ t
88 ON (t.obj#=DECODE(x.clstobj#,NULL,x.detailobj#,x.clstobj#))
89 ) xx
90 -- needed for deferred segments and partitioned objects
91 LEFT OUTER JOIN seg$ s
92 ON (xx.file# = s.file#
93 AND xx.block# = s.block#
94 AND xx.ts# = s.ts#)) loop
95
96 IF (is_used = 0 AND crec.zmap_used= 'Y') THEN
97 is_used:=1;
98 END IF;
99
100 clob_rest := clob_rest || crec.zmap_obj ||', ';
101 END LOOP;
102
103 clob_rest := substr(clob_rest,1,length(clob_rest)-2) ||' ] }';
104
105 IF (is_used = 1) THEN
106 -- ratio of tables with zone maps versus total # of tables in percent
107 SELECT (COUNT(detailobj#)/COUNT(obj#))*100 INTO zmap_ratio
108 FROM ( SELECT detailobj#
109 FROM sum$ s
110 JOIN sumdetail$ sd
111 ON (s.obj# = sd.sumobj#)
112 WHERE bitand(s.xpflags,34359738368)=34359738368
113 AND bitand(sd.flags,2) = 2
114 ) zm
115 RIGHT OUTER JOIN tab$ t
116 ON (t.obj#=zm.detailobj#);
117 END IF;
118
119 END;