[Home] [Help]
PROCEDURE: SYS.DBMS_FEATURE_PARTITION_SYSTEM
Source
1 PROCEDURE dbms_feature_partition_system
2 (is_used OUT number, data_ratio OUT number, clob_rest OUT clob)
3 AS
4 BEGIN
5 -- initialize
6 is_used := 0;
7 data_ratio := 0;
8 clob_rest := NULL;
9
10 FOR crec IN (select num||':'||idx_or_tab||':'||ptype||':'||subptype||':'||pcnt||':'||subpcnt||':'||
11 pcols||':'||subpcols||':'||idx_flags||':'||
12 idx_type||':'||idx_uk||':'||rpcnt||':'||rsubpcnt||':'|| def_segment_creation||':'||
13 partial_idx||':'||orphaned_entries ||':'||zonemap||':'||attrcluster||':'||
14 subpartemp || '|' my_string
15 from (select dense_rank() over (order by decode(bo#,null,pobj#,bo#)) NUM,
16 idx_or_tab,
17 ptype, pcols, pcnt, rpcnt,
18 subptype, subpcols, subpartemp, subpcnt, rsubpcnt,
19 idx_flags, idx_type, idx_uk, orphaned_entries,
20 def_segment_creation, partial_idx,
21 zonemap, attrcluster
22 from
23 ( select /*+ full(o) */ o.obj#, i.bo#, p.obj# pobj#,
24 decode(o.type#,1,'I',2,'T',null) IDX_OR_TAB,
25 is_xml ||
26 decode(p.parttype, 1, case when bitand(p.flags,64)=64 then
27 -- INTERVAL-REF, 12c
28 case when bitand(p.flags,32)=32 then 'INT-REF'
29 else 'INTERVAL' end
30 else 'RANGE' end
31 ,2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REF'
32 ,p.parttype||'-?') ||
33 decode(bitand(p.flags,32),32,' (PARENT)') PTYPE,
34 decode(mod(p.spare2, 256), 0, null, 1, 'RANGE', 2, 'HASH', 3,'SYSTEM'
35 , 4, 'LIST', 5, 'REF'
36 , p.spare2||'-?') SUBPTYPE,
37 p.partcnt PCNT,
38 mod(trunc(p.spare2/65536), 65536) SUBPCNT,
39 p.partkeycols PCOLS,
40 case mod(trunc(p.spare2/256), 256)
41 when 0 then null
42 else mod(trunc(p.spare2/256), 256) end SUBPCOLS,
43 case when bitand(p.flags,1) = 1 then
44 case when bitand(p.flags,2) = 2 then 'LP'
45 else 'L' end
46 when bitand(p.flags,2) = 2 then 'GP'
47 end IDX_FLAGS,
48 decode(i.type#, 1, 'NORMAL'||
49 decode(bitand(i.property, 4), 0, '', 4, '/REV'),
50 2, 'BITMAP', 3, 'CLUSTER', 4, 'IOT - TOP',
51 5, 'IOT - NESTED', 6, 'SECONDARY', 7, 'ANSI', 8, 'LOB',
52 9, 'DOMAIN') ||
53 case when bitand(i.property,16) = 16
54 then '-FUNC' end IDX_TYPE,
55 decode(i.property, null,null,
56 decode(bitand(i.property, 1), 0, 'NU',
57 1, 'U', '?')) IDX_UK,
58 -- real partition and subpartition count
59 case when bitand(p.flags,64)=64 then op.xnumpart else p.partcnt end RPCNT,
60 osp.numsubpart RSUBPCNT,
61 -- deferred segments
62 case o.type#
63 when 1 then --index
64 decode(ip_seg_off,null,isp_seg_off,ip_seg_off)
65 when 2 then --table
66 decode(tp_seg_off,null,tsp_seg_off,tp_seg_off)
67 else null end DEF_SEGMENT_CREATION,
68 -- partial indexing
69 case o.type#
70 when 1 then --index
71 decode(bitand(i.flags, 8388608), 8388608, 'PARTIAL', 'FULL')||'-'||
72 -- overload field with count of all [sub]partitions with indexing off
73 decode(ip_idx_off,null,isp_idx_off,ip_idx_off)
74 when 2 then --table
75 decode(bitand(p.flags,8192),8192,'OFF','ON')||'-'||
76 -- overload field with count of all [sub]partitions with indexing off
77 decode(tp_idx_off,null,tsp_idx_off,tp_idx_off)
78 else null end PARTIAL_IDX,
79 null ORPHANED_ENTRIES,
80 decode(zonemap,null,'N',zonemap) ZONEMAP,
81 decode(attrcluster,null,'N',attrcluster) ATTRCLUSTER,
82 st_part SUBPARTEMP
83 from partobj$ p, obj$ o, user$ u, ind$ i,
84 ( select distinct obj#, 'XML-' as is_xml from opqtype$ where type=1) xml,
85 -- real subpartition count for tables and indexes
86 ( select /* NO_MERGE FULL(tsp) FULL(tcp) */ tcp.bo#, count(*) numsubpart
87 from tabsubpart$ tsp, tabcompart$ tcp
88 where tcp.obj# = tsp.pobj#
89 group by tcp.bo#
90 union all
91 select /* NO_MERGE FULL(isp) FULL(icp) */ icp.bo#, count(*) numsubpart
92 from indsubpart$ isp, indcompart$ icp
93 where icp.obj# = isp.pobj#
94 group by icp.bo#) osp,
95 -- real partition count for tables and indexes
96 ( select tp.bo#, count(*) xnumpart
97 from tabpart$ tp
98 group by tp.bo#
99 union all
100 select ip.bo#, count(*) xnumpart
101 from indpart$ ip
102 group by ip.bo#) op,
103 -- details table partitions: partial indexing and deferred segments
104 ( select tp.bo#,
105 -- number or partitions with indexing off
106 sum(decode(bitand(tp.flags, 2097152), 2097152, 1, 0)) tp_idx_off,
107 -- number or partitions with deferred segment creation
108 sum(decode(bitand(tp.flags, 65536), 65536, 1, 0)) tp_seg_off
109 from tabpart$ tp
110 group by tp.bo#) pxd,
111 -- details table subpartitions: partial indexing and deferred segments
112 ( select tcp.bo#,
113 -- number or subpartitions with indexing off
114 sum(decode(bitand(tsp.flags, 2097152), 2097152, 1, 0)) tsp_idx_off,
115 -- number or subpartitions with deferred segment creation
116 sum(decode(bitand(tsp.flags, 65536), 65536, 1, 0)) tsp_seg_off
117 from tabsubpart$ tsp, tabcompart$ tcp
118 where tcp.obj# = tsp.pobj#
119 group by tcp.bo#) spxd,
120 -- details index partitions: partial indexing and deferred segments
121 ( select ip.bo#,
122 -- number or partitions with indexing off
123 sum(decode(bitand(ip.flags, 1), 1, 1, 0)) ip_idx_off,
124 -- number or partitions with deferred segment creation
125 sum(decode(bitand(ip.flags, 65536), 65536, 1, 0)) ip_seg_off
126 from indpart$ ip
127 group by ip.bo#) ipd,
128 -- details index subpartitions: partial indexing and deferred segments
129 ( select icp.bo#,
130 -- number or subpartitions with indexing off
131 sum(decode(bitand(isp.flags, 1), 1, 1, 0)) isp_idx_off,
132 -- number or subpartitions with deferred segment creation
133 sum(decode(bitand(isp.flags, 65536), 65536, 1, 0)) isp_seg_off
134 from indsubpart$ isp, indcompart$ icp
135 where icp.obj# = isp.pobj#
136 group by icp.bo#) ispd,
137 -- attribute clustering
138 ( select c.clstobj#, 'Y-'||
139 -- kind of attribute clustering
140 case when ctable is not null
141 then 'MT-' else 'ST-' end
142 ||
143 case when clstfunc = 1 then 'I-' -- interleaved
144 else 'L-' end -- linear
145 ||to_char(decode(ctable, null,0,ctable)+1)||'-'||ccol as ATTRCLUSTER
146 from sys.clst$ c,
147 -- count of tables and columns used for attribute clustering
148 -- no detailed breakdown of columns per row
149 -- table count does not include fact table for hierarchical attr. clustering
150 ( select clstobj#, count(intcol#) ccol
151 from sys.clstkey$
152 group by clstobj#) k,
153 ( select clstobj#, count(*) ctable
154 from sys.clstjoin$
155 group by clstobj#) kt
156 where c.clstobj# = k.clstobj#
157 and c.clstobj# = kt.clstobj#(+)) attrcl,
158 -- zone maps
159 (select detailobj#, zonemap from
160 (select sd.detailobj#, flags, 'Y-'||
161 -- single table zonemap or hierarchical zonemap
162 decode(bitand(sn.flag3, 1024),
163 0, 'ST', 'MT') ||
164 -- number of tables and columns in zonemap (aggr, no detailed breakdown)
165 -- table count does not include fact table for hierarch. zonemap
166 '-'|| count(distinct sd.detailobj#) over (partition by sd.sumobj#) ||
167 '-'|| sa.zmcol as ZONEMAP
168 from sys.sumdetail$ sd, sys.sum$ s, sys.snap$ sn,
169 ( select sumobj#, count(*) zmcol
170 from sys.sumagg$
171 where aggfunction = 18
172 group by sumobj#) sa
173 where s.obj# = sd.sumobj#
174 and s.obj# = sa.sumobj#
175 and s.containernam(+) = sn.vname) v
176 where bitand(v.flags, 2) = 2 /* zonemap fact table */
177 ) zm,
178 ( select bo#, count(*) st_part
179 from defsubpart$
180 group by bo# ) spt
181 where o.obj# = i.obj#(+)
182 and o.owner# = u.user#
183 and p.obj# = o.obj#
184 and p.obj# = xml.obj#(+)
185 and p.obj# = osp.bo#(+)
186 and p.obj# = op.bo#(+)
187 and p.obj# = pxd.bo#(+)
188 and p.obj# = spxd.bo#(+)
189 and p.obj# = ipd.bo#(+)
190 and p.obj# = ispd.bo#(+)
191 and p.obj# = spt.bo#(+)
192 and o.obj# = attrcl.clstobj#(+)
193 and o.obj# = zm.detailobj#(+)
194 -- fix bug 3074607 - filter on obj$
195 and o.type# in (1,2,19,20,25,34,35)
196 union all
197 -- global nonpartitioned indexes on partitioned tables
198 select o.obj#, i.bo#, p.obj# pobj#,
199 'I' IDX_OR_TAB,
200 null,null,null,null,
201 case cols when 0 then null
202 else cols end PCOLS,null,
203 'GNP' IDX_FLAGS,
204 decode(i.type#, 1, 'NORMAL'||
205 decode(bitand(i.property, 4), 0, '', 4, '/REV'),
206 2, 'BITMAP', 3, 'CLUSTER', 4, 'IOT - TOP',
207 5, 'IOT - NESTED', 6, 'SECONDARY', 7, 'ANSI', 8, 'LOB',
208 9, 'DOMAIN') ||
209 case when bitand(i.property,16) = 16 then '-FUNC' end IDX_TYPE,
210 decode(i.property, null,null,
211 decode(bitand(i.property, 1), 0, 'NU',
212 1, 'U', '?')) IDX_UK,
213 null, null,
214 null DEF_SEGMENT_CREATION,
215 -- decode(bitand(p.flags,6144),4096,'YES',2048,'NO','NONE') DEF_SEGMENT_CREATION,
216 decode(bitand(i.flags, 8388608), 8388608, 'PARTIAL', 'FULL') PARTIAL_IDX,
217 decode(bitand(i.flags, 268435456), 268435456, 'YES', 'NO') ORPHANED_ENTRIES,
218 NULL ZONEMAP, NULL ATTRCLUSTER,
219 NULL SUBPARTEMP
220 from partobj$ p, user$ u, obj$ o, ind$ i
221 where p.obj# = i.bo#
222 and o.owner# = u.user#
223 and p.obj# = o.obj#
224 -- nonpartitioned index
225 and bitand(i.property, 2) <>2 )
226 order by num, idx_or_tab desc
227 )) LOOP
228
229 if (is_used = 0) then
230 is_used:=1;
231 end if;
232
233 clob_rest := clob_rest||crec.my_string;
234 end loop;
235
236 if (is_used = 1) then
237 select pcnt into data_ratio
238 from
239 (
240 SELECT c1, TRUNC((ratio_to_report(sum_blocks) over())*100,2) pcnt
241 FROM
242 (
243 select decode(p.obj#,null,'REST','PARTTAB') c1, sum(s.blocks) sum_blocks
244 from tabpart$ p, seg$ s
245 where s.file#=p.file#(+)
246 and s.block#=p.block#(+)
247 and s.type#=5
248 group by decode(p.obj#,null,'REST','PARTTAB')
249 )
250 )
251 where c1 = 'PARTTAB';
252 end if;
253 end;