[Home] [Help]
PACKAGE BODY: OLAPSYS.OLAPFACTVIEW
Source
1 PACKAGE BODY OlapFactView AS
2
3 PROCEDURE CreateFactTab(exOwnerName IN varchar2,exCubeName IN varchar2,eOutFile IN varchar2,eOutPath IN varchar2,partitioning IN BOOLEAN,mvspcName IN varchar2 default null,indspcName IN varchar2 default null) IS
4
5 -- This it the Procedure to Create Cube MV in ROLLUP form for a Cube. This procedure is used to write to a file
6 -- To run it do the following
7
8
9 filehandle UTL_FILE.FILE_TYPE;
10 eOwnerName varchar2(30);
11 eCubeName varchar2(30);
12 eMVName varchar2(30);
13 -- eOutPath varchar2(30);
14 -- eOutFile varchar2(30);
15 eDimName varchar2(30);
16 eHierName varchar2(30);
17 eLevelName varchar2(30);
18 eLevelColName varchar2(30);
19 eLeavColName varchar2(30);
20 eFactName varchar2(30);
21 eUnitName varchar2(30);
22 ePosition NUMBER;
23 eTempMV varchar2(30);
24 eTabName varchar2(30);
25 eTableName varchar2(30);
26 eparLevelName varchar2(100);
27 parTabName varchar2(30);
28 parColName varchar2(30);
29 dimCount INTEGER;
30 i INTEGER;
31 j INTEGER;
32 k INTEGER;
33 m INTEGER;
34 n INTEGER;
35 o INTEGER;
36 z INTEGER;
37 c NUMBER;
38 flag INTEGER;
39 numOfMv NUMBER;
40 numOfdimMV NUMBER;
41 tempMVnum INTEGER;
42 highLvlCount INTEGER;
43 highLvlNum INTEGER;
44 tableCount INTEGER;
45 tempName varchar2(30);
46 bufString varchar2(32767);
47 indexString varchar2(32767);
48 parentString varchar2(300);
49 childString varchar2(300);
50 mvString varchar2(32767);
51 mvnum INTEGER;
52 num_rows NUMBER;
53 num_bytes NUMBER;
54 partgid NUMBER;
55 partnum NUMBER;
56 tempnum NUMBER;
57 tpnum NUMBER;
58 val NUMBER;
59 tkval NUMBER;
60 times NUMBER;
61 ntimes NUMBER;
62 loopval NUMBER;
63 tempval NUMBER;
64 tpval NUMBER;
65 query_length NUMBER;
66 whereFlag BOOLEAN;
67 dupmvFlag BOOLEAN;
68 dupdimmvFlag BOOLEAN;
69 others EXCEPTION;
70 partition BOOLEAN;
71 poscount NUMBER;
72
73 TYPE dimType IS RECORD
74 (dimName varchar2(30),
75 hierCount INTEGER);
76
77 TYPE dimHiertype IS RECORD
78 (dimName varchar2(30),
79 hierName varchar2(30),
80 hierNum INTEGER,
81 dimNum INTEGER,
82 levelCount INTEGER
83 );
84
85 TYPE dimhierList_t is TABLE of dimHiertype;
86
87
88
89 -- TYPE levelName_t IS TABLE OF VARCHAR2(30)
90 -- INDEX BY BINARY_INTEGER;
91
92 -- TYPE levelPos_t IS TABLE OF VARCHAR2(30)
93 -- INDEX BY BINARY_INTEGER;
94
95 TYPE hierLeveltype is RECORD
96 (dimName varchar2(30),
97 hierName varchar2(30),
98 levelName varchar2(30),
99 levelPos varchar2(30),
100 levelNum INTEGER
101 );
102
103 TYPE hierLevelList_t is TABLE of hierLeveltype;
104
105
106
107 TYPE mv_dimHier IS RECORD
108 (mvNum INTEGER,
109 dimName varchar2(30),
110 hierName varchar2(30),
111 lvlCount INTEGER,
112 highestLvl BOOLEAN
113 );
114
115
116 TYPE mvtablename IS RECORD
117 (ownerName varchar2(30),
118 tabName varchar2(30)
119 );
120
121 TYPE parnumtable is RECORD
122 (gidNum NUMBER,
123 ownerName varchar2(30)
124 );
125
126
127 TYPE parnumtable_t is TABLE of parnumtable;
128
129 TYPE mvtable_t is TABLE of mvtablename;
130
131
132 TYPE mvtype_t IS TABLE of mv_dimHier;
133
134 TYPE dimList_t IS TABLE OF dimType;
135 -- TYPE dimList IS TABLE OF ALL_OLAP_FACT_LEVEL_USES.DIMENSION_NAME%TYPE
136
137 TYPE levList_t IS TABLE OF ALL_OLAP_DIM_LEVELS.LEVEL_NAME%TYPE;
138
139 TYPE hierarchyList_t IS TABLE OF ALL_OLAP_DIM_HIERARCHIES.DIMENSION_NAME%TYPE;
140
141 TYPE colLvlList_t IS TABLE OF ALL_OLAP_LEVEL_KEY_COLUMN_USES.COLUMN_NAME%TYPE;
142
143
144
145 CURSOR curDimName IS SELECT DIMENSION_NAME
146 FROM ALL_OLAP_FACT_LEVEL_USES WHERE FACT_TABLE_NAME= eFactName AND
147 OWNER= eOwnerName AND CUBE_NAME= eCubeName;
148
149 CURSOR curLevelName IS SELECT V.L
150 FROM ( SELECT CHILD_LEVEL_NAME L, POSITION P
151 FROM ALL_OLAP_DIM_HIER_LEVEL_USES
152 WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
153 AND HIERARCHY_NAME= eHierName
154 UNION
155 SELECT PARENT_LEVEL_NAME L, POSITION+1
156 FROM ALL_OLAP_DIM_HIER_LEVEL_USES
157 WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
158 AND HIERARCHY_NAME= eHierName) V
159 ORDER BY V.P DESC;
160
161 CURSOR curHierarchyName IS SELECT HIERARCHY_NAME
162 FROM ALL_OLAP_DIM_HIERARCHIES WHERE
163 OWNER= eOwnerName AND
164 DIMENSION_NAME= eDimName;
165
166 CURSOR curColName IS SELECT COLUMN_NAME, POSITION
167 FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
168 WHERE OWNER= eOwnerName AND LEVEL_NAME= eLevelName
169 AND DIMENSION_NAME= eDimName ;
170
171 CURSOR curTabName IS SELECT LEVEL_TABLE_NAME
172 FROM ALL_OLAP_DIM_LEVELS
173 WHERE OWNER= eOwnerName AND LEVEL_NAME= eLevelName
174 AND DIMENSION_NAME= eDimName;
175
176 CURSOR curParentName IS SELECT TABLE_NAME || '.' || COLUMN_NAME, TABLE_NAME
177 FROM ALL_OLAP_JOIN_KEY_COLUMN_USES
178 WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
179 AND HIERARCHY_NAME= eHierName
180 AND CHILD_LEVEL_NAME= eLevelName
181 AND POSITION = ePosition;
182
183
184 CURSOR curLeavName IS SELECT distinct a.column_name
185 FROM ALL_OLAP_KEY_COLUMN_USES a, ALL_OLAP_FOREIGN_KEYS b,
186 ALL_OLAP_FACT_LEVEL_USES c
187 where a.table_name = b.table_name
188 and a.position = ePosition
189 and a.key_name = b.foreign_key_name
190 and b.table_name = c.fact_table_name
191 and b.foreign_key_name = c.foreign_key_name
192 and c.dimension_name = eDimName
193 and c.level_name = eLevelName
194 and c.dimension_owner = eOwnerName
195 and c.fact_table_owner = eOwnerName
196 and c.fact_table_name = eFactName;
197
198 CURSOR curFactName IS SELECT distinct c.fact_table_name
199 from ALL_OLAP_CUBES a, ALL_OLAP_CUBE_MEASURES b, ALL_OLAP_CUBE_MEASURE_MAPS c
200 where a.owner = b.owner
201 and a.cube_name = b.cube_name
202 and b.owner = c.owner
203 and b.cube_name = c.cube_name
204 and b.measure_name = c.measure_name
205 and a.owner = eOwnerName
206 and a.cube_name = eCubeName;
207
208 CURSOR curUnitName IS SELECT distinct c.column_name
209 from ALL_OLAP_CUBES a, ALL_OLAP_CUBE_MEASURES b, ALL_OLAP_CUBE_MEASURE_MAPS c
210 where a.owner = b.owner
211 and a.cube_name = b.cube_name
212 and b.owner = c.owner
213 and b.cube_name = c.cube_name
214 and b.measure_name = c.measure_name
215 and a.owner = eOwnerName
216 and a.cube_name = eCubeName;
217
218 CURSOR checkName IS SELECT QUERY from ALL_MVIEWS
219 where owner= eOwnerName
220 and QUERY_LEN= query_length;
221
222
223 CURSOR curLevDimName IS SELECT LEVEL_NAME
224 FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
225 WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName;
226
227 CURSOR curColDimName IS SELECT COLUMN_NAME, POSITION
228 FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
229 WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName;
230
231 dimHierList dimhierList_t:= dimhierList_t();
232 hierLevelList hierLevelList_t:= hierLevelList_t();
233 dimList dimList_t:= dimList_t();
234 levList levList_t:= levList_t();
235 hierarchyList hierarchyList_t:= hierarchyList_t();
236 colLvlList colLvlList_t:= colLvlList_t();
237 partList dimhierList_t:= dimhierList_t();
238 mvtableList mvtable_t:= mvtable_t();
239 parnum parnumtable_t := parnumtable_t();
240
241
242 mvList mvtype_t:= mvtype_t();
243 mvListinc mvtype_t:= mvtype_t();
244 mvListdec mvtype_t:= mvtype_t();
245 mvdimList mvtype_t:= mvtype_t();
246
247
248 BEGIN
249 -- CREATE TABLE SYSTEM.MVIEW$_ADV_INDEX (RUNID# NUMBER NOT NULL,
250 -- RANK# NUMBER NOT NULL,
251 -- SUMMARY_OWNER VARCHAR2(32),
252 -- INDEX_TEXT LONG);
253 numOfMV:= 1;
254 numOfdimMV:= 1;
255 -- eOutPath:= '/users/oracle';
256 cwm2_olap_metadata_refresh.mr_refresh;
257 eOwnerName:= UPPER(exOwnerName);
258 eCubeName:= UPPER(exCubeName);
259 IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN
260 filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');
261 END IF;
262 IF NOT curFactName%ISOPEN THEN
263 OPEN curFactName;
264 LOOP
265 FETCH curFactName INTO eFactName;
266 EXIT WHEN curFactName%NOTFOUND;
267 END LOOP;
268 CLOSE CurFactName;
269 END IF;
270 IF eFactName IS NULL THEN
271 RAISE UTL_FILE.INTERNAL_ERROR;
272 END IF;
273 IF NOT curFactName%ISOPEN THEN
274 OPEN curFactName;
275 LOOP
276 FETCH curFactName INTO eFactName;
277 EXIT WHEN curFactName%NOTFOUND;
278 END LOOP;
279 CLOSE CurFactName;
280 END IF;
281
282 IF NOT curDimName%ISOPEN THEN
283 OPEN curDimName;
284 i:= 0;
285 LOOP
286 FETCH curDimName INTO tempName;
287 EXIT WHEN curDimName%NOTFOUND;
288 dimList.EXTEND;
289
290 i:= i + 1;
291 dimList(i).dimName:= tempName;
292 -- dimList(i):= i;
293 END LOOP;
294 CLOSE CurDimName;
295 END IF;
296 dimCount:= dimList.Count;
297 j:= 0;
298 FOR i in 1..dimList.Count LOOP
299 eDimName:= dimList(i).dimName;
300 k:= 0;
301 IF NOT curHierarchyName%ISOPEN THEN
302 OPEN curHierarchyName;
303 END IF;
304 LOOP
305 FETCH curHierarchyName into tempName;
306 EXIT WHEN curHierarchyName%NOTFOUND;
307 j:= j + 1;
308 k:= k + 1;
309 dimHierList.EXTEND;
310 dimHierList(j).dimName := eDimName;
311 dimHierList(j).dimNum := i;
312 dimHierList(j).hierName:= tempName;
313 dimHierList(j).hierNum:= k;
314 END LOOP;
315 CLOSE curHierarchyName;
316 IF k = 0 THEN
317 j:= j + 1;
318 k:= k + 1;
319 dimHierList.EXTEND;
320 dimHierList(j).dimName := eDimName;
321 dimHierList(j).dimNum := i;
322 dimHierList(j).hierName:= NULL;
323 dimHierList(j).hierNum:= k;
324 END IF;
325 dimList(i).hierCount:= k;
326 END LOOP;
327 j:= 0;
328 FOR i in 1..dimHierList.Count LOOP
329 eDimName:= dimHierList(i).dimName;
330 eHierName:= dimHierList(i).hierName;
331 k:= 0;
332 IF NOT curLevelName%ISOPEN THEN
333 OPEN curLevelName;
334 END IF;
335 LOOP
336 FETCH curLevelName into tempName;
337 EXIT WHEN curLevelName%NOTFOUND;
338 k:= k + 1;
339 j:= j + 1;
340 hierLevelList.EXTEND;
341 hierLevelList(j).dimName:= eDimName;
342 hierLevelList(j).hierName:= eHierName;
343 hierLevelList(j).levelName:= tempName;
344 hierLevelList(j).levelNum:= k;
345 -- hierLevelList(j).levelPos.EXTEND;
346 -- heirLevelList(j).levelName(k):= tempName;
347 END LOOP;
348 CLOSE curLevelName;
349 IF k= 0 THEN
350 IF NOT curLevDimName%ISOPEN THEN
351 OPEN curLevDimName;
352 END IF;
353 LOOP
354 FETCH curLevDimName into tempName;
355 EXIT WHEN curLevDimName%NOTFOUND;
356 k:= k + 1;
357 j:= j + 1;
358 hierLevelList.EXTEND;
359 hierLevelList(j).dimName:= eDimName;
360 hierLevelList(j).hierName:= NULL;
364 CLOSE curLevDimName;
361 hierLevelList(j).levelName:= tempName;
362 hierLevelList(j).levelNum:= k;
363 END LOOP;
365 END IF;
366 dimHierList(i).levelCount:= k;
367
368 END LOOP;
369
370 numOfMV:= 1;
371 FOR i in 1..dimList.Count LOOP
372 numOfMV:= numOfMV * dimList(i).hierCount;
373 END LOOP;
374
375 m:= 0;
376 n:= 0;
377 flag:= 1;
378 FOR i in 1..dimList.Count LOOP
379 tempMVnum:= numOfMV / dimList(i).hierCount;
380 eDimName:= dimList(i).dimName;
381 n:= 0;
382 IF flag= 1 THEN
383 FOR j in 1..dimList(i).hierCount LOOP
384 FOR l in 1..dimHierList.Count LOOP
385 IF dimHierList(l).dimName= eDimName AND
386 dimHierList(l).hierNum= j THEN
387 FOR k in 1..tempMVnum LOOP
388 eHierName:= dimHierList(l).hierName;
389 m:= m + 1;
390 n:= n + 1;
391 flag:= 0;
392 mvList.EXTEND;
393 mvList(m).mvNum:= n;
394 mvList(m).dimName:= eDimName;
395 mvList(m).hierName:= eHierName;
396 END LOOP;
397 END IF;
398 END LOOP;
399 END LOOP;
400 ELSE
401 FOR k in 1..tempMVnum LOOP
402 FOR j in 1..dimList(i).hierCount LOOP
403 FOR l in 1..dimHierList.Count LOOP
404 IF dimHierList(l).dimName= eDimName AND
405 dimHierList(l).hierNum= j THEN
406 eHierName:= dimHierList(l).hierName;
407 m:= m + 1;
408 n:= n + 1;
409 flag:= 1;
410 mvList.EXTEND;
411 mvList(m).mvNum:= n;
412 mvList(m).dimName:= eDimName;
413 mvList(m).hierName:= eHierName;
414 END IF;
415 END LOOP;
416 END LOOP;
417 END LOOP;
418 END IF;
419 END LOOP;
420 FOR i in 1..mvList.Count LOOP
421 eDimName:= mvList(i).dimName;
422 eHierName:= mvList(i).hierName;
423 FOR j in 1..dimHierList.Count LOOP
424 IF eDimName = dimHierList(j).dimName THEN
425 IF (eHierName = dimHierList(j).hierName OR
426 dimHierList(j).hierName IS NULL ) THEN
427 mvList(i).lvlCount:= dimHierList(j).levelCount;
428 mvList(i).highestLvl:= FALSE;
429 END IF;
430 END IF;
431 END LOOP;
432 END LOOP;
433 FOR i in 1..numOfMV LOOP
434 highLvlCount:= 0;
435 highLvlNum:= 1;
436 FOR j in 1..mvList.Count LOOP
437 IF mvList(j).mvNum = i AND
438 mvList(j).lvlCount >= highLvlCount THEN
439 highLvlCount:= mvList(j).lvlCount;
440 highLvlNum:= j;
441 END IF;
442 END LOOP;
443 mvList(highLvlNum).highestLvl:= TRUE;
444 END LOOP;
445
446 m:= 0;
447 -- highLvlCount:= 0;
448 FOR i in 1..numOfMV LOOP
449 j:= 1;
450 FOR j in 1..mvList.Count LOOP
451 IF mvList(j).mvNum = i AND
452 mvList(j).highestLvl= TRUE THEN
453 highLvlCount:= mvList(j).LvlCount;
454 -- EXIT LOOP;
455 END IF;
456 END LOOP;
457 -- END LOOP;
458 -- WHILE j <= mvList.Count AND
459 -- mvList(j).mvNum= i
460 -- LOOP
461 -- highLvlCount:= mvList(j).LvlCount;
462 -- j:= j+ 1;
463 -- EXIT WHEN mvList(j).highestLvl = TRUE;
464 -- END LOOP;
465 -- FOR j in 1..mvList.Count LOOP
466 -- IF mvList(j).mvNum= i AND
467 -- mvList(j).highestLvl= TRUE THEN
468 -- highLvlNum:= mvList(j).lvlCount;
469 -- EXIT;
470 -- END IF;
471 -- END LOOP;
472 FOR k in REVERSE 0..highLvlCount LOOP
473 FOR j in 1..mvList.Count LOOP
474 IF mvList(j).mvNum = i AND
475 mvList(j).lvlCount = k THEN
476 m:= m + 1;
477 mvListdec.EXTEND;
478 mvListdec(m).mvNum:= i;
479 mvListdec(m).dimName:= mvList(j).dimName;
480 mvListdec(m).hierName:= mvList(j).hierName;
481 mvListdec(m).lvlCount:= mvList(j).lvlCount;
482 mvListdec(m).highestLvl:= mvList(j).highestLvl;
483 END IF;
484 END LOOP;
485 END LOOP;
486 END LOOP;
487
488
489 FOR i in 1..mvListdec.Count LOOP
490 mvListdec(i).highestLvl:= FALSE;
491 END LOOP;
492
493 FOR i in 1..numOfMV LOOP
494 highLvlCount:= 0;
495 highLvlNum:= 1;
496 FOR j in 1..mvListdec.Count LOOP
497 IF mvListdec(j).mvNum = i THEN
498 IF highLvlCount = 0 THEN
499 highLvlCount:= mvListdec(j).lvlCount;
500 END IF;
501 IF mvListdec(j).lvlCount <= highLvlCount THEN
502 highLvlCount:= mvListdec(j).lvlCount;
503 highLvlNum:= j;
504 END IF;
505 END IF;
506 END LOOP;
507 mvListdec(highLvlNum).highestLvl:= TRUE;
508 END LOOP;
509 m:= 0;
510 FOR i in 1..numOfMV LOOP
511 j:= 1;
512 FOR j in 1..mvList.Count LOOP
513 IF mvList(j).mvNum = i AND
514 mvList(j).highestLvl= TRUE THEN
515 highLvlCount:= mvList(j).LvlCount;
516 -- EXIT LOOP;
517 END IF;
518 END LOOP;
519 -- WHILE j <= mvList.Count AND
520 -- mvList(j).mvNum= i AND
521 -- mvList(j).highestLvl= TRUE
522 -- LOOP
523 -- highLvlCount:= mvList(j).LvlCount;
524 -- j:= j+ 1;
525 -- END LOOP;
526 -- FOR j in 1..mvList.Count LOOP
527 -- IF mvList(j).mvNum= i AND
531 -- END IF;
528 -- mvList(j).highestLvl= TRUE THEN
529 -- highLvlNum:= mvList(j).lvlCount;
530 -- EXIT;
532 -- END LOOP;
533 FOR k in 0..highLvlCount LOOP
534 FOR j in 1..mvList.Count LOOP
535 IF mvList(j).mvNum = i AND
536 mvList(j).lvlCount = k THEN
537 m:= m + 1;
538 mvListinc.EXTEND;
539 mvListinc(m).mvNum:= i;
540 mvListinc(m).dimName:= mvList(j).dimName;
541 mvListinc(m).hierName:= mvList(j).hierName;
542 mvListinc(m).lvlCount:= mvList(j).lvlCount;
543 mvListinc(m).highestLvl:= mvList(j).highestLvl;
544 END IF;
545 END LOOP;
546 END LOOP;
547 END LOOP;
548 -- INSERT INTO SYSTEM.MVIEW$_ADV_LOG(RUNID#,STATUS) VALUES
549 -- (runid,1);
550 FOR i in 1..numofMV LOOP
551 --UTL_PUT(filehandle,buffer)
552 eMVName:= SUBSTR(exCubeName,0,20) || '_' || i || '_OLAP';
553 UTL_FILE.PUT_LINE(filehandle,'create materialized view');
554 UTL_FILE.PUT(filehandle,eMVName);
555 UTL_FILE.NEW_LINE(filehandle,1);
556 -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
557 -- UTL_FILE.NEW_LINE(filehandle,1);
558 -- UTL_FILE.PUT_LINE(filehandle,'parallel');
559
560 dupmvFlag:= FALSE;
561 partition:= TRUE;
562 IF partitioning = TRUE THEN
563 partgid:= 0;
564 partnum:= 0;
565 o:= 0;
566 indexString:= '';
567 indexString:= indexString || 'partition by range (gid) (';
568 UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');
569 FOR j in 1..mvListdec.Count LOOP
570 IF mvListdec(j).mvNum= i THEN
571 edimName:= mvListdec(j).dimName;
572 ehierName:= mvListdec(j).hierName;
573 FOR l in 1..dimHierList.Count LOOP
574 IF eDimName = dimHierList(l).dimName AND
575 eHierName = dimHierList(l).hierName THEN
576 o:= o + 1;
577 partList.EXTEND;
578 partList(o).dimName:= dimHierList(l).dimName;
579 partList(o).hierName:= dimHierList(l).hierName;
580 partList(o).dimNum:= dimHierList(l).dimNum;
581 partList(o).hierNum:= dimHierList(l).hierNum;
582 partList(o).levelCount:= dimHierList(l).levelCount;
583 ELSIF eDimName = dimHierList(l).dimName AND
584 dimHierList(l).hierName IS NULL THEN
585 o:= o + 1;
586 partList.EXTEND;
587 partList(o).dimName:= dimHierList(l).dimName;
588 partList(o).hierName:= dimHierList(l).hierName;
589 partList(o).dimNum:= dimHierList(l).dimNum;
590 partList(o).hierNum:= dimHierList(l).hierNum;
591 partList(o).levelCount:= dimHierList(l).levelCount;
592
593 END IF;
594 END LOOP;
595 END IF;
596 END LOOP;
597 times:= 1;
598 FOR k in 1..partList.Count LOOP
599 times:= times * partList(k).levelCount;
600 END LOOP;
601
602 FOR k in 1..times LOOP
603 parnum.EXTEND;
604 parnum(k).gidNum:= 0;
605 END LOOP;
606
607 val:= 0;
608 ntimes:= 1;
609 loopval:= 1;
610 tkval:= 0;
611 tpnum:= 0;
612 FOR k in 1..partList.Count LOOP
613 c:= 0;
614 tempMVNum:= times/ partList(k).levelCount;
615 IF k= 1 THEN
616 FOR z in 1..tempMVNum LOOP
617 FOR m in 1..partList(k).levelCount LOOP
618 c:= c + 1;
619 parnum(c).gidNum:= val;
620 IF z <> tempMVNum THEN
621 IF m= partList(k).levelCount THEN
622 val:= 0;
623 ELSE
624 IF val= 0 THEN
625 val:= 1;
626 ELSE
627 val:= (val * 2) + 1;
628 -- tkval:= m - 1;
629 -- tpnum:= tkval * 2;
630 -- val:= val + tpnum;
631 END IF;
632 END IF;
633 ELSE
634 IF m = partList(k).levelCount THEN
635 tpnum:= val + 1;
636 val:= tpnum * 2;
637 tkval:= val;
638 ELSE
639 IF val= 0 THEN
640 val:= 1;
641 ELSE
642 -- tkval:= m - 1;
643 -- tpnum:= tkval * 2;
644 -- val:= val + tpnum;
645 val:= (val * 2) + 1;
646 tkval:= val;
647 END IF;
648 END IF;
649 END IF;
650 END LOOP;
651 END LOOP;
652 loopval:= loopval * partList(k).levelCount;
653 ELSE
654 ntimes:= 1;
655 ntimes:= tempMVNum / loopval;
656 val:= 0;
657 -- tkval:= 0;
658 FOR i in 1..ntimes LOOP
659 FOR j in 1..partList(k).levelCount LOOP
660 tempval:= loopval / partList(1).levelCount;
661 FOR v in 1..tempval LOOP
662 FOR h in 1..partList(1).levelCount LOOP
663 c:= c + 1;
664 parnum(c).gidNum:= parnum(c).gidNum + val;
665 END LOOP;
666 END LOOP;
667 IF j = partList(k).levelCount THEN
668 IF i= ntimes THEN
669 tkval:= tpnum * 4;
670 val:= 0;
671 ELSE
672 tpnum:= tkval;
673 val:= 0;
674 END IF;
675 ELSE
676 IF val= 0 THEN
677 tpnum:= tkval;
678 val:= val + tpnum;
679 ELSE
680 tpnum:= tpnum * 2;
681 val:= val + tpnum;
682 END IF;
683 END IF;
684 END LOOP;
685 END LOOP;
686 loopval:= loopval * partList(k).levelCount;
687 END IF;
688 END LOOP;
692 IF k < 800 THEN
689
690 FOR k in 1..times LOOP
691 IF k > 1 THEN
693 indexString:= indexString || ' partition values less than(' || parnum(k).gidNum || ')' || ' ,';
694
695 END IF;
696 UTL_FILE.PUTF(filehandle,'partition values less than(%s),',parnum(k).gidNum);
697 UTL_FILE.NEW_LINE(filehandle,1);
698 END IF;
699 END LOOP;
700 indexString:= indexString || ' partition values less than(MAXVALUE))';
701 UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
702 partList.DELETE;
703 parnum.DELETE;
704 END IF;
705 UTL_FILE.PUT_LINE(filehandle,'pctfree 5 pctused 40');
706 IF mvspcName IS NOT NULL THEN
707 UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);
708 UTL_FILE.NEW_LINE(filehandle,1);
709 END IF;
710 UTL_FILE.PUT_LINE(filehandle,'build immediate');
711 UTL_FILE.PUT_LINE(filehandle,'using no index');
712 UTL_FILE.PUT_LINE(filehandle,'refresh force');
713 UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
714 UTL_FILE.PUT_LINE(filehandle,'AS');
715 UTL_FILE.PUT_LINE(filehandle,'SELECT');
716 bufString:= bufString || 'SELECT ';
717
718 UTL_FILE.PUT(filehandle,'GROUPING_ID(');
719 bufString:= bufString || 'GROUPING_ID( ';
720 FOR j in 1..mvListinc.Count LOOP
721 IF mvListinc(j).mvNum= i THEN
722 edimName:= mvListinc(j).dimName;
723 ehierName:= mvListinc(j).hierName;
724 FOR l in 1..hierLevelList.Count LOOP
725 IF hierLevelList(l).dimName= eDimName AND
726 hierLevelList(l).hierName= eHierName THEN
727 eLevelName:= hierLevelList(l).levelName;
728 IF NOT curColName%ISOPEN THEN
729 OPEN curColName;
730 END IF;
731 LOOP
732 FETCH curColName into eLevelColName,ePosition;
733 EXIT WHEN curColName%NOTFOUND;
734 END LOOP;
735 CLOSE curColName;
736 IF NOT curTabName%ISOPEN THEN
737 OPEN curTabName;
738 END IF;
739 LOOP
740 FETCH curTabName into eTabName;
741 EXIT WHEN curTabName%NOTFOUND;
742 END LOOP;
743 CLOSE curTabName;
744 UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
745 --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';
746
747 bufString:= bufString
748 || dbms_assert.enquote_name(eTabName)
749 || '.'
750 || dbms_assert.enquote_name(eLevelColName)
751 || ' ';
752
753 IF hierLevelList(l).levelNum < mvListinc(j).lvlCount THEN
754 UTL_FILE.PUT_LINE(filehandle,',');
755 bufString:= bufString || ', ';
756 END IF;
757
758 ELSIF hierLevelList(l).dimName = eDimName AND
759 hierLevelList(l).hierName IS NULL THEN
760 eLevelName:= hierLevelList(l).levelName;
761 IF NOT curColDimName%ISOPEN THEN
762 OPEN curColDimName;
763 END IF;
764 LOOP
765 FETCH curColDimName into eLevelColName,ePosition ;
766 EXIT WHEN curColDimName%NOTFOUND;
767 END LOOP;
768 CLOSE curColDimName;
769 IF NOT curTabName%ISOPEN THEN
770 OPEN curTabName;
771 END IF;
772 LOOP
773 FETCH curTabName into eTabName;
774 EXIT WHEN curTabName%NOTFOUND;
775 END LOOP;
776 CLOSE curTabName;
777 UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
778 --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';
779
780 bufString:= bufString
781 || dbms_assert.enquote_name(eTabName)
782 || '.'
783 || dbms_assert.enquote_name(eLevelColName)
784 || ' ';
785
786 IF hierLevelList(l).levelNum < mvListinc(j).lvlCount THEN
787 UTL_FILE.PUT_LINE(filehandle,',');
788 bufString:= bufString || ', ';
789 END IF;
790 END IF;
791 END LOOP;
792 IF mvListinc(j).highestLvl = FALSE THEN
793 UTL_FILE.PUT_LINE(filehandle,',');
794 bufString:= bufString || ', ';
795 END IF;
796 END IF;
797 END LOOP;
798 UTL_FILE.PUT_LINE(filehandle,') gid,');
799 bufString:= bufString || ') gid, ';
800 IF NOT curUnitName%ISOPEN THEN
801 OPEN curUnitName;
802 LOOP
803 FETCH curUnitName INTO eUnitName;
804 EXIT WHEN curUnitName%NOTFOUND;
805 UTL_FILE.PUTF(filehandle,'SUM(%s.%s) SUM_OF_%s,', eFactName,eUnitName,eUnitName);
806 UTL_FILE.NEW_LINE(filehandle,1);
807 -- UTL_FILE.PUTF(filehandle,'COUNT(%s.%s) COUNT_OF_%s,', eFactName,eUnitName,eUnitName);
808 -- UTL_FILE.NEW_LINE(filehandle,1);
809 --Bug 8915150 bufString:= bufString || 'SUM(' || eFactName || '.' || eUnitName || ') ';
810
811 bufString:= bufString
812 || 'SUM('
813 || dbms_assert.enquote_name(eFactName)
814 || '.'
815 || dbms_assert.enquote_name(eUnitName)
816 || ') ';
817
818 --Bug 8915150 bufString:= bufString || 'SUM_OF_' || eUnitName || ', ';
819
820 bufString:= bufString
824
821 || 'SUM_OF_'
822 || dbms_assert.enquote_name(eUnitName)
823 || ', ';
825 -- bufString:= bufString || 'COUNT(' || eFactName || '.' || eUnitName || ') ';
826 -- bufString:= bufString || 'COUNT_OF_' || eUnitName || ', ';
827 END LOOP;
828 CLOSE CurUnitName;
829 END IF;
830 UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_OF_STAR,');
831 bufString:= bufString || 'COUNT(*) COUNT_OF_STAR, ';
832
833 FOR j in 1..mvListdec.Count LOOP
834 IF mvListdec(j).mvNum= i THEN
835 edimName:= mvListdec(j).dimName;
836 ehierName:= mvListdec(j).hierName;
837 FOR l in 1..hierLevelList.Count LOOP
838 IF hierLevelList(l).dimName= eDimName AND
839 hierLevelList(l).hierName= ehierName THEN
840 eLevelName:= hierLevelList(l).levelName;
841 IF NOT curColName%ISOPEN THEN
842 OPEN curColName;
843 END IF;
844 LOOP
845 FETCH curColName into eLevelColName,ePosition ;
846 EXIT WHEN curColName%NOTFOUND;
847 END LOOP;
848 CLOSE curColName;
849 IF NOT curTabName%ISOPEN THEN
850 OPEN curTabName;
851 END IF;
852 LOOP
853 FETCH curTabName into eTabName;
854 EXIT WHEN curTabName%NOTFOUND;
855 END LOOP;
856 CLOSE curTabName;
857 UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);
858 -- UTL_FILE.NEW_LINE(filehandle,1);
859 --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;
860
861 bufString:= bufString
862 || dbms_assert.enquote_name(eTabName)
863 || '.'
864 || dbms_assert.enquote_name(eLevelColName)
865 || ' '
866 || dbms_assert.enquote_name(eLevelColName);
867
868 IF hierLevelList(l).levelNum < mvListdec(j).lvlCount THEN
869 UTL_FILE.PUT_LINE(filehandle,',');
870 bufString:= bufString || ', ';
871 END IF;
872 ELSIF hierLevelList(l).dimName = eDimName AND
873 hierLevelList(l).hierName IS NULL THEN
874 eLevelName:= hierLevelList(l).levelName;
875 IF NOT curColDimName%ISOPEN THEN
876 OPEN curColDimName;
877 END IF;
878 LOOP
879 FETCH curColDimName into eLevelColName,ePosition ;
880 EXIT WHEN curColDimName%NOTFOUND;
881 END LOOP;
882 CLOSE curColDimName;
883 IF NOT curTabName%ISOPEN THEN
884 OPEN curTabName;
885 END IF;
886 LOOP
887 FETCH curTabName into eTabName;
888 EXIT WHEN curTabName%NOTFOUND;
889 END LOOP;
890 CLOSE curTabName;
891 UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);
892 -- UTL_FILE.NEW_LINE(filehandle,1);
893 --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName;
894
895 bufString:= bufString
896 || dbms_assert.enquote_name(eTabName)
897 || '.'
898 || dbms_assert.enquote_name(eLevelColName)
899 || ' '
900 || dbms_assert.enquote_name(eLevelColName);
901
902 IF hierLevelList(l).levelNum < mvListdec(j).lvlCount THEN
903 UTL_FILE.PUT_LINE(filehandle,',');
904 bufString:= bufString || ', ';
905 END IF;
906 END IF;
907 END LOOP;
908 IF mvListdec(j).highestLvl = FALSE THEN
909 UTL_FILE.PUT_LINE(filehandle,',');
910 bufString:= bufString || ', ';
911 END IF;
912 END IF;
913 END LOOP;
914 UTL_FILE.NEW_LINE(filehandle,1);
915 UTL_FILE.PUT_LINE(filehandle,'FROM');
916 o:= 0;
917 bufString:= bufString || 'FROM ';
918 FOR j in 1..mvListinc.Count LOOP
919 IF mvListinc(j).mvNum= i THEN
920 edimName:= mvListinc(j).dimName;
921 ehierName:= mvListinc(j).hierName;
922 FOR l in 1..hierLevelList.Count LOOP
923 IF hierLevelList(l).dimName= edimName AND
924 hierLevelList(l).hierName= ehierName AND
925 mvListinc(j).lvlCount= hierLevelList(l).levelNum THEN
926 eLevelName:= hierLevelList(l).levelName;
927 IF NOT curColName%ISOPEN THEN
928 OPEN curColName;
929 END IF;
930 LOOP
931 FETCH curColName into eLevelColName,ePosition;
932 EXIT WHEN curColName%NOTFOUND;
933 END LOOP;
934 CLOSE curColName;
935 IF NOT curTabName%ISOPEN THEN
936 OPEN curTabName;
937 END IF;
938 LOOP
939 FETCH curTabName into eTabName;
940 EXIT WHEN curTabName%NOTFOUND;
941 END LOOP;
942 CLOSE curTabName;
943 o:= o + 1;
944 mvtableList.EXTEND;
945 mvtableList(o).tabName:= eTabName;
946 mvtableList(o).ownerName:= eOwnerName;
947 -- UTL_FILE.PUTF(filehandle,'%s.%s %s,', eOwnerName,eTabName,eTabName);
948 -- UTL_FILE.PUT_LINE(filehandle,' ');
949 -- bufString:= bufString || eOwnerName || '.' || eTabName || ' ' || eTabName || ', ';
953 IF NOT curColDimName%ISOPEN THEN
950 ELSIF hierLevelList(l).dimName = eDimName AND
951 hierLevelList(l).hierName IS NULL THEN
952 eLevelName:= hierLevelList(l).levelName;
954 OPEN curColDimName;
955 END IF;
956 LOOP
957 FETCH curColDimName into eLevelColName,ePosition ;
958 EXIT WHEN curColDimName%NOTFOUND;
959 END LOOP;
960 CLOSE curColDimName;
961 IF NOT curTabName%ISOPEN THEN
962 OPEN curTabName;
963 END IF;
964 LOOP
965 FETCH curTabName into eTabName;
966 EXIT WHEN curTabName%NOTFOUND;
967 END LOOP;
968 CLOSE curTabName;
969 o:= o + 1;
970 mvtableList.EXTEND;
971 mvtableList(o).tabName:= eTabName;
972 mvtableList(o).ownerName:= eOwnerName;
973 END IF;
974 END LOOP;
975 END IF;
976 END LOOP;
977 FOR j in 1..mvListinc.Count LOOP
978 IF mvListinc(j).mvNum= i THEN
979 edimName:= mvListinc(j).dimName;
980 ehierName:= mvListinc(j).hierName;
981 FOR l in 1..hierLevelList.Count LOOP
982 IF hierLevelList(l).dimName= edimName AND
983 hierLevelList(l).hierName= ehierName THEN
984 eLevelName:= hierLevelList(l).levelName;
985 -- mvnum:= mvnum + 1;
986 IF NOT curColName%ISOPEN THEN
987 OPEN curColName;
988 END IF;
989 LOOP
990 FETCH curColName into eLevelColName,ePosition ;
991 EXIT WHEN curColName%NOTFOUND;
992 END LOOP;
993 CLOSE curColName;
994 IF NOT curTabName%ISOPEN THEN
995 OPEN curTabName;
996 END IF;
997 LOOP
998 FETCH curTabName into eTabName;
999 EXIT WHEN curTabName%NOTFOUND;
1000 END LOOP;
1001 CLOSE curTabName;
1002 parentString:= eTabName || '.' || eLevelColName;
1003 childString:= parentString;
1004 IF NOT curParentName%ISOPEN THEN
1005 OPEN curParentName;
1006 END IF;
1007 LOOP
1008 FETCH curParentName into eparLevelName,etableName;
1009 EXIT WHEN curParentName%FOUND;
1010 END LOOP;
1011 CLOSE curParentName;
1012 childString:= eparLevelName;
1013 IF childString <> parentString THEN
1014 tableCount:= mvtableList.Count;
1015 z:= 1;
1016 whereFlag:= FALSE;
1017 WHILE z <= tableCount LOOP
1018 IF mvtableList(z).tabName = eTabName THEN
1019 whereFlag:= TRUE;
1020 END IF;
1021 EXIT WHEN mvtableList(z).tabName= eTabName;
1022 z:= z + 1;
1023 END LOOP;
1024 IF whereFlag= FALSE THEN
1025 o:= o +1;
1026 mvtableList.EXTEND;
1027 mvtableList(o).tabName:= eTabName;
1028 mvtableList(o).ownerName:= eOwnerName;
1029 END IF;
1030 -- bufString:= bufString || ' AND ';
1031 -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
1032 -- bufString:= bufString || '(' || childString || '=' || parentString || ') ';
1033 END IF;
1034 ELSIF hierLevelList(l).dimName= edimName AND
1035 hierLevelList(l).hierName= NULL THEN
1036 eLevelName:= hierLevelList(l).levelName;
1037 -- mvnum:= mvnum + 1;
1038 IF NOT curColDimName%ISOPEN THEN
1039 OPEN curColDimName;
1040 END IF;
1041 LOOP
1042 FETCH curColDimName into eLevelColName,ePosition ;
1043 EXIT WHEN curColDimName%NOTFOUND;
1044 END LOOP;
1045 CLOSE curColDimName;
1046 IF NOT curTabName%ISOPEN THEN
1047 OPEN curTabName;
1048 END IF;
1049 LOOP
1050 FETCH curTabName into eTabName;
1051 EXIT WHEN curTabName%NOTFOUND;
1052 END LOOP;
1053 CLOSE curTabName;
1054 parentString:= eTabName || '.' || eLevelColName;
1055 childString:= parentString;
1056 IF NOT curParentName%ISOPEN THEN
1057 OPEN curParentName;
1058 END IF;
1059 LOOP
1060 FETCH curParentName into eparLevelName,etableName;
1061 EXIT WHEN curParentName%FOUND;
1062 END LOOP;
1063 CLOSE curParentName;
1064 childString:= eparLevelName;
1065 IF childString <> parentString THEN
1066 tableCount:= mvtableList.Count;
1067 z:= 1;
1068 whereFlag:= FALSE;
1069 WHILE z <= tableCount LOOP
1070 IF mvtableList(z).tabName = eTabName THEN
1071 whereFlag:= TRUE;
1072 END IF;
1073 EXIT WHEN mvtableList(z).tabName= eTabName;
1074 z:= z + 1;
1075 END LOOP;
1076 IF whereFlag= FALSE THEN
1077 o:= o +1;
1078 mvtableList.EXTEND;
1079 mvtableList(o).tabName:= eTabName;
1080 mvtableList(o).ownerName:= eOwnerName;
1081 END IF;
1082 -- bufString:= bufString || ' AND ';
1083 -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
1084 -- bufString:= bufString || '(' || childString || '=' || parentString || ') ';
1085 END IF;
1086 END IF;
1087 END LOOP;
1088 END IF;
1089 END LOOP;
1090 FOR o in 1..mvtableList.Count LOOP
1091 --Bug 8915150 bufString:= bufString || mvtableList(o).ownerName || '.' || mvtableList(o).tabName || ' '
1092 --Bug 8915150 || mvtableList(o).tabName || ', ';
1093
1094 bufString:= bufString
1095 ||dbms_assert.enquote_name(mvtableList(o).ownerName)
1099 || dbms_assert.enquote_name(mvtableList(o).tabName)
1096 || '.'
1097 || dbms_assert.enquote_name(mvtableList(o).tabName)
1098 || ' '
1100 || ', ';
1101 UTL_FILE.PUTF(filehandle,'%s.%s %s,', mvtableList(o).ownerName,mvtableList(o).tabName,
1102 mvtableList(o).tabName);
1103 UTL_FILE.PUT_LINE(filehandle,' ');
1104 END LOOP;
1105 mvtableList.DELETE;
1106 UTL_FILE.PUTF(filehandle,'%s.%s %s',eOwnerName,eFactName,eFactName);
1107 UTL_FILE.PUT_LINE(filehandle,' ');
1108 UTL_FILE.PUT_LINE(filehandle,'WHERE');
1109 whereFlag:= FALSE;
1110 --Bug 8915150 bufString:= bufString || eOwnerName || '.' || eFactName || ' ' || eFactName || ' ';
1111
1112 bufString:= bufString
1113 || dbms_assert.enquote_name(eOwnerName)
1114 || '.'
1115 || dbms_assert.enquote_name(eFactName)
1116 || ' '
1117 || dbms_assert.enquote_name(eFactName)
1118 || ' ';
1119
1120 bufString:= bufString || ' ' || 'WHERE ';
1121 mvnum:= 0;
1122
1123 FOR j in 1..mvListinc.Count LOOP
1124 IF mvListinc(j).mvNum= i THEN
1125 edimName:= mvListinc(j).dimName;
1126 ehierName:= mvListinc(j).hierName;
1127 FOR l in 1..hierLevelList.Count LOOP
1128 IF hierLevelList(l).dimName= edimName AND
1129 hierLevelList(l).hierName= ehierName AND
1130 mvListinc(j).lvlCount= hierLevelList(l).levelNum THEN
1131 eLevelName:= hierLevelList(l).levelName;
1132 mvnum:= mvnum + 1;
1133 IF NOT curColName%ISOPEN THEN
1134 OPEN curColName;
1135 END IF;
1136 poscount:= 0;
1137 LOOP
1138 FETCH curColName into eLevelColName,ePosition;
1139 EXIT WHEN curColName%NOTFOUND;
1140 poscount:= poscount + 1;
1141 END LOOP;
1142 CLOSE curColName;
1143 IF NOT curColName%ISOPEN THEN
1144 OPEN curColName;
1145 END IF;
1146 LOOP
1147 FETCH curColName into eLevelColName,ePosition;
1148 EXIT WHEN curColName%NOTFOUND;
1149 IF NOT curTabName%ISOPEN THEN
1150 OPEN curTabName;
1151 END IF;
1152 LOOP
1153 FETCH curTabName into eTabName;
1154 EXIT WHEN curTabName%NOTFOUND;
1155 END LOOP;
1156 CLOSE curTabName;
1157 IF NOT curLeavName%ISOPEN THEN
1158 OPEN curLeavName;
1159 END IF;
1160 LOOP
1161 FETCH curLeavName into eLeavColName ;
1162 EXIT WHEN curLeavName%FOUND;
1163 END LOOP;
1164 CLOSE curLeavName;
1165 UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
1166 --Bug 8915150 bufString:= bufString || '(' || eTabName || '.' || eLevelColName || '=' || eFactName || '.' || eLeavColName || ') ';
1167
1168 bufString:= bufString
1169 || '('
1170 || dbms_assert.enquote_name(eTabName)
1171 || '.'
1172 || dbms_assert.enquote_name(eLevelColName)
1173 || '='
1174 || dbms_assert.enquote_name(eFactName)
1175 || '.'
1176 || dbms_assert.enquote_name(eLeavColName)
1177 || ') ';
1178
1179 IF ePosition < poscount THEN
1180 UTL_FILE.PUT_LINE(filehandle,' AND');
1181 bufString:= bufString || ' AND ';
1182 END IF;
1183 END LOOP;
1184 CLOSE curColName;
1185 ELSIF hierLevelList(l).dimName = eDimName AND
1186 hierLevelList(l).hierName IS NULL THEN
1187 eLevelName:= hierLevelList(l).levelName;
1188 mvnum:= mvnum + 1;
1189 IF NOT curColDimName%ISOPEN THEN
1190 OPEN curColDimName;
1191 END IF;
1192 poscount:= 0;
1193 LOOP
1194 FETCH curColDimName into eLevelColName,ePosition;
1195 EXIT WHEN curColDimName%NOTFOUND;
1196 poscount:= poscount + 1;
1197 END LOOP;
1198 CLOSE curColDimName;
1199 IF NOT curColDimName%ISOPEN THEN
1200 OPEN curColDimName;
1201 END IF;
1202 LOOP
1203 FETCH curColDimName into eLevelColName,ePosition ;
1204 EXIT WHEN curColDimName%NOTFOUND;
1205 IF NOT curTabName%ISOPEN THEN
1206 OPEN curTabName;
1207 END IF;
1208 LOOP
1209 FETCH curTabName into eTabName;
1210 EXIT WHEN curTabName%NOTFOUND;
1211 END LOOP;
1212 CLOSE curTabName;
1213 IF NOT curLeavName%ISOPEN THEN
1214 OPEN curLeavName;
1215 END IF;
1216 LOOP
1217 FETCH curLeavName into eLeavColName ;
1218 EXIT WHEN curLeavName%NOTFOUND;
1219 END LOOP;
1220 CLOSE curLeavName;
1221 -- UTL_FILE.PUT_LINE(filehandle,' AND');
1222 UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
1223 --Bug 8915150 bufString:= bufString || '(' || eTabName || '.' || eLevelColName || '=' || eFactName || '.' || eLeavColName || ') ';
1224
1225 bufString:= bufString
1229 || dbms_assert.enquote_name(eLevelColName)
1226 || '('
1227 || dbms_assert.enquote_name(eTabName)
1228 || '.'
1230 || '='
1231 || dbms_assert.enquote_name(eFactName)
1232 || '.'
1233 || dbms_assert.enquote_name(eLeavColName)
1234 || ') ';
1235
1236 IF ePosition < poscount THEN
1237 UTL_FILE.PUT_LINE(filehandle,' AND');
1238 bufString:= bufString || ' AND ';
1239 END IF;
1240 END LOOP;
1241 CLOSE curColDimName;
1242 END IF;
1243 END LOOP;
1244 IF mvnum < dimList.Count THEN
1245 UTL_FILE.PUT_LINE(filehandle,' AND');
1246 bufString:= bufString || ' AND ';
1247 END IF;
1248 END IF;
1249 END LOOP;
1250 FOR j in 1..mvListinc.Count LOOP
1251 IF mvListinc(j).mvNum= i THEN
1252 edimName:= mvListinc(j).dimName;
1253 ehierName:= mvListinc(j).hierName;
1254 FOR l in 1..hierLevelList.Count LOOP
1255 IF hierLevelList(l).dimName= edimName AND
1256 hierLevelList(l).hierName= ehierName THEN
1257 eLevelName:= hierLevelList(l).levelName;
1258 -- mvnum:= mvnum + 1;
1259 IF NOT curColName%ISOPEN THEN
1260 OPEN curColName;
1261 END IF;
1262 LOOP
1263 FETCH curColName into eLevelColName,ePosition ;
1264 EXIT WHEN curColName%NOTFOUND;
1265 IF NOT curTabName%ISOPEN THEN
1266 OPEN curTabName;
1267 END IF;
1268 LOOP
1269 FETCH curTabName into eTabName;
1270 EXIT WHEN curTabName%NOTFOUND;
1271 END LOOP;
1272 CLOSE curTabName;
1273 parentString:= eTabName || '.' || eLevelColName;
1274 childString:= parentString;
1275 IF NOT curParentName%ISOPEN THEN
1276 OPEN curParentName;
1277 END IF;
1278 LOOP
1279 FETCH curParentName into eparLevelName,etableName ;
1280 EXIT WHEN curParentName%FOUND;
1281 END LOOP;
1282 CLOSE curParentName;
1283 childString:= eparLevelName;
1284 IF childString <> parentString THEN
1285 bufString:= bufString || ' AND ';
1286 UTL_FILE.PUT_LINE(filehandle,' AND');
1287 UTL_FILE.PUTF(filehandle,'(%s = %s) ', childString,parentString);
1288 --Bug 8915150 bufString:= bufString || '(' || childString || '=' || parentString || ') ';
1289 bufString:= bufString
1290 || '('
1291 || dbms_assert.enquote_name(childString)
1292 || '='
1293 || dbms_assert.enquote_name(parentString)
1294 || ') ';
1295
1296 END IF;
1297 END LOOP;
1298 CLOSE curColName;
1299 ELSIF hierLevelList(l).dimName= edimName AND
1300 hierLevelList(l).hierName= NULL THEN
1301 eLevelName:= hierLevelList(l).levelName;
1302 -- mvnum:= mvnum + 1;
1303 IF NOT curColDimName%ISOPEN THEN
1304 OPEN curColDimName;
1305 END IF;
1306 LOOP
1307 FETCH curColDimName into eLevelColName,ePosition ;
1308 EXIT WHEN curColDimName%NOTFOUND;
1309 IF NOT curTabName%ISOPEN THEN
1310 OPEN curTabName;
1311 END IF;
1312 LOOP
1313 FETCH curTabName into eTabName;
1314 EXIT WHEN curTabName%NOTFOUND;
1315 END LOOP;
1316 CLOSE curTabName;
1317 parentString:= eTabName || '.' || eLevelColName;
1318 childString:= parentString;
1319 IF NOT curParentName%ISOPEN THEN
1320 OPEN curParentName;
1321 END IF;
1322 LOOP
1323 FETCH curParentName into eparLevelName,etableName ;
1324 EXIT WHEN curParentName%FOUND;
1325 END LOOP;
1326 CLOSE curParentName;
1327 childString:= eparLevelName;
1328 IF childString <> parentString THEN
1329 bufString:= bufString || ' AND ';
1330 UTL_FILE.PUT_LINE(filehandle,' AND');
1331 UTL_FILE.PUTF(filehandle,'(%s = %s) ', childString,parentString);
1332 --Bug 8915150 bufString:= bufString || '(' || childString || '=' || parentString || ') ';
1333
1334 bufString:= bufString
1335 || '('
1336 || dbms_assert.enquote_name(childString)
1337 || '='
1338 || dbms_assert.enquote_name(parentString)
1339 || ') ';
1340 END IF;
1341 END LOOP;
1342 CLOSE curColDimName;
1343 END IF;
1344 END LOOP;
1345 END IF;
1346 END LOOP;
1347
1348 UTL_FILE.PUT_LINE(filehandle,' GROUP BY ');
1349 bufString:= bufString || 'GROUP BY ';
1350 FOR j in 1..mvListinc.Count LOOP
1351 IF mvListinc(j).mvNum= i THEN
1352 mvnum:= 0;
1353 edimName:= mvListinc(j).dimName;
1354 ehierName:= mvListinc(j).hierName;
1355 FOR l in 1..hierLevelList.Count LOOP
1356 IF hierLevelList(l).dimName= edimName AND
1360 IF NOT curColName%ISOPEN THEN
1357 hierLevelList(l).hierName= ehierName THEN
1358 eLevelName:= hierLevelList(l).levelName;
1359 mvnum:= mvnum + 1;
1361 OPEN curColName;
1362 END IF;
1363 poscount:= 0;
1364 LOOP
1365 FETCH curColName into eLevelColName,ePosition;
1366 EXIT WHEN curColName%NOTFOUND;
1367 poscount:= poscount + 1;
1368 END LOOP;
1369 CLOSE curColName;
1370 IF NOT curColName%ISOPEN THEN
1371 OPEN curColName;
1372 END IF;
1373 LOOP
1374 FETCH curColName INTO eLevelColName,ePosition ;
1375 EXIT WHEN curColName%NOTFOUND;
1376 IF NOT curTabName%ISOPEN THEN
1377 OPEN curTabName;
1378 END IF;
1379 LOOP
1380 FETCH curTabName INTO eTabName;
1381 EXIT WHEN curTabName%NOTFOUND;
1382 END LOOP;
1383 CLOSE curTabName;
1384 IF ePosition < poscount AND ePosition = 1 THEN
1385 UTL_FILE.PUT(filehandle,'(');
1386 ELSIF ePosition <= poscount AND ePosition <> 1 THEN
1387 UTL_FILE.PUT(filehandle,', ');
1388 END IF;
1389 UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
1390 --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';
1391
1392 bufString:= bufString
1393 || dbms_assert.enquote_name(eTabName)
1394 || '.'
1395 || dbms_assert.enquote_name(eLevelColName)
1396 || ' ';
1397
1398 END LOOP;
1399 CLOSE curColName;
1400 IF ePosition <> 1 THEN
1401 UTL_FILE.PUT(filehandle,')');
1402 END IF;
1403 IF mvListinc(j).lvlCount > mvnum AND mvnum = 1 THEN
1404 UTL_FILE.PUT(filehandle,', ROLLUP( ');
1405 bufString:= bufString || ', ROLLUP( ';
1406 ELSIF mvListinc(j).lvlCount > mvnum AND mvnum > 1 THEN
1407 UTL_FILE.PUT_LINE(filehandle,', ');
1408 bufString:= bufString || ', ';
1409 ELSIF mvListinc(j).lvlCount= mvnum THEN
1410 UTL_FILE.PUT(filehandle,') ');
1411 bufString:= bufString || ') ';
1412 END IF;
1413 ELSIF hierLevelList(l).dimName = eDimName AND
1414 hierLevelList(l).hierName IS NULL THEN
1415 eLevelName:= hierLevelList(l).levelName;
1416 mvnum:= mvnum + 1;
1417 IF NOT curColDimName%ISOPEN THEN
1418 OPEN curColDimName;
1419 END IF;
1420 poscount:= 0;
1421 LOOP
1422 FETCH curColDimName into eLevelColName,ePosition;
1423 EXIT WHEN curColDimName%NOTFOUND;
1424 poscount:= poscount + 1;
1425 END LOOP;
1426 CLOSE curColDimName;
1427 IF NOT curColDimName%ISOPEN THEN
1428 OPEN curColDimName;
1429 END IF;
1430 LOOP
1431 FETCH curColDimName into eLevelColName,ePosition ;
1432 EXIT WHEN curColDimName%NOTFOUND;
1433 IF NOT curTabName%ISOPEN THEN
1434 OPEN curTabName;
1435 END IF;
1436 LOOP
1437 FETCH curTabName into eTabName;
1438 EXIT WHEN curTabName%FOUND;
1439 END LOOP;
1440 CLOSE curTabName;
1441 IF ePosition < poscount AND ePosition <> 1 THEN
1442 UTL_FILE.PUT(filehandle,'(');
1443 ELSIF ePosition <= poscount THEN
1444 UTL_FILE.PUT(filehandle,',');
1445 END IF;
1446 UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
1447 --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName || ' ';
1448
1449 bufString:= bufString
1450 || dbms_assert.enquote_name(eTabName)
1451 || '.'
1452 || dbms_assert.enquote_name(eLevelColName)
1453 || ' ';
1454
1455 END LOOP;
1456 CLOSE curColDimName;
1457 IF ePosition <> 1 THEN
1458 UTL_FILE.PUT(filehandle,' )');
1459 END IF;
1460 END IF;
1461 END LOOP;
1462 IF mvListinc(j).highestLvl = FALSE THEN
1463 UTL_FILE.PUT(filehandle,', ');
1464 bufString:= bufString || ', ';
1465 ELSE
1466 UTL_FILE.PUT(filehandle,';');
1467 -- bufString:= bufString || ';';
1468 END IF;
1469 END IF;
1470 END LOOP;
1471 eTempMV:= 'aaa';
1472 num_bytes:= 0;
1473 query_length:= LENGTH(bufString);
1474 IF NOT checkName%ISOPEN THEN
1475 OPEN checkName;
1476 END IF;
1477 LOOP
1478 FETCH checkName into mvString;
1479 IF bufString = mvString THEN
1480 EXIT ;
1481 ELSE
1482 -- DBMS_SUMMARY.estimate_mview_size(eTempMV,bufString,num_rows,num_bytes);
1483 -- INSERT INTO SYSTEM.MVIEW$_ADV_OUTPUT(RUNID#,OUTPUT_TYPE,RANK#,SUMMARY_OWNER,
1484 -- QUERY_TEXT,STORAGE_IN_BYTES,BENEFIT_TO_COST_RATIO) VALUES
1485 -- (runid,0,i,eOwnerName,bufString,num_bytes,0);
1486
1487 -- INSERT INTO SYSTEM.MVIEW$_ADV_PARTITION(runid#,rank#,summary_owner,query_text) VALUES
1488 -- (runId,i,eOwnerName,indexString);
1489 dupmvFlag:= TRUE;
1490
1494 END LOOP;
1491 indexString:= '';
1492 END IF;
1493 EXIT WHEN checkName%NOTFOUND;
1495 CLOSE checkName;
1496
1497 UTL_FILE.NEW_LINE(filehandle,2);
1498 UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1499 eOwnerName);
1500 UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1501 UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
1502 UTL_FILE.NEW_LINE(filehandle,1);
1503 UTL_FILE.NEW_LINE(filehandle,2);
1504 UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
1505
1506 eTempMV:= '';
1507 bufString:= '';
1508 UTL_FILE.NEW_LINE(filehandle,1);
1509 mvnum:= 0;
1510 tpnum:= 0;
1511 FOR j in 1..mvListinc.Count LOOP
1512 IF mvListinc(j).mvNum= i THEN
1513 edimName:= mvListinc(j).dimName;
1514 ehierName:= mvListinc(j).hierName;
1515 FOR l in 1..hierLevelList.Count LOOP
1516 IF hierLevelList(l).dimName= eDimName AND
1517 hierLevelList(l).hierName= eHierName THEN
1518 eLevelName:= hierLevelList(l).levelName;
1519 IF NOT curColName%ISOPEN THEN
1520 OPEN curColName;
1521 END IF;
1522 LOOP
1523 FETCH curColName into eLevelColName,ePosition ;
1524 EXIT WHEN curColName%NOTFOUND;
1525 END LOOP;
1526 CLOSE curColName;
1527 IF NOT curTabName%ISOPEN THEN
1528 OPEN curTabName;
1529 END IF;
1530 LOOP
1531 FETCH curTabName into eTabName;
1532 EXIT WHEN curTabName%NOTFOUND;
1533 END LOOP;
1534 CLOSE curTabName;
1535 tpnum:= j * l;
1536 IF hierLevelList(l).levelNum <> 1 THEN
1537 UTL_FILE.NEW_LINE(filehandle,2);
1538 UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BI_%s%s%s%s_%s',SUBSTR(exCubeName,0,5),SUBSTR(eLevelColName,0,5),SUBSTR(eLevelColName,7,5),SUBSTR(eLevelColName,-3,5),tpnum);
1539 UTL_FILE.PUTF(filehandle,'_%s ON %s(%s_%s)',i,eMVName,eLevelColName,l);
1540 UTL_FILE.NEW_LINE(filehandle,1);
1541 UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1542 UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1543 IF indspcName IS NOT NULL THEN
1544 UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1545 UTL_FILE.NEW_LINE(filehandle,1);
1546 END IF;
1547 UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1548 UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1549 UTL_FILE.NEW_LINE(filehandle,2);
1550 END IF;
1551 mvnum:= mvnum + 1;
1552 --Bug 8915150 bufString:= bufString || 'BMPIDX_' || eLevelColName;
1553
1554 bufString:= bufString
1555 || 'BMPIDX_'
1556 || dbms_assert.enquote_name(eLevelColName);
1557
1558 -- bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
1559 -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';
1560
1561 -- bufString:= bufString || 'PARALLEL NOLOGGING; ';
1562 -- IF dupmvFlag= TRUE THEN
1563 -- INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
1564 -- SUMMARY_OWNER) VALUES
1565 -- (runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);
1566 -- END IF;
1567 bufString:= '';
1568 ELSIF hierLevelList(l).dimName = eDimName AND
1569 hierLevelList(l).hierName IS NULL THEN
1570 eLevelName:= hierLevelList(l).levelName;
1571 mvnum:= mvnum + 1;
1572 IF NOT curColDimName%ISOPEN THEN
1573 OPEN curColDimName;
1574 END IF;
1575 LOOP
1576 FETCH curColDimName into eLevelColName,ePosition ;
1577 EXIT WHEN curColDimName%NOTFOUND;
1578 END LOOP;
1579 CLOSE curColDimName;
1580 IF NOT curTabName%ISOPEN THEN
1581 OPEN curTabName;
1582 END IF;
1583 LOOP
1584 FETCH curTabName into eTabName;
1585 EXIT WHEN curTabName%NOTFOUND;
1586 END LOOP;
1587 CLOSE curTabName;
1588 --Bug 8915150 bufString:= bufString || 'BMPIDX_' || eLevelColName;
1589
1590 bufString:= bufString
1591 || 'BMPIDX_'
1592 || dbms_assert.enquote_name(eLevelColName);
1593
1594 IF hierLevelList(l).levelNum <> 1 THEN
1595 UTL_FILE.NEW_LINE(filehandle,2);
1596 UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMPIDX_%s_%s ON %s(%s)',eLevelColName,i,eMVName,eLevelColName);
1597 UTL_FILE.NEW_LINE(filehandle,1);
1598 UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1599 UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1600 IF indspcName IS NOT NULL THEN
1601 UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1602 UTL_FILE.NEW_LINE(filehandle,1);
1603 END IF;
1604 UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1605 UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1606 UTL_FILE.NEW_LINE(filehandle,2);
1607 END IF;
1608 -- bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
1609 -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';
1610 -- bufString:= bufString || 'PARALLEL NOLOGGING; ';
1611 -- IF dupmvFlag= TRUE THEN
1612 -- INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
1616 bufString:= '';
1613 -- SUMMARY_OWNER) VALUES
1614 -- (runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);
1615 -- END IF;
1617 END IF;
1618 END LOOP;
1619 END IF;
1620 END LOOP;
1621
1622 -- INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,SUMMARY_OWNER,
1623 -- INDEX_TEXT) VALUES
1624 -- (runid,i,eOwnerName,bufString);
1625
1626 o:= 0;
1627 FOR j in 1..mvListinc.Count LOOP
1628 IF mvListinc(j).mvNum= i THEN
1629 edimName:= mvListinc(j).dimName;
1630 ehierName:= mvListinc(j).hierName;
1631 FOR l in 1..dimHierList.Count LOOP
1632 IF eDimName = dimHierList(l).dimName AND
1633 eHierName = dimHierList(l).hierName THEN
1634 o:= o + 1;
1635 partList.EXTEND;
1636 partList(o).dimName:= dimHierList(l).dimName;
1637 partList(o).hierName:= dimHierList(l).hierName;
1638 partList(o).dimNum:= dimHierList(l).dimNum;
1639 partList(o).hierNum:= dimHierList(l).hierNum;
1640 partList(o).levelCount:= dimHierList(l).levelCount;
1641 ELSIF eDimName = dimHierList(l).dimName AND
1642 dimHierList(l).hierName IS NULL THEN
1643 o:= o + 1;
1644 partList.EXTEND;
1645 partList(o).dimName:= dimHierList(l).dimName;
1646 partList(o).hierName:= dimHierList(l).hierName;
1647 partList(o).dimNum:= dimHierList(l).dimNum;
1648 partList(o).hierNum:= dimHierList(l).hierNum;
1649 partList(o).levelCount:= dimHierList(l).levelCount;
1650
1651 END IF;
1652 END LOOP;
1653 END IF;
1654 END LOOP;
1655 times:= 0;
1656 FOR k in 1..partList.Count LOOP
1657 times:= times + partList(k).levelCount;
1658 END LOOP;
1659
1660 FOR j in 1..partList.Count LOOP
1661 edimName:= partList(j).dimName;
1662 ehierName:= partList(j).hierName;
1663 m:= 0;
1664
1665 tpnum:= j * i;
1666 -- UTL_FILE.NEW_LINE(filehandle,2);
1667 -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
1668 -- UTL_FILE.NEW_LINE(filehandle,1);
1669 -- UTL_FILE.PUT_LINE(filehandle,' ( ');
1670 -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');
1671 -- UTL_FILE.PUT_LINE(filehandle,' ( ');
1672 FOR l in 1..hierLevelList.Count LOOP
1673 IF hierLevelList(l).dimName= eDimName AND
1674 hierLevelList(l).hierName= eHierName THEN
1675 eLevelName:= hierLevelList(l).levelName;
1676 IF NOT curColName%ISOPEN THEN
1677 OPEN curColName;
1678 END IF;
1679 LOOP
1680 FETCH curColName into eLevelColName,ePosition ;
1681 EXIT WHEN curColName%NOTFOUND;
1682 END LOOP;
1683 CLOSE curColName;
1684 IF NOT curTabName%ISOPEN THEN
1685 OPEN curTabName;
1686 END IF;
1687 LOOP
1688 FETCH curTabName into eTabName;
1689 EXIT WHEN curTabName%NOTFOUND;
1690 END LOOP;
1691 CLOSE curTabName;
1692 times:= times - 1;
1693 m:= m + 1;
1694 -- UTL_FILE.PUTF(filehandle,' sys_op_vecbit(sys_op_numtoraw(gid),%s)',times);
1695 -- IF m < partList(j).levelCount THEN
1696
1697 -- UTL_FILE.PUT(filehandle,',');
1698 -- END IF;
1699 -- UTL_FILE.PUTF(filehandle,' /* grouping(%s) */ ', eLevelColName);
1700 -- UTL_FILE.NEW_LINE(filehandle,1);
1701 END IF;
1702 END LOOP;
1703 -- UTL_FILE.PUT_LINE(filehandle,' ) ');
1704 -- UTL_FILE.PUT_LINE(filehandle,' ) ');
1705 -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
1706 -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
1707 -- IF indspcName IS NOT NULL THEN
1708 -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
1709 -- UTL_FILE.NEW_LINE(filehandle,1);
1710 -- END IF;
1711 -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
1712 -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
1713 -- UTL_FILE.NEW_LINE(filehandle,2);
1714 END LOOP;
1715 partList.DELETE;
1716 UTL_FILE.NEW_LINE(filehandle,2);
1717 UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
1718 eOwnerName);
1719 UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
1720 UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all hidden columns size 254'' , granularity=>''GLOBAL'') ;');
1721 UTL_FILE.NEW_LINE(filehandle,2);
1722 UTL_FILE.PUTF(filehandle,'execute cwm2_olap_cube.set_mv_summary_code(''%s'', ',
1723 eOwnerName);
1724 UTL_FILE.PUTF(filehandle,'''%s'', ',eCubeName);
1725 UTL_FILE.PUTF(filehandle,'''ROLLUP'') ;');
1726 -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all hidden columns size 254'' , granularity=>''GLOBAL'') ;');
1727 UTL_FILE.NEW_LINE(filehandle,3);
1728
1729
1730 END LOOP;
1731
1732
1733 -- commit;
1734 UTL_FILE.FCLOSE(filehandle);
1735 -- EXCEPTION
1736 -- WHEN others THEN
1737 -- UTL_FILE.PUT('others');
1738
1739 --END ;
1740
1741 commit;
1742 EXCEPTION
1743 WHEN UTL_FILE.INVALID_PATH THEN
1744 UTL_FILE.FCLOSE(filehandle);
1745 DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
1746 WHEN UTL_FILE.INVALID_OPERATION THEN
1747 UTL_FILE.FCLOSE(filehandle);
1748 DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
1749 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1750 UTL_FILE.FCLOSE(filehandle);
1754 DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
1751 DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
1752 WHEN UTL_FILE.WRITE_ERROR THEN
1753 UTL_FILE.FCLOSE(filehandle);
1755 WHEN UTL_FILE.INTERNAL_ERROR THEN
1756 UTL_FILE.FCLOSE(filehandle);
1757 DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
1758 WHEN others THEN
1759 -- UTL_FILE.FCLOSE(filehandle);
1760 RAISE;
1761 END ;
1762
1763 PROCEDURE CreateFactTab(runId IN NUMBER,exOwnerName IN varchar2,exCubeName IN varchar2,partitioning IN BOOLEAN) IS
1764 -- This it the Procedure to Create Cube MV in ROLLUP form for a Cube. This procedure is used to write to a table for OEM
1765 -- Example CreateFatMVTable(89,'XADEMO','ANALYTIC_CUBE','ANALYTIC_MV');
1766
1767 -- filehandle UTL_FILE.FILE_TYPE;
1768 eOwnerName varchar2(30);
1769 eCubeName varchar2(30);
1770 eMVName varchar2(30);
1771 -- eOutPath varchar2(30);
1772 -- eOutFile varchar2(30);
1773 eDimName varchar2(30);
1774 eHierName varchar2(30);
1775 eLevelName varchar2(30);
1776 eLevelColName varchar2(30);
1777 eLeavColName varchar2(30);
1778 eFactName varchar2(30);
1779 eUnitName varchar2(30);
1780 ePosition NUMBER;
1781 eTempMV varchar2(30);
1782 eTabName varchar2(30);
1783 eTableName varchar2(30);
1784 eparLevelName varchar2(100);
1785 parTabName varchar2(30);
1786 parColName varchar2(30);
1787 dimCount INTEGER;
1788 i INTEGER;
1789 j INTEGER;
1790 k INTEGER;
1791 m INTEGER;
1792 n INTEGER;
1793 o INTEGER;
1794 z INTEGER;
1795 c NUMBER;
1796 flag INTEGER;
1797 numOfMv NUMBER;
1798 numOfdimMV NUMBER;
1799 tempMVnum INTEGER;
1800 highLvlCount INTEGER;
1801 highLvlNum INTEGER;
1802 tableCount INTEGER;
1803 tempName varchar2(30);
1804 bufString varchar2(32767);
1805 indexString varchar2(32767);
1806 parentString varchar2(300);
1807 childString varchar2(300);
1808 mvString varchar2(32767);
1809 mvnum INTEGER;
1810 num_rows NUMBER;
1811 num_bytes NUMBER;
1812 partgid NUMBER;
1813 partnum NUMBER;
1814 tempnum NUMBER;
1815 tpnum NUMBER;
1816 val NUMBER;
1817 tkval NUMBER;
1818 times NUMBER;
1819 ntimes NUMBER;
1820 loopval NUMBER;
1821 tempval NUMBER;
1822 tpval NUMBER;
1823 query_length NUMBER;
1824 whereFlag BOOLEAN;
1825 dupmvFlag BOOLEAN;
1826 dupdimmvFlag BOOLEAN;
1827 others EXCEPTION;
1828 -- partition BOOLEAN;
1829 poscount NUMBER;
1830
1831 TYPE dimType IS RECORD
1832 (dimName varchar2(30),
1833 hierCount INTEGER);
1834
1835 TYPE dimHiertype IS RECORD
1836 (dimName varchar2(30),
1837 hierName varchar2(30),
1838 hierNum INTEGER,
1839 dimNum INTEGER,
1840 levelCount INTEGER
1841 );
1842
1843 TYPE dimhierList_t is TABLE of dimHiertype;
1844
1845
1846
1847 -- TYPE levelName_t IS TABLE OF VARCHAR2(30)
1848 -- INDEX BY BINARY_INTEGER;
1849
1850 -- TYPE levelPos_t IS TABLE OF VARCHAR2(30)
1851 -- INDEX BY BINARY_INTEGER;
1852
1853 TYPE hierLeveltype is RECORD
1854 (dimName varchar2(30),
1855 hierName varchar2(30),
1856 levelName varchar2(30),
1857 levelPos varchar2(30),
1858 levelNum INTEGER
1859 );
1860
1861 TYPE hierLevelList_t is TABLE of hierLeveltype;
1862
1863
1864
1865 TYPE mv_dimHier IS RECORD
1866 (mvNum INTEGER,
1867 dimName varchar2(30),
1868 hierName varchar2(30),
1869 lvlCount INTEGER,
1870 highestLvl BOOLEAN
1871 );
1872
1873
1874 TYPE mvtablename IS RECORD
1875 (ownerName varchar2(30),
1876 tabName varchar2(30)
1877 );
1878
1879 TYPE parnumtable is RECORD
1880 (gidNum NUMBER,
1881 ownerName varchar2(30)
1882 );
1883
1884
1885 TYPE parnumtable_t is TABLE of parnumtable;
1886
1887 TYPE mvtable_t is TABLE of mvtablename;
1888
1889
1890 TYPE mvtype_t IS TABLE of mv_dimHier;
1891
1892 TYPE dimList_t IS TABLE OF dimType;
1893 -- TYPE dimList IS TABLE OF ALL_OLAP_FACT_LEVEL_USES.DIMENSION_NAME%TYPE
1894
1895 TYPE levList_t IS TABLE OF ALL_OLAP_DIM_LEVELS.LEVEL_NAME%TYPE;
1896
1897 TYPE hierarchyList_t IS TABLE OF ALL_OLAP_DIM_HIERARCHIES.DIMENSION_NAME%TYPE;
1898
1899 TYPE colLvlList_t IS TABLE OF ALL_OLAP_LEVEL_KEY_COLUMN_USES.COLUMN_NAME%TYPE;
1900
1901
1902
1903 CURSOR curDimName IS SELECT DIMENSION_NAME
1904 FROM ALL_OLAP_FACT_LEVEL_USES WHERE FACT_TABLE_NAME= eFactName AND
1905 OWNER= eOwnerName AND CUBE_NAME= eCubeName;
1906
1907 CURSOR curLevelName IS SELECT V.L
1908 FROM ( SELECT CHILD_LEVEL_NAME L, POSITION P
1909 FROM ALL_OLAP_DIM_HIER_LEVEL_USES
1910 WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
1911 AND HIERARCHY_NAME= eHierName
1912 UNION
1913 SELECT PARENT_LEVEL_NAME L, POSITION+1
1914 FROM ALL_OLAP_DIM_HIER_LEVEL_USES
1915 WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
1916 AND HIERARCHY_NAME= eHierName) V
1917 ORDER BY V.P DESC;
1918
1919 CURSOR curHierarchyName IS SELECT HIERARCHY_NAME
1920 FROM ALL_OLAP_DIM_HIERARCHIES WHERE
1921 OWNER= eOwnerName AND
1922 DIMENSION_NAME= eDimName;
1923
1924 CURSOR curColName IS SELECT COLUMN_NAME, POSITION
1925 FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
1926 WHERE OWNER= eOwnerName AND LEVEL_NAME= eLevelName
1930 FROM ALL_OLAP_DIM_LEVELS
1927 AND DIMENSION_NAME= eDimName ;
1928
1929 CURSOR curTabName IS SELECT LEVEL_TABLE_NAME
1931 WHERE OWNER= eOwnerName AND LEVEL_NAME= eLevelName
1932 AND DIMENSION_NAME= eDimName;
1933
1934 CURSOR curParentName IS SELECT TABLE_NAME || '.' || COLUMN_NAME, TABLE_NAME
1935 FROM ALL_OLAP_JOIN_KEY_COLUMN_USES
1936 WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName
1937 AND HIERARCHY_NAME= eHierName
1938 AND CHILD_LEVEL_NAME= eLevelName
1939 AND POSITION = ePosition;
1940
1941
1942 CURSOR curLeavName IS SELECT distinct a.column_name
1943 FROM ALL_OLAP_KEY_COLUMN_USES a, ALL_OLAP_FOREIGN_KEYS b,
1944 ALL_OLAP_FACT_LEVEL_USES c
1945 where a.table_name = b.table_name
1946 and a.position = ePosition
1947 and a.key_name = b.foreign_key_name
1948 and b.table_name = c.fact_table_name
1949 and b.foreign_key_name = c.foreign_key_name
1950 and c.dimension_name = eDimName
1951 and c.level_name = eLevelName
1952 and c.dimension_owner = eOwnerName
1953 and c.fact_table_owner = eOwnerName
1954 and c.fact_table_name = eFactName;
1955
1956 CURSOR curFactName IS SELECT distinct c.fact_table_name
1957 from ALL_OLAP_CUBES a, ALL_OLAP_CUBE_MEASURES b, ALL_OLAP_CUBE_MEASURE_MAPS c
1958 where a.owner = b.owner
1959 and a.cube_name = b.cube_name
1960 and b.owner = c.owner
1961 and b.cube_name = c.cube_name
1962 and b.measure_name = c.measure_name
1963 and a.owner = eOwnerName
1964 and a.cube_name = eCubeName;
1965
1966 CURSOR curUnitName IS SELECT distinct c.column_name
1967 from ALL_OLAP_CUBES a, ALL_OLAP_CUBE_MEASURES b, ALL_OLAP_CUBE_MEASURE_MAPS c
1968 where a.owner = b.owner
1969 and a.cube_name = b.cube_name
1970 and b.owner = c.owner
1971 and b.cube_name = c.cube_name
1972 and b.measure_name = c.measure_name
1973 and a.owner = eOwnerName
1974 and a.cube_name = eCubeName;
1975
1976 CURSOR checkName IS SELECT QUERY from ALL_MVIEWS
1977 where owner= eOwnerName
1978 and QUERY_LEN= query_length;
1979
1980
1981 CURSOR curLevDimName IS SELECT LEVEL_NAME
1982 FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
1983 WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName;
1984
1985 CURSOR curColDimName IS SELECT COLUMN_NAME, POSITION
1986 FROM ALL_OLAP_LEVEL_KEY_COLUMN_USES
1987 WHERE OWNER= eOwnerName AND DIMENSION_NAME= eDimName;
1988
1989 dimHierList dimhierList_t:= dimhierList_t();
1990 hierLevelList hierLevelList_t:= hierLevelList_t();
1991 dimList dimList_t:= dimList_t();
1992 levList levList_t:= levList_t();
1993 hierarchyList hierarchyList_t:= hierarchyList_t();
1994 colLvlList colLvlList_t:= colLvlList_t();
1995 partList dimhierList_t:= dimhierList_t();
1996 mvtableList mvtable_t:= mvtable_t();
1997 parnum parnumtable_t := parnumtable_t();
1998
1999
2000 mvList mvtype_t:= mvtype_t();
2001 mvListinc mvtype_t:= mvtype_t();
2002 mvListdec mvtype_t:= mvtype_t();
2003 mvdimList mvtype_t:= mvtype_t();
2004
2005
2006 BEGIN
2007 -- CREATE TABLE SYSTEM.MVIEW$_ADV_INDEX (RUNID# NUMBER NOT NULL,
2008 -- RANK# NUMBER NOT NULL,
2009 -- SUMMARY_OWNER VARCHAR2(32),
2010 -- INDEX_TEXT LONG);
2011 numOfMV:= 1;
2012 numOfdimMV:= 1;
2013 -- eOutPath:= '/users/oracle';
2014 eOwnerName:= UPPER(exOwnerName);
2015 eCubeName:= UPPER(exCubeName);
2016 cwm2_olap_metadata_refresh.mr_refresh;
2017 -- IF UTL_FILE.IS_OPEN(filehandle) = FALSE THEN
2018 -- filehandle := UTL_FILE.FOPEN(eOutPath,eOutFile,'w');
2019 -- END IF;
2020 IF NOT curFactName%ISOPEN THEN
2021 OPEN curFactName;
2022 LOOP
2023 FETCH curFactName INTO eFactName;
2024 EXIT WHEN curFactName%NOTFOUND;
2025 END LOOP;
2026 CLOSE CurFactName;
2027 END IF;
2028 IF eFactName IS NULL THEN
2029 RAISE UTL_FILE.INTERNAL_ERROR;
2030 END IF;
2031 IF NOT curFactName%ISOPEN THEN
2032 OPEN curFactName;
2033 LOOP
2034 FETCH curFactName INTO eFactName;
2035 EXIT WHEN curFactName%NOTFOUND;
2036 END LOOP;
2037 CLOSE CurFactName;
2038 END IF;
2039
2040 IF NOT curDimName%ISOPEN THEN
2041 OPEN curDimName;
2042 i:= 0;
2043 LOOP
2044 FETCH curDimName INTO tempName;
2045 EXIT WHEN curDimName%NOTFOUND;
2046 dimList.EXTEND;
2047
2048 i:= i + 1;
2049 dimList(i).dimName:= tempName;
2050 -- dimList(i):= i;
2051 END LOOP;
2052 CLOSE CurDimName;
2053 END IF;
2054 dimCount:= dimList.Count;
2055 j:= 0;
2056 FOR i in 1..dimList.Count LOOP
2057 eDimName:= dimList(i).dimName;
2058 k:= 0;
2059 IF NOT curHierarchyName%ISOPEN THEN
2060 OPEN curHierarchyName;
2061 END IF;
2062 LOOP
2063 FETCH curHierarchyName into tempName;
2064 EXIT WHEN curHierarchyName%NOTFOUND;
2065 j:= j + 1;
2066 k:= k + 1;
2067 dimHierList.EXTEND;
2068 dimHierList(j).dimName := eDimName;
2069 dimHierList(j).dimNum := i;
2070 dimHierList(j).hierName:= tempName;
2071 dimHierList(j).hierNum:= k;
2072 END LOOP;
2073 CLOSE curHierarchyName;
2074 IF k = 0 THEN
2075 j:= j + 1;
2076 k:= k + 1;
2077 dimHierList.EXTEND;
2078 dimHierList(j).dimName := eDimName;
2079 dimHierList(j).dimNum := i;
2080 dimHierList(j).hierName:= NULL;
2081 dimHierList(j).hierNum:= k;
2082 END IF;
2083 dimList(i).hierCount:= k;
2084 END LOOP;
2085 j:= 0;
2086 FOR i in 1..dimHierList.Count LOOP
2087 eDimName:= dimHierList(i).dimName;
2091 OPEN curLevelName;
2088 eHierName:= dimHierList(i).hierName;
2089 k:= 0;
2090 IF NOT curLevelName%ISOPEN THEN
2092 END IF;
2093 LOOP
2094 FETCH curLevelName into tempName;
2095 EXIT WHEN curLevelName%NOTFOUND;
2096 k:= k + 1;
2097 j:= j + 1;
2098 hierLevelList.EXTEND;
2099 hierLevelList(j).dimName:= eDimName;
2100 hierLevelList(j).hierName:= eHierName;
2101 hierLevelList(j).levelName:= tempName;
2102 hierLevelList(j).levelNum:= k;
2103 -- hierLevelList(j).levelPos.EXTEND;
2104 -- heirLevelList(j).levelName(k):= tempName;
2105 END LOOP;
2106 CLOSE curLevelName;
2107 IF k= 0 THEN
2108 IF NOT curLevDimName%ISOPEN THEN
2109 OPEN curLevDimName;
2110 END IF;
2111 LOOP
2112 FETCH curLevDimName into tempName;
2113 EXIT WHEN curLevDimName%NOTFOUND;
2114 k:= k + 1;
2115 j:= j + 1;
2116 hierLevelList.EXTEND;
2117 hierLevelList(j).dimName:= eDimName;
2118 hierLevelList(j).hierName:= NULL;
2119 hierLevelList(j).levelName:= tempName;
2120 hierLevelList(j).levelNum:= k;
2121 END LOOP;
2122 CLOSE curLevDimName;
2123 END IF;
2124 dimHierList(i).levelCount:= k;
2125
2126 END LOOP;
2127
2128 numOfMV:= 1;
2129 FOR i in 1..dimList.Count LOOP
2130 numOfMV:= numOfMV * dimList(i).hierCount;
2131 END LOOP;
2132
2133 m:= 0;
2134 n:= 0;
2135 flag:= 1;
2136 FOR i in 1..dimList.Count LOOP
2137 tempMVnum:= numOfMV / dimList(i).hierCount;
2138 eDimName:= dimList(i).dimName;
2139 n:= 0;
2140 IF flag= 1 THEN
2141 FOR j in 1..dimList(i).hierCount LOOP
2142 FOR l in 1..dimHierList.Count LOOP
2143 IF dimHierList(l).dimName= eDimName AND
2144 dimHierList(l).hierNum= j THEN
2145 FOR k in 1..tempMVnum LOOP
2146 eHierName:= dimHierList(l).hierName;
2147 m:= m + 1;
2148 n:= n + 1;
2149 flag:= 0;
2150 mvList.EXTEND;
2151 mvList(m).mvNum:= n;
2152 mvList(m).dimName:= eDimName;
2153 mvList(m).hierName:= eHierName;
2154 END LOOP;
2155 END IF;
2156 END LOOP;
2157 END LOOP;
2158 ELSE
2159 FOR k in 1..tempMVnum LOOP
2160 FOR j in 1..dimList(i).hierCount LOOP
2161 FOR l in 1..dimHierList.Count LOOP
2162 IF dimHierList(l).dimName= eDimName AND
2163 dimHierList(l).hierNum= j THEN
2164 eHierName:= dimHierList(l).hierName;
2165 m:= m + 1;
2166 n:= n + 1;
2167 flag:= 1;
2168 mvList.EXTEND;
2169 mvList(m).mvNum:= n;
2170 mvList(m).dimName:= eDimName;
2171 mvList(m).hierName:= eHierName;
2172 END IF;
2173 END LOOP;
2174 END LOOP;
2175 END LOOP;
2176 END IF;
2177 END LOOP;
2178 FOR i in 1..mvList.Count LOOP
2179 eDimName:= mvList(i).dimName;
2180 eHierName:= mvList(i).hierName;
2181 FOR j in 1..dimHierList.Count LOOP
2182 IF eDimName = dimHierList(j).dimName THEN
2183 IF (eHierName = dimHierList(j).hierName OR
2184 dimHierList(j).hierName IS NULL ) THEN
2185 mvList(i).lvlCount:= dimHierList(j).levelCount;
2186 mvList(i).highestLvl:= FALSE;
2187 END IF;
2188 END IF;
2189 END LOOP;
2190 END LOOP;
2191 FOR i in 1..numOfMV LOOP
2192 highLvlCount:= 0;
2193 highLvlNum:= 1;
2194 FOR j in 1..mvList.Count LOOP
2195 IF mvList(j).mvNum = i AND
2196 mvList(j).lvlCount >= highLvlCount THEN
2197 highLvlCount:= mvList(j).lvlCount;
2198 highLvlNum:= j;
2199 END IF;
2200 END LOOP;
2201 mvList(highLvlNum).highestLvl:= TRUE;
2202 END LOOP;
2203
2204 m:= 0;
2205 -- highLvlCount:= 0;
2206 FOR i in 1..numOfMV LOOP
2207 j:= 1;
2208 FOR j in 1..mvList.Count LOOP
2209 IF mvList(j).mvNum = i AND
2213 END IF;
2210 mvList(j).highestLvl= TRUE THEN
2211 highLvlCount:= mvList(j).LvlCount;
2212 -- EXIT LOOP;
2214 END LOOP;
2215 -- END LOOP;
2216 -- WHILE j <= mvList.Count AND
2217 -- mvList(j).mvNum= i
2218 -- LOOP
2219 -- highLvlCount:= mvList(j).LvlCount;
2220 -- j:= j+ 1;
2221 -- EXIT WHEN mvList(j).highestLvl = TRUE;
2222 -- END LOOP;
2223 -- FOR j in 1..mvList.Count LOOP
2224 -- IF mvList(j).mvNum= i AND
2225 -- mvList(j).highestLvl= TRUE THEN
2226 -- highLvlNum:= mvList(j).lvlCount;
2227 -- EXIT;
2228 -- END IF;
2229 -- END LOOP;
2230 FOR k in REVERSE 0..highLvlCount LOOP
2231 FOR j in 1..mvList.Count LOOP
2232 IF mvList(j).mvNum = i AND
2233 mvList(j).lvlCount = k THEN
2234 m:= m + 1;
2235 mvListdec.EXTEND;
2236 mvListdec(m).mvNum:= i;
2237 mvListdec(m).dimName:= mvList(j).dimName;
2238 mvListdec(m).hierName:= mvList(j).hierName;
2239 mvListdec(m).lvlCount:= mvList(j).lvlCount;
2240 mvListdec(m).highestLvl:= mvList(j).highestLvl;
2241 END IF;
2242 END LOOP;
2243 END LOOP;
2244 END LOOP;
2245
2246
2247 FOR i in 1..mvListdec.Count LOOP
2248 mvListdec(i).highestLvl:= FALSE;
2249 END LOOP;
2250
2251 FOR i in 1..numOfMV LOOP
2252 highLvlCount:= 0;
2253 highLvlNum:= 1;
2254 FOR j in 1..mvListdec.Count LOOP
2255 IF mvListdec(j).mvNum = i THEN
2256 IF highLvlCount = 0 THEN
2257 highLvlCount:= mvListdec(j).lvlCount;
2258 END IF;
2259 IF mvListdec(j).lvlCount <= highLvlCount THEN
2260 highLvlCount:= mvListdec(j).lvlCount;
2261 highLvlNum:= j;
2262 END IF;
2263 END IF;
2264 END LOOP;
2265 mvListdec(highLvlNum).highestLvl:= TRUE;
2266 END LOOP;
2267 m:= 0;
2268 FOR i in 1..numOfMV LOOP
2269 j:= 1;
2270 FOR j in 1..mvList.Count LOOP
2271 IF mvList(j).mvNum = i AND
2272 mvList(j).highestLvl= TRUE THEN
2273 highLvlCount:= mvList(j).LvlCount;
2274 -- EXIT LOOP;
2275 END IF;
2276 END LOOP;
2277 -- WHILE j <= mvList.Count AND
2278 -- mvList(j).mvNum= i AND
2279 -- mvList(j).highestLvl= TRUE
2280 -- LOOP
2281 -- highLvlCount:= mvList(j).LvlCount;
2282 -- j:= j+ 1;
2283 -- END LOOP;
2284 -- FOR j in 1..mvList.Count LOOP
2285 -- IF mvList(j).mvNum= i AND
2286 -- mvList(j).highestLvl= TRUE THEN
2287 -- highLvlNum:= mvList(j).lvlCount;
2288 -- EXIT;
2289 -- END IF;
2290 -- END LOOP;
2291 FOR k in 0..highLvlCount LOOP
2292 FOR j in 1..mvList.Count LOOP
2293 IF mvList(j).mvNum = i AND
2294 mvList(j).lvlCount = k THEN
2295 m:= m + 1;
2296 mvListinc.EXTEND;
2297 mvListinc(m).mvNum:= i;
2298 mvListinc(m).dimName:= mvList(j).dimName;
2299 mvListinc(m).hierName:= mvList(j).hierName;
2300 mvListinc(m).lvlCount:= mvList(j).lvlCount;
2301 mvListinc(m).highestLvl:= mvList(j).highestLvl;
2302 END IF;
2303 END LOOP;
2304 END LOOP;
2305 END LOOP;
2306 -- INSERT INTO SYSTEM.MVIEW$_ADV_LOG(RUNID#,STATUS) VALUES
2307 -- (runid,1);
2308 FOR i in 1..numofMV LOOP
2309 --UTL_PUT(filehandle,buffer)
2310 eMVName:= SUBSTR(exCubeName,0,20) || '_' || i || '_OLAP';
2311 -- UTL_FILE.PUT_LINE(filehandle,'create materialized view');
2312 -- UTL_FILE.PUT(filehandle,eMVName);
2313 -- UTL_FILE.NEW_LINE(filehandle,1);
2314 -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s',eOwnerName);
2315 -- UTL_FILE.NEW_LINE(filehandle,1);
2316 -- UTL_FILE.PUT_LINE(filehandle,'parallel');
2317
2318 dupmvFlag:= FALSE;
2319 -- partition:= TRUE;
2320 IF partitioning = TRUE THEN
2321 partgid:= 0;
2322 partnum:= 0;
2323 o:= 0;
2324 indexString:= '';
2325 indexString:= indexString || 'partition by range (gid) (';
2326 -- UTL_FILE.PUT_LINE(filehandle,'partition by range (gid) (');
2327 FOR j in 1..mvListdec.Count LOOP
2328 IF mvListdec(j).mvNum= i THEN
2329 edimName:= mvListdec(j).dimName;
2330 ehierName:= mvListdec(j).hierName;
2331 FOR l in 1..dimHierList.Count LOOP
2332 IF eDimName = dimHierList(l).dimName AND
2333 eHierName = dimHierList(l).hierName THEN
2334 o:= o + 1;
2335 partList.EXTEND;
2336 partList(o).dimName:= dimHierList(l).dimName;
2337 partList(o).hierName:= dimHierList(l).hierName;
2341 ELSIF eDimName = dimHierList(l).dimName AND
2338 partList(o).dimNum:= dimHierList(l).dimNum;
2339 partList(o).hierNum:= dimHierList(l).hierNum;
2340 partList(o).levelCount:= dimHierList(l).levelCount;
2342 dimHierList(l).hierName IS NULL THEN
2343 o:= o + 1;
2344 partList.EXTEND;
2345 partList(o).dimName:= dimHierList(l).dimName;
2346 partList(o).hierName:= dimHierList(l).hierName;
2347 partList(o).dimNum:= dimHierList(l).dimNum;
2348 partList(o).hierNum:= dimHierList(l).hierNum;
2349 partList(o).levelCount:= dimHierList(l).levelCount;
2350
2351 END IF;
2352 END LOOP;
2353 END IF;
2354 END LOOP;
2355 times:= 1;
2356 FOR k in 1..partList.Count LOOP
2357 times:= times * partList(k).levelCount;
2358 END LOOP;
2359
2360 FOR k in 1..times LOOP
2361 parnum.EXTEND;
2362 parnum(k).gidNum:= 0;
2363 END LOOP;
2364
2365 val:= 0;
2366 ntimes:= 1;
2367 loopval:= 1;
2368 tkval:= 0;
2369 tpnum:= 0;
2370 FOR k in 1..partList.Count LOOP
2371 c:= 0;
2372 tempMVNum:= times/ partList(k).levelCount;
2373 IF k= 1 THEN
2374 FOR z in 1..tempMVNum LOOP
2375 FOR m in 1..partList(k).levelCount LOOP
2376 c:= c + 1;
2377 parnum(c).gidNum:= val;
2378 IF z <> tempMVNum THEN
2379 IF m= partList(k).levelCount THEN
2380 val:= 0;
2381 ELSE
2382 IF val= 0 THEN
2383 val:= 1;
2384 ELSE
2385 val:= (val * 2) + 1;
2386 -- tkval:= m - 1;
2387 -- tpnum:= tkval * 2;
2388 -- val:= val + tpnum;
2389 END IF;
2390 END IF;
2391 ELSE
2392 IF m = partList(k).levelCount THEN
2393 tpnum:= val + 1;
2394 val:= tpnum * 2;
2395 tkval:= val;
2396 ELSE
2397 IF val= 0 THEN
2398 val:= 1;
2399 ELSE
2400 -- tkval:= m - 1;
2401 -- tpnum:= tkval * 2;
2402 -- val:= val + tpnum;
2403 val:= (val * 2) + 1;
2404 tkval:= val;
2405 END IF;
2406 END IF;
2407 END IF;
2408 END LOOP;
2409 END LOOP;
2410 loopval:= loopval * partList(k).levelCount;
2411 ELSE
2412 ntimes:= 1;
2413 ntimes:= tempMVNum / loopval;
2414 val:= 0;
2415 -- tkval:= 0;
2416 FOR i in 1..ntimes LOOP
2417 FOR j in 1..partList(k).levelCount LOOP
2418 tempval:= loopval / partList(1).levelCount;
2419 FOR v in 1..tempval LOOP
2420 FOR h in 1..partList(1).levelCount LOOP
2421 c:= c + 1;
2422 parnum(c).gidNum:= parnum(c).gidNum + val;
2423 END LOOP;
2424 END LOOP;
2425 IF j = partList(k).levelCount THEN
2426 IF i= ntimes THEN
2427 tkval:= tpnum * 4;
2428 val:= 0;
2429 ELSE
2430 tpnum:= tkval;
2431 val:= 0;
2432 END IF;
2433 ELSE
2434 IF val= 0 THEN
2435 tpnum:= tkval;
2436 val:= val + tpnum;
2437 ELSE
2438 tpnum:= tpnum * 2;
2439 val:= val + tpnum;
2440 END IF;
2441 END IF;
2442 END LOOP;
2443 END LOOP;
2444 loopval:= loopval * partList(k).levelCount;
2445 END IF;
2446 END LOOP;
2447
2448 FOR k in 1..times LOOP
2449 IF k > 1 THEN
2450 IF k < 800 THEN
2451 indexString:= indexString || ' partition values less than(' || parnum(k).gidNum || ')' || ' ,';
2452
2453 END IF;
2454 -- UTL_FILE.PUTF(filehandle,'partition values less than(%s),',parnum(k).gidNum);
2455 -- UTL_FILE.NEW_LINE(filehandle,1);
2456 END IF;
2457 END LOOP;
2458 indexString:= indexString || ' partition values less than(MAXVALUE))';
2459 -- UTL_FILE.PUT_LINE(filehandle,'partition values less than(MAXVALUE))');
2460 partList.DELETE;
2461 parnum.DELETE;
2462 END IF;
2463 -- UTL_FILE.PUT_LINE(filehandle,'pctfree 5 pctused 40');
2464 -- IF mvspcName IS NOT NULL THEN
2465 -- UTL_FILE.PUTF(filehandle,'tablespace %s', mvspcName);
2466 -- UTL_FILE.NEW_LINE(filehandle,1);
2467 -- END IF;
2468 -- UTL_FILE.PUT_LINE(filehandle,'build immediate');
2469 -- UTL_FILE.PUT_LINE(filehandle,'using no index');
2470 -- UTL_FILE.PUT_LINE(filehandle,'refresh force');
2471 -- UTL_FILE.PUT_LINE(filehandle,'enable query rewrite');
2472 -- UTL_FILE.PUT_LINE(filehandle,'AS');
2473 -- UTL_FILE.PUT_LINE(filehandle,'SELECT');
2474 bufString:= bufString || 'SELECT ';
2475
2476 -- UTL_FILE.PUT(filehandle,'GROUPING_ID(');
2477 bufString:= bufString || 'GROUPING_ID(';
2478 FOR j in 1..mvListinc.Count LOOP
2479 IF mvListinc(j).mvNum= i THEN
2480 edimName:= mvListinc(j).dimName;
2481 ehierName:= mvListinc(j).hierName;
2482 FOR l in 1..hierLevelList.Count LOOP
2483 IF hierLevelList(l).dimName= eDimName AND
2484 hierLevelList(l).hierName= eHierName THEN
2485 eLevelName:= hierLevelList(l).levelName;
2486 IF NOT curColName%ISOPEN THEN
2487 OPEN curColName;
2488 END IF;
2489 LOOP
2490 FETCH curColName into eLevelColName,ePosition;
2491 EXIT WHEN curColName%NOTFOUND;
2492 END LOOP;
2493 CLOSE curColName;
2494 IF NOT curTabName%ISOPEN THEN
2495 OPEN curTabName;
2496 END IF;
2497 LOOP
2501 CLOSE curTabName;
2498 FETCH curTabName into eTabName;
2499 EXIT WHEN curTabName%NOTFOUND;
2500 END LOOP;
2502 -- UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
2503 --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName;
2504
2505 bufString:= bufString
2506 || dbms_assert.enquote_name(eTabName)
2507 || '.'
2508 || dbms_assert.enquote_name(eLevelColName);
2509
2510 IF hierLevelList(l).levelNum < mvListinc(j).lvlCount THEN
2511 -- UTL_FILE.PUT_LINE(filehandle,',');
2512 bufString:= bufString || ', ';
2513 END IF;
2514
2515 ELSIF hierLevelList(l).dimName = eDimName AND
2516 hierLevelList(l).hierName IS NULL THEN
2517 eLevelName:= hierLevelList(l).levelName;
2518 IF NOT curColDimName%ISOPEN THEN
2519 OPEN curColDimName;
2520 END IF;
2521 LOOP
2522 FETCH curColDimName into eLevelColName,ePosition ;
2523 EXIT WHEN curColDimName%NOTFOUND;
2524 END LOOP;
2525 CLOSE curColDimName;
2526 IF NOT curTabName%ISOPEN THEN
2527 OPEN curTabName;
2528 END IF;
2529 LOOP
2530 FETCH curTabName into eTabName;
2531 EXIT WHEN curTabName%NOTFOUND;
2532 END LOOP;
2533 CLOSE curTabName;
2534 -- UTL_FILE.PUTF(filehandle,'%s.%s', eTabName,eLevelColName);
2535 --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName;
2536
2537 bufString:= bufString
2538 || dbms_assert.enquote_name(eTabName)
2539 || '.'
2540 || dbms_assert.enquote_name(eLevelColName);
2541
2542 IF hierLevelList(l).levelNum < mvListinc(j).lvlCount THEN
2543 -- UTL_FILE.PUT_LINE(filehandle,',');
2544 bufString:= bufString || ', ';
2545 END IF;
2546 END IF;
2547 END LOOP;
2548 IF mvListinc(j).highestLvl = FALSE THEN
2549 -- UTL_FILE.PUT_LINE(filehandle,',');
2550 bufString:= bufString || ', ';
2551 END IF;
2552 END IF;
2553 END LOOP;
2554 -- UTL_FILE.PUT_LINE(filehandle,') gid,');
2555 bufString:= bufString || ') gid, ';
2556 IF NOT curUnitName%ISOPEN THEN
2557 OPEN curUnitName;
2558 LOOP
2559 FETCH curUnitName INTO eUnitName;
2560 EXIT WHEN curUnitName%NOTFOUND;
2561 -- UTL_FILE.PUTF(filehandle,'SUM(%s.%s) SUM_OF_%s,', eFactName,eUnitName,eUnitName);
2562 -- UTL_FILE.NEW_LINE(filehandle,1);
2563 -- UTL_FILE.PUTF(filehandle,'COUNT(%s.%s) COUNT_OF_%s,', eFactName,eUnitName,eUnitName);
2564 -- UTL_FILE.NEW_LINE(filehandle,1);
2565 --Bug 8915150 bufString:= bufString || 'SUM(' || eFactName || '.' || eUnitName || ') ';
2566
2567 bufString:= bufString
2568 || 'SUM('
2569 || dbms_assert.enquote_name(eFactName)
2570 || '.'
2571 || dbms_assert.enquote_name(eUnitName)
2572 || ') ';
2573
2574 --Bug 8915150 bufString:= bufString || 'SUM_OF_' || eUnitName || ', ';
2575
2576 bufString:= bufString
2577 || 'SUM_OF_'
2578 || dbms_assert.enquote_name(eUnitName)
2579 || ', ';
2580
2581 -- bufString:= bufString || 'COUNT(' || eFactName || '.' || eUnitName || ') ';
2582 -- bufString:= bufString || 'COUNT_OF_' || eUnitName || ', ';
2583 END LOOP;
2584 CLOSE CurUnitName;
2585 END IF;
2586 -- UTL_FILE.PUT_LINE(filehandle,'COUNT(*) COUNT_OF_STAR,');
2587 bufString:= bufString || 'COUNT(*) COUNT_OF_STAR, ';
2588
2589 FOR j in 1..mvListdec.Count LOOP
2590 IF mvListdec(j).mvNum= i THEN
2591 edimName:= mvListdec(j).dimName;
2592 ehierName:= mvListdec(j).hierName;
2593 FOR l in 1..hierLevelList.Count LOOP
2594 IF hierLevelList(l).dimName= eDimName AND
2595 hierLevelList(l).hierName= ehierName THEN
2596 eLevelName:= hierLevelList(l).levelName;
2597 IF NOT curColName%ISOPEN THEN
2598 OPEN curColName;
2599 END IF;
2600 LOOP
2601 FETCH curColName into eLevelColName,ePosition ;
2605 IF NOT curTabName%ISOPEN THEN
2602 EXIT WHEN curColName%NOTFOUND;
2603 END LOOP;
2604 CLOSE curColName;
2606 OPEN curTabName;
2607 END IF;
2608 LOOP
2609 FETCH curTabName into eTabName;
2610 EXIT WHEN curTabName%NOTFOUND;
2611 END LOOP;
2612 CLOSE curTabName;
2613 -- UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);
2614 -- UTL_FILE.NEW_LINE(filehandle,1);
2615
2616 --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName || '_' || l;
2617
2618 bufString:= bufString
2619 || dbms_assert.enquote_name(eTabName)
2620 || '.'
2621 || dbms_assert.enquote_name(eLevelColName)
2622 || ' '
2623 || dbms_assert.enquote_name(eLevelColName)
2624 || '_'
2625 || l;
2626
2627 IF hierLevelList(l).levelNum < mvListdec(j).lvlCount THEN
2628 -- UTL_FILE.PUT_LINE(filehandle,',');
2629 bufString:= bufString || ', ';
2630 END IF;
2631 ELSIF hierLevelList(l).dimName = eDimName AND
2632 hierLevelList(l).hierName IS NULL THEN
2633 eLevelName:= hierLevelList(l).levelName;
2634 IF NOT curColDimName%ISOPEN THEN
2635 OPEN curColDimName;
2636 END IF;
2637 LOOP
2638 FETCH curColDimName into eLevelColName,ePosition ;
2639 EXIT WHEN curColDimName%NOTFOUND;
2640 END LOOP;
2641 CLOSE curColDimName;
2642 IF NOT curTabName%ISOPEN THEN
2643 OPEN curTabName;
2644 END IF;
2645 LOOP
2646 FETCH curTabName into eTabName;
2647 EXIT WHEN curTabName%NOTFOUND;
2648 END LOOP;
2649 CLOSE curTabName;
2650 -- UTL_FILE.PUTF(filehandle,'%s.%s %s_%s', eTabName,eLevelColName, eLevelColName,l);
2651 --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ||' ' || eLevelColName || '_' || l ;
2652
2653 bufString:= bufString
2654 || dbms_assert.enquote_name(eTabName)
2655 || '.'
2656 || dbms_assert.enquote_name(eLevelColName)
2657 || ' '
2658 || dbms_assert.enquote_name(eLevelColName)
2659 || '_'
2660 || l ;
2661
2662 IF hierLevelList(l).levelNum < mvListdec(j).lvlCount THEN
2663 -- UTL_FILE.PUT_LINE(filehandle,',');
2664 bufString:= bufString || ', ';
2665 END IF;
2666 END IF;
2667 END LOOP;
2668 IF mvListdec(j).highestLvl = FALSE THEN
2669 -- UTL_FILE.PUT_LINE(filehandle,',');
2670 bufString:= bufString || ', ';
2671 END IF;
2672 END IF;
2673 END LOOP;
2674 -- UTL_FILE.NEW_LINE(filehandle,1);
2675 -- UTL_FILE.PUT_LINE(filehandle,'FROM');
2676 o:= 0;
2677 bufString:= bufString || ' FROM ';
2678 FOR j in 1..mvListinc.Count LOOP
2679 IF mvListinc(j).mvNum= i THEN
2680 edimName:= mvListinc(j).dimName;
2681 ehierName:= mvListinc(j).hierName;
2682 FOR l in 1..hierLevelList.Count LOOP
2683 IF hierLevelList(l).dimName= edimName AND
2684 hierLevelList(l).hierName= ehierName AND
2685 mvListinc(j).lvlCount= hierLevelList(l).levelNum THEN
2686 eLevelName:= hierLevelList(l).levelName;
2687 IF NOT curColName%ISOPEN THEN
2688 OPEN curColName;
2689 END IF;
2690 LOOP
2691 FETCH curColName into eLevelColName,ePosition;
2692 EXIT WHEN curColName%NOTFOUND;
2693 END LOOP;
2694 CLOSE curColName;
2695 IF NOT curTabName%ISOPEN THEN
2696 OPEN curTabName;
2697 END IF;
2698 LOOP
2699 FETCH curTabName into eTabName;
2700 EXIT WHEN curTabName%NOTFOUND;
2701 END LOOP;
2702 CLOSE curTabName;
2703 o:= o + 1;
2704 mvtableList.EXTEND;
2705 mvtableList(o).tabName:= eTabName;
2706 mvtableList(o).ownerName:= eOwnerName;
2707 -- UTL_FILE.PUTF(filehandle,'%s.%s %s,', eOwnerName,eTabName,eTabName);
2708 -- UTL_FILE.PUT_LINE(filehandle,' ');
2709 -- bufString:= bufString || eOwnerName || '.' || eTabName || ' ' || eTabName || ', ';
2710 ELSIF hierLevelList(l).dimName = eDimName AND
2711 hierLevelList(l).hierName IS NULL THEN
2715 END IF;
2712 eLevelName:= hierLevelList(l).levelName;
2713 IF NOT curColDimName%ISOPEN THEN
2714 OPEN curColDimName;
2716 LOOP
2717 FETCH curColDimName into eLevelColName,ePosition ;
2718 EXIT WHEN curColDimName%NOTFOUND;
2719 END LOOP;
2720 CLOSE curColDimName;
2721 IF NOT curTabName%ISOPEN THEN
2722 OPEN curTabName;
2723 END IF;
2724 LOOP
2725 FETCH curTabName into eTabName;
2726 EXIT WHEN curTabName%NOTFOUND;
2727 END LOOP;
2728 CLOSE curTabName;
2729 o:= o + 1;
2730 mvtableList.EXTEND;
2731 mvtableList(o).tabName:= eTabName;
2732 mvtableList(o).ownerName:= eOwnerName;
2733 END IF;
2734 END LOOP;
2735 END IF;
2736 END LOOP;
2737 FOR j in 1..mvListinc.Count LOOP
2738 IF mvListinc(j).mvNum= i THEN
2739 edimName:= mvListinc(j).dimName;
2740 ehierName:= mvListinc(j).hierName;
2741 FOR l in 1..hierLevelList.Count LOOP
2742 IF hierLevelList(l).dimName= edimName AND
2743 hierLevelList(l).hierName= ehierName THEN
2744 eLevelName:= hierLevelList(l).levelName;
2745 -- mvnum:= mvnum + 1;
2746 IF NOT curColName%ISOPEN THEN
2747 OPEN curColName;
2748 END IF;
2749 LOOP
2750 FETCH curColName into eLevelColName,ePosition ;
2751 EXIT WHEN curColName%NOTFOUND;
2752 END LOOP;
2753 CLOSE curColName;
2754 IF NOT curTabName%ISOPEN THEN
2755 OPEN curTabName;
2756 END IF;
2757 LOOP
2758 FETCH curTabName into eTabName;
2759 EXIT WHEN curTabName%NOTFOUND;
2760 END LOOP;
2761 CLOSE curTabName;
2762 parentString:= eTabName || '.' || eLevelColName;
2763 childString:= parentString;
2764 IF NOT curParentName%ISOPEN THEN
2765 OPEN curParentName;
2766 END IF;
2767 LOOP
2768 FETCH curParentName into eparLevelName,etableName;
2769 EXIT WHEN curParentName%FOUND;
2770 END LOOP;
2771 CLOSE curParentName;
2772 childString:= eparLevelName;
2773 IF childString <> parentString THEN
2774 tableCount:= mvtableList.Count;
2775 z:= 1;
2776 whereFlag:= FALSE;
2777 WHILE z <= tableCount LOOP
2778 IF mvtableList(z).tabName = eTabName THEN
2779 whereFlag:= TRUE;
2780 END IF;
2781 EXIT WHEN mvtableList(z).tabName= eTabName;
2782 z:= z + 1;
2783 END LOOP;
2784 IF whereFlag= FALSE THEN
2785 o:= o +1;
2786 mvtableList.EXTEND;
2787 mvtableList(o).tabName:= eTabName;
2788 mvtableList(o).ownerName:= eOwnerName;
2789 END IF;
2790 -- bufString:= bufString || ' AND ';
2791 -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
2792 -- bufString:= bufString || '(' || childString || '=' || parentString || ') ';
2793 END IF;
2794 ELSIF hierLevelList(l).dimName= edimName AND
2795 hierLevelList(l).hierName= NULL THEN
2796 eLevelName:= hierLevelList(l).levelName;
2797 -- mvnum:= mvnum + 1;
2798 IF NOT curColDimName%ISOPEN THEN
2799 OPEN curColDimName;
2800 END IF;
2801 LOOP
2802 FETCH curColDimName into eLevelColName,ePosition ;
2803 EXIT WHEN curColDimName%NOTFOUND;
2804 END LOOP;
2805 CLOSE curColDimName;
2806 IF NOT curTabName%ISOPEN THEN
2807 OPEN curTabName;
2808 END IF;
2809 LOOP
2810 FETCH curTabName into eTabName;
2811 EXIT WHEN curTabName%NOTFOUND;
2812 END LOOP;
2813 CLOSE curTabName;
2814 parentString:= eTabName || '.' || eLevelColName;
2815 childString:= parentString;
2816 IF NOT curParentName%ISOPEN THEN
2817 OPEN curParentName;
2818 END IF;
2819 LOOP
2820 FETCH curParentName into eparLevelName,etableName;
2821 EXIT WHEN curParentName%FOUND;
2822 END LOOP;
2823 CLOSE curParentName;
2824 childString:= eparLevelName;
2825 IF childString <> parentString THEN
2826 tableCount:= mvtableList.Count;
2827 z:= 1;
2828 whereFlag:= FALSE;
2829 WHILE z <= tableCount LOOP
2830 IF mvtableList(z).tabName = eTabName THEN
2831 whereFlag:= TRUE;
2832 END IF;
2833 EXIT WHEN mvtableList(z).tabName= eTabName;
2834 z:= z + 1;
2835 END LOOP;
2836 IF whereFlag= FALSE THEN
2837 o:= o +1;
2838 mvtableList.EXTEND;
2839 mvtableList(o).tabName:= eTabName;
2840 mvtableList(o).ownerName:= eOwnerName;
2841 END IF;
2842 -- bufString:= bufString || ' AND ';
2843 -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
2844 -- bufString:= bufString || '(' || childString || '=' || parentString || ') ';
2845 END IF;
2846 END IF;
2847 END LOOP;
2848 END IF;
2849 END LOOP;
2850 FOR o in 1..mvtableList.Count LOOP
2851 --Bug 8915150 bufString:= bufString || mvtableList(o).ownerName || '.' || mvtableList(o).tabName || ' '
2852 --Bug 8915150 || mvtableList(o).tabName || ', ';
2853
2854 bufString:= bufString
2855 || dbms_assert.enquote_name(mvtableList(o).ownerName)
2856 || '.'
2857 || dbms_assert.enquote_name(mvtableList(o).tabName)
2861 -- UTL_FILE.PUTF(filehandle,'%s.%s %s,', mvtableList(o).ownerName,mvtableList(o).tabName,
2858 || ' '
2859 || dbms_assert.enquote_name(mvtableList(o).tabName)
2860 || ', ';
2862 -- mvtableList(o).tabName);
2863 -- UTL_FILE.PUT_LINE(filehandle,' ');
2864 END LOOP;
2865 mvtableList.DELETE;
2866 -- UTL_FILE.PUTF(filehandle,'%s.%s %s',eOwnerName,eFactName,eFactName);
2867 -- UTL_FILE.PUT_LINE(filehandle,' ');
2868 -- UTL_FILE.PUT_LINE(filehandle,'WHERE');
2869 whereFlag:= FALSE;
2870 --Bug 8915150 bufString:= bufString || eOwnerName || '.' || eFactName || ' ' || eFactName || ' ';
2871
2872 bufString:= bufString
2873 || dbms_assert.enquote_name(eOwnerName)
2874 || '.'
2875 || dbms_assert.enquote_name(eFactName)
2876 || ' '
2877 || dbms_assert.enquote_name(eFactName)
2878 || ' ';
2879
2880 bufString:= bufString || ' ' || 'WHERE ';
2881 mvnum:= 0;
2882
2883 FOR j in 1..mvListinc.Count LOOP
2884 IF mvListinc(j).mvNum= i THEN
2885 edimName:= mvListinc(j).dimName;
2886 ehierName:= mvListinc(j).hierName;
2887 FOR l in 1..hierLevelList.Count LOOP
2888 IF hierLevelList(l).dimName= edimName AND
2889 hierLevelList(l).hierName= ehierName AND
2890 mvListinc(j).lvlCount= hierLevelList(l).levelNum THEN
2891 eLevelName:= hierLevelList(l).levelName;
2892 mvnum:= mvnum + 1;
2893 IF NOT curColName%ISOPEN THEN
2894 OPEN curColName;
2895 END IF;
2896 poscount:= 0;
2897 LOOP
2898 FETCH curColName into eLevelColName,ePosition;
2899 EXIT WHEN curColName%NOTFOUND;
2900 poscount:= poscount + 1;
2901 END LOOP;
2902 CLOSE curColName;
2903 IF NOT curColName%ISOPEN THEN
2904 OPEN curColName;
2905 END IF;
2906 LOOP
2907 FETCH curColName into eLevelColName,ePosition;
2908 EXIT WHEN curColName%NOTFOUND;
2909 IF NOT curTabName%ISOPEN THEN
2910 OPEN curTabName;
2911 END IF;
2912 LOOP
2913 FETCH curTabName into eTabName;
2914 EXIT WHEN curTabName%NOTFOUND;
2915 END LOOP;
2916 CLOSE curTabName;
2917 IF NOT curLeavName%ISOPEN THEN
2918 OPEN curLeavName;
2919 END IF;
2920 LOOP
2921 FETCH curLeavName into eLeavColName ;
2922 EXIT WHEN curLeavName%FOUND;
2923 END LOOP;
2924 CLOSE curLeavName;
2925 -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
2926 --Bug 8915150 bufString:= bufString || '(' || eTabName || '.' || eLevelColName || ' = ' || eFactName || '.' || eLeavColName || ') ';
2927
2928 bufString:= bufString
2929 || '('
2930 || dbms_assert.enquote_name(eTabName)
2931 || '.'
2932 || dbms_assert.enquote_name(eLevelColName)
2933 || ' = '
2934 || dbms_assert.enquote_name(eFactName)
2935 || '.'
2936 || dbms_assert.enquote_name(eLeavColName)
2937 || ') ';
2938
2939 IF ePosition < poscount THEN
2940 -- UTL_FILE.PUT_LINE(filehandle,' AND');
2941 bufString:= bufString || ' AND ';
2942 END IF;
2943 END LOOP;
2944 CLOSE curColName;
2945 ELSIF hierLevelList(l).dimName = eDimName AND
2946 hierLevelList(l).hierName IS NULL THEN
2947 eLevelName:= hierLevelList(l).levelName;
2948 mvnum:= mvnum + 1;
2949 IF NOT curColDimName%ISOPEN THEN
2950 OPEN curColDimName;
2951 END IF;
2952 poscount:= 0;
2953 LOOP
2954 FETCH curColDimName into eLevelColName,ePosition;
2955 EXIT WHEN curColDimName%NOTFOUND;
2956 poscount:= poscount + 1;
2957 END LOOP;
2958 CLOSE curColDimName;
2959 IF NOT curColDimName%ISOPEN THEN
2960 OPEN curColDimName;
2961 END IF;
2962 LOOP
2963 FETCH curColDimName into eLevelColName,ePosition ;
2964 EXIT WHEN curColDimName%NOTFOUND;
2965 IF NOT curTabName%ISOPEN THEN
2966 OPEN curTabName;
2967 END IF;
2968 LOOP
2969 FETCH curTabName into eTabName;
2970 EXIT WHEN curTabName%FOUND;
2971 END LOOP;
2972 CLOSE curTabName;
2973 IF NOT curLeavName%ISOPEN THEN
2974 OPEN curLeavName;
2975 END IF;
2976 LOOP
2977 FETCH curLeavName into eLeavColName ;
2978 EXIT WHEN curLeavName%FOUND;
2979 END LOOP;
2980 CLOSE curLeavName;
2981 -- UTL_FILE.PUT_LINE(filehandle,' AND');
2982 -- UTL_FILE.PUTF(filehandle,'(%s.%s = %s.%s)', eTabName,eLevelColName,eFactName,eLeavColName);
2983 --Bug 8915150 bufString:= bufString || '(' || eTabName || '.' || eLevelColName || ' = ' || eFactName || '.' || eLeavColName || ') ';
2984
2985 bufString:= bufString
2986 || '('
2987 || dbms_assert.enquote_name(eTabName)
2988 || '.'
2989 || dbms_assert.enquote_name(eLevelColName)
2993 || dbms_assert.enquote_name(eLeavColName)
2990 || ' = '
2991 || dbms_assert.enquote_name(eFactName)
2992 || '.'
2994 || ') ';
2995
2996 IF ePosition < poscount THEN
2997 -- UTL_FILE.PUT_LINE(filehandle,' AND');
2998 bufString:= bufString || ' AND ';
2999 END IF;
3000 END LOOP;
3001 CLOSE curColDimName;
3002 END IF;
3003 END LOOP;
3004 IF mvnum < dimList.Count THEN
3005 -- UTL_FILE.PUT_LINE(filehandle,' AND');
3006 bufString:= bufString || ' AND ';
3007 END IF;
3008 END IF;
3009 END LOOP;
3010 FOR j in 1..mvListinc.Count LOOP
3011 IF mvListinc(j).mvNum= i THEN
3012 edimName:= mvListinc(j).dimName;
3013 ehierName:= mvListinc(j).hierName;
3014 FOR l in 1..hierLevelList.Count LOOP
3015 IF hierLevelList(l).dimName= edimName AND
3016 hierLevelList(l).hierName= ehierName THEN
3017 eLevelName:= hierLevelList(l).levelName;
3018 -- mvnum:= mvnum + 1;
3019 IF NOT curColName%ISOPEN THEN
3020 OPEN curColName;
3021 END IF;
3022 LOOP
3023 FETCH curColName into eLevelColName,ePosition ;
3024 EXIT WHEN curColName%NOTFOUND;
3025 IF NOT curTabName%ISOPEN THEN
3026 OPEN curTabName;
3027 END IF;
3028 LOOP
3029 FETCH curTabName into eTabName;
3030 EXIT WHEN curTabName%NOTFOUND;
3031 END LOOP;
3032 CLOSE curTabName;
3033 parentString:= eTabName || '.' || eLevelColName;
3034 childString:= parentString;
3035 IF NOT curParentName%ISOPEN THEN
3036 OPEN curParentName;
3037 END IF;
3038 LOOP
3039 FETCH curParentName into eparLevelName,etableName ;
3040 EXIT WHEN curParentName%FOUND;
3041 END LOOP;
3042 CLOSE curParentName;
3043 childString:= eparLevelName;
3044 IF childString <> parentString THEN
3045 bufString:= bufString || ' AND ';
3046 -- UTL_FILE.PUT_LINE(filehandle,' AND');
3047 -- UTL_FILE.PUTF(filehandle,'(%s = %s) ', childString,parentString);
3048 --Bug 8915150 bufString:= bufString || '(' || childString || ' = ' || parentString || ') ';
3049
3050 bufString:= bufString
3051 || '('
3052 || dbms_assert.enquote_name(childString)
3053 || ' = '
3054 || dbms_assert.enquote_name(parentString)
3055 || ') ';
3056
3057 END IF;
3058 END LOOP;
3059 CLOSE curColName;
3060 END IF;
3061 END LOOP;
3062 END IF;
3063 END LOOP;
3064
3065 -- UTL_FILE.PUT_LINE(filehandle,' GROUP BY ');
3066 bufString:= bufString || 'GROUP BY ';
3067 FOR j in 1..mvListinc.Count LOOP
3068 IF mvListinc(j).mvNum= i THEN
3069 mvnum:= 0;
3070 edimName:= mvListinc(j).dimName;
3071 ehierName:= mvListinc(j).hierName;
3072 FOR l in 1..hierLevelList.Count LOOP
3073 IF hierLevelList(l).dimName= edimName AND
3074 hierLevelList(l).hierName= ehierName THEN
3075 eLevelName:= hierLevelList(l).levelName;
3076 mvnum:= mvnum + 1;
3077 IF NOT curColName%ISOPEN THEN
3078 OPEN curColName;
3079 END IF;
3080 poscount:= 0;
3081 LOOP
3082 FETCH curColName into eLevelColName,ePosition;
3083 EXIT WHEN curColName%NOTFOUND;
3084 poscount:= poscount + 1;
3085 END LOOP;
3086 CLOSE curColName;
3087 IF NOT curColName%ISOPEN THEN
3088 OPEN curColName;
3089 END IF;
3090 LOOP
3091 FETCH curColName INTO eLevelColName,ePosition ;
3092 EXIT WHEN curColName%NOTFOUND;
3093 IF NOT curTabName%ISOPEN THEN
3094 OPEN curTabName;
3095 END IF;
3096 LOOP
3097 FETCH curTabName INTO eTabName;
3098 EXIT WHEN curTabName%NOTFOUND;
3099 END LOOP;
3100 CLOSE curTabName;
3101
3102 IF ePosition < poscount AND ePosition = 1 THEN
3103 bufString:= bufString || '(';
3104 -- UTL_FILE.PUT(filehandle,'(');
3105 ELSIF ePosition <= poscount AND ePosition <> 1 THEN
3106 bufString:= bufString || ', ';
3107 -- UTL_FILE.PUT(filehandle,', ');
3108 END IF;
3109 -- UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
3110 --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ;
3111
3112 bufString:= bufString
3113 || dbms_assert.enquote_name(eTabName)
3114 || '.'
3115 || dbms_assert.enquote_name(eLevelColName) ;
3116
3117 END LOOP;
3118 CLOSE curColName;
3119 IF ePosition <> 1 THEN
3120 bufString:= bufString || ') ';
3121 -- UTL_FILE.PUT(filehandle,')');
3122 END IF;
3123 IF mvListinc(j).lvlCount > mvnum AND mvnum = 1 THEN
3127 -- UTL_FILE.PUT_LINE(filehandle,', ');
3124 -- UTL_FILE.PUT(filehandle,', ROLLUP( ');
3125 bufString:= bufString || ', ROLLUP( ';
3126 ELSIF mvListinc(j).lvlCount > mvnum AND mvnum > 1 THEN
3128 bufString:= bufString || ', ';
3129 ELSIF mvListinc(j).lvlCount= mvnum THEN
3130 -- UTL_FILE.PUT(filehandle,') ');
3131 bufString:= bufString || ') ';
3132 END IF;
3133 ELSIF hierLevelList(l).dimName = eDimName AND
3134 hierLevelList(l).hierName IS NULL THEN
3135 eLevelName:= hierLevelList(l).levelName;
3136 mvnum:= mvnum + 1;
3137 IF NOT curColDimName%ISOPEN THEN
3138 OPEN curColDimName;
3139 END IF;
3140 poscount:= 0;
3141 LOOP
3142 FETCH curColDimName into eLevelColName,ePosition;
3143 EXIT WHEN curColDimName%NOTFOUND;
3144 poscount:= poscount + 1;
3145 END LOOP;
3146 CLOSE curColDimName;
3147 IF NOT curColDimName%ISOPEN THEN
3148 OPEN curColDimName;
3149 END IF;
3150 LOOP
3151 FETCH curColDimName into eLevelColName,ePosition ;
3152 EXIT WHEN curColDimName%NOTFOUND;
3153 IF NOT curTabName%ISOPEN THEN
3154 OPEN curTabName;
3155 END IF;
3156 LOOP
3157 FETCH curTabName into eTabName;
3158 EXIT WHEN curTabName%FOUND;
3159 END LOOP;
3160 CLOSE curTabName;
3161 --Bug 8915150 bufString:= bufString || eTabName || '.' || eLevelColName ;
3162
3163 bufString:= bufString
3164 || dbms_assert.enquote_name(eTabName)
3165 || '.'
3166 || dbms_assert.enquote_name(eLevelColName) ;
3167
3168 -- IF ePosition < poscount AND ePosition <> 1 THEN
3169 -- bufString:= bufString || '(';
3170 -- UTL_FILE.PUT(filehandle,'(');
3171 -- ELSIF ePosition <= poscount THEN
3172 -- bufString:= bufString || ', ';
3173 -- UTL_FILE.PUT(filehandle,',');
3174 -- END IF;
3175 -- UTL_FILE.PUTF(filehandle,'%s.%s ', eTabName,eLevelColName);
3176
3177 END LOOP;
3178 CLOSE curColDimName;
3179 IF ePosition <> 1 THEN
3180 -- UTL_FILE.PUT(filehandle,' )');
3181 bufString:= bufString || ')';
3182 END IF;
3183 END IF;
3184 END LOOP;
3185 IF mvListinc(j).highestLvl = FALSE THEN
3186 -- UTL_FILE.PUT(filehandle,', ');
3187 bufString:= bufString || ', ';
3188 -- ELSE
3189 -- UTL_FILE.PUT(filehandle,';');
3190 -- bufString:= bufString || ';';
3191 END IF;
3192 END IF;
3193 END LOOP;
3194 eTempMV:= 'aaa';
3195 num_bytes:= 0;
3196 query_length:= LENGTH(bufString);
3197 IF NOT checkName%ISOPEN THEN
3198 OPEN checkName;
3199 END IF;
3200 LOOP
3201 FETCH checkName into mvString;
3202 IF bufString = mvString THEN
3203 EXIT ;
3204 ELSE
3205 DBMS_SUMMARY.estimate_mview_size(eTempMV,bufString,num_rows,num_bytes);
3206 INSERT INTO SYSTEM.MVIEW$_ADV_OUTPUT(RUNID#,OUTPUT_TYPE,RANK#,SUMMARY_OWNER,
3207 QUERY_TEXT,STORAGE_IN_BYTES,BENEFIT_TO_COST_RATIO) VALUES
3208 (runid,0,i,eOwnerName,bufString,num_bytes,0);
3209
3210 INSERT INTO SYSTEM.MVIEW$_ADV_PARTITION(runid#,rank#,summary_owner,query_text) VALUES
3211 (runId,i,eOwnerName,indexString);
3212 dupmvFlag:= TRUE;
3213
3214 indexString:= '';
3215 END IF;
3216 EXIT WHEN checkName%NOTFOUND;
3217 END LOOP;
3218 CLOSE checkName;
3219
3220 -- UTL_FILE.NEW_LINE(filehandle,2);
3221 -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3222 -- eOwnerName);
3223 -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3224 -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all columns size 1 for columns size 254 GID'' , granularity=>''GLOBAL'') ;');
3225 -- UTL_FILE.NEW_LINE(filehandle,1);
3226 -- UTL_FILE.NEW_LINE(filehandle,2);
3227 -- UTL_FILE.PUTF(filehandle,'ALTER TABLE %s MINIMIZE RECORDS_PER_BLOCK ; ',eMVName);
3228
3229 eTempMV:= '';
3230 bufString:= '';
3231 -- UTL_FILE.NEW_LINE(filehandle,1);
3232 mvnum:= 0;
3233 tpnum:= 0;
3234 FOR j in 1..mvListinc.Count LOOP
3235 IF mvListinc(j).mvNum= i THEN
3236 edimName:= mvListinc(j).dimName;
3237 ehierName:= mvListinc(j).hierName;
3238 FOR l in 1..hierLevelList.Count LOOP
3239 IF hierLevelList(l).dimName= eDimName AND
3240 hierLevelList(l).hierName= eHierName THEN
3241 eLevelName:= hierLevelList(l).levelName;
3242 IF NOT curColName%ISOPEN THEN
3243 OPEN curColName;
3244 END IF;
3245 LOOP
3246 FETCH curColName into eLevelColName,ePosition ;
3247 EXIT WHEN curColName%NOTFOUND;
3248 END LOOP;
3249 CLOSE curColName;
3250 IF NOT curTabName%ISOPEN THEN
3251 OPEN curTabName;
3252 END IF;
3253 LOOP
3254 FETCH curTabName into eTabName;
3255 EXIT WHEN curTabName%NOTFOUND;
3256 END LOOP;
3257 CLOSE curTabName;
3258 tpnum:= j * l;
3259 IF hierLevelList(l).levelNum <> 1 THEN
3260 -- UTL_FILE.NEW_LINE(filehandle,2);
3264 --Bug 8915150 bufString:= bufString || 'BI_' || SUBSTR(exCubeName,0,5) || SUBSTR(eLevelColName,0,5) ||SUBSTR(eLevelColName,7,5) || SUBSTR(eLevelColName,-3,5) || '_' || tpnum;
3261 -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BI_%s%s%s%s_%s',SUBSTR(exCubeName,0,5),SUBSTR(eLevelColName,0,5),SUBSTR(eLevelColName,7,5),SUBSTR(eLevelColName,-3,5),tpnum);
3262 -- UTL_FILE.PUTF(filehandle,'_%s ON %s(%s_%s)',i,eMVName,eLevelColName,l);
3263
3265
3266 bufString:= bufString
3267 || 'BI_'
3268 || SUBSTR(dbms_assert.enquote_name(exCubeName),0,5)
3269 || SUBSTR(dbms_assert.enquote_name(eLevelColName),0,5)
3270 || SUBSTR(dbms_assert.enquote_name(eLevelColName),7,5)
3271 || SUBSTR(dbms_assert.enquote_name(eLevelColName),-3,5)
3272 || '_'
3273 || tpnum;
3274
3275 eLevelColName:= eLevelColName || '_' || l;
3276 -- UTL_FILE.NEW_LINE(filehandle,1);
3277 -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3278 -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3279 -- IF indspcName IS NOT NULL THEN
3280 -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3281 -- UTL_FILE.NEW_LINE(filehandle,1);
3282 -- END IF;
3283 -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3284 -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3285 -- UTL_FILE.NEW_LINE(filehandle,2);
3286 INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
3287 SUMMARY_OWNER) VALUES
3288 (runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);
3289 END IF;
3290 mvnum:= mvnum + 1;
3291 -- bufString:= bufString || 'BMPIDX_' || eLevelColName;
3292 -- bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
3293 -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';
3294 -- bufString:= bufString || 'PARALLEL NOLOGGING; ';
3295 -- IF dupmvFlag= TRUE THEN
3296 -- INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
3297 -- SUMMARY_OWNER) VALUES
3298 -- (runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);
3299 -- END IF;
3300 bufString:= '';
3301 ELSIF hierLevelList(l).dimName = eDimName AND
3302 hierLevelList(l).hierName IS NULL THEN
3303 eLevelName:= hierLevelList(l).levelName;
3304 mvnum:= mvnum + 1;
3305 IF NOT curColDimName%ISOPEN THEN
3306 OPEN curColDimName;
3307 END IF;
3308 LOOP
3309 FETCH curColDimName into eLevelColName,ePosition ;
3310 EXIT WHEN curColDimName%NOTFOUND;
3311 END LOOP;
3312 CLOSE curColDimName;
3313 IF NOT curTabName%ISOPEN THEN
3314 OPEN curTabName;
3315 END IF;
3316 LOOP
3317 FETCH curTabName into eTabName;
3318 EXIT WHEN curTabName%NOTFOUND;
3319 END LOOP;
3320 CLOSE curTabName;
3321 -- bufString:= bufString || 'BMPIDX_' || eLevelColName;
3322 IF hierLevelList(l).levelNum <> 1 THEN
3323 -- UTL_FILE.NEW_LINE(filehandle,2);
3324 --Bug 8915150 bufString:= bufString || 'BI_' || SUBSTR(exCubeName,0,5) || SUBSTR(eLevelColName,0,5) ||SUBSTR(eLevelColName,7,5) || SUBSTR(eLevelColName,-3,5) || '_' || tpnum;
3325
3326 bufString:= bufString
3327 || 'BI_'
3328 || SUBSTR(dbms_assert.enquote_name(exCubeName),0,5)
3329 || SUBSTR(dbms_assert.enquote_name(eLevelColName),0,5)
3330 || SUBSTR(dbms_assert.enquote_name(eLevelColName),7,5)
3331 || SUBSTR(dbms_assert.enquote_name(eLevelColName),-3,5)
3332 || '_'
3333 || tpnum;
3334
3335 eLevelColName:= eLevelColName || '_' || l;
3336 -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMPIDX_%s_%s ON %s(%s)',eLevelColName,i,eMVName,eLevelColName);
3337 -- UTL_FILE.NEW_LINE(filehandle,1);
3338 -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3339 -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3340 -- IF indspcName IS NOT NULL THEN
3341 -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3342 -- UTL_FILE.NEW_LINE(filehandle,1);
3343 -- END IF;
3344 -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3345 -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3346 -- UTL_FILE.NEW_LINE(filehandle,2);
3347 INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
3348 SUMMARY_OWNER) VALUES
3349 (runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);
3350
3351 END IF;
3352 -- bufString:= bufString || ' ON ' || eMVName || i || '(' || eLevelColName || ') ';
3353 -- bufString:= bufString || 'TABLESPACE TS_INDEX_01 LOCAL COMPUTE STATISTICS';
3354 -- bufString:= bufString || 'PARALLEL NOLOGGING; ';
3355 -- IF dupmvFlag= TRUE THEN
3356
3357 -- INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
3358 -- SUMMARY_OWNER) VALUES
3359 -- (runid,i + 100,mvnum,0,bufString,eLevelColName,eOwnerName);
3360 -- INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,MVINDEX#,INDEX_TYPE,INDEX_NAME,COLUMN_NAME,
3361 -- SUMMARY_OWNER) VALUES
3362 -- (runid,i,mvnum,0,bufString,eLevelColName,eOwnerName);
3363 -- END IF;
3364 bufString:= '';
3365 END IF;
3366 END LOOP;
3367 END IF;
3368 END LOOP;
3369
3370 -- INSERT INTO SYSTEM.MVIEW$_ADV_INDEX(RUNID#,RANK#,SUMMARY_OWNER,
3371 -- INDEX_TEXT) VALUES
3372 -- (runid,i,eOwnerName,bufString);
3373
3374 o:= 0;
3375 FOR j in 1..mvListinc.Count LOOP
3376 IF mvListinc(j).mvNum= i THEN
3377 edimName:= mvListinc(j).dimName;
3378 ehierName:= mvListinc(j).hierName;
3379 FOR l in 1..dimHierList.Count LOOP
3380 IF eDimName = dimHierList(l).dimName AND
3381 eHierName = dimHierList(l).hierName THEN
3382 o:= o + 1;
3383 partList.EXTEND;
3384 partList(o).dimName:= dimHierList(l).dimName;
3385 partList(o).hierName:= dimHierList(l).hierName;
3386 partList(o).dimNum:= dimHierList(l).dimNum;
3387 partList(o).hierNum:= dimHierList(l).hierNum;
3388 partList(o).levelCount:= dimHierList(l).levelCount;
3389 ELSIF eDimName = dimHierList(l).dimName AND
3390 dimHierList(l).hierName IS NULL THEN
3391 o:= o + 1;
3392 partList.EXTEND;
3393 partList(o).dimName:= dimHierList(l).dimName;
3394 partList(o).hierName:= dimHierList(l).hierName;
3395 partList(o).dimNum:= dimHierList(l).dimNum;
3396 partList(o).hierNum:= dimHierList(l).hierNum;
3397 partList(o).levelCount:= dimHierList(l).levelCount;
3398
3399 END IF;
3400 END LOOP;
3401 END IF;
3402 END LOOP;
3403 times:= 0;
3404 FOR k in 1..partList.Count LOOP
3405 times:= times + partList(k).levelCount;
3406 END LOOP;
3407
3408 FOR j in 1..partList.Count LOOP
3409 edimName:= partList(j).dimName;
3410 ehierName:= partList(j).hierName;
3411 m:= 0;
3412
3413 tpnum:= j * i;
3414 -- UTL_FILE.NEW_LINE(filehandle,2);
3415 -- UTL_FILE.PUTF(filehandle,'CREATE BITMAP INDEX BMI_%s_GID_FI_%s_%s ON %s',eDimName,tpnum,i,eMVName);
3416 -- UTL_FILE.NEW_LINE(filehandle,1);
3417 -- UTL_FILE.PUT_LINE(filehandle,' ( ');
3418 -- UTL_FILE.PUT_LINE(filehandle,' bin_to_num ');
3419 -- UTL_FILE.PUT_LINE(filehandle,' ( ');
3420 FOR l in 1..hierLevelList.Count LOOP
3421 IF hierLevelList(l).dimName= eDimName AND
3422 hierLevelList(l).hierName= eHierName THEN
3423 eLevelName:= hierLevelList(l).levelName;
3424 IF NOT curColName%ISOPEN THEN
3425 OPEN curColName;
3426 END IF;
3427 LOOP
3428 FETCH curColName into eLevelColName,ePosition ;
3429 EXIT WHEN curColName%NOTFOUND;
3430 END LOOP;
3431 CLOSE curColName;
3432 IF NOT curTabName%ISOPEN THEN
3433 OPEN curTabName;
3434 END IF;
3435 LOOP
3436 FETCH curTabName into eTabName;
3437 EXIT WHEN curTabName%NOTFOUND;
3438 END LOOP;
3439 CLOSE curTabName;
3440 times:= times - 1;
3441 m:= m + 1;
3442 -- UTL_FILE.PUTF(filehandle,' sys_op_vecbit(sys_op_numtoraw(gid),%s)',times);
3443 -- IF m < partList(j).levelCount THEN
3444
3445 -- UTL_FILE.PUT(filehandle,',');
3446 -- END IF;
3447 -- UTL_FILE.PUTF(filehandle,' /* grouping(%s) */ ', eLevelColName);
3448 -- UTL_FILE.NEW_LINE(filehandle,1);
3449 END IF;
3450 END LOOP;
3451 -- UTL_FILE.PUT_LINE(filehandle,' ) ');
3452 -- UTL_FILE.PUT_LINE(filehandle,' ) ');
3453 -- UTL_FILE.PUT_LINE(filehandle,'LOCAL');
3454 -- UTL_FILE.PUT_LINE(filehandle,'COMPUTE STATISTICS');
3455 -- IF indspcName IS NOT NULL THEN
3456 -- UTL_FILE.PUTF(filehandle,'TABLESPACE %s', indspcName);
3457 -- UTL_FILE.NEW_LINE(filehandle,1);
3458 -- END IF;
3459 -- UTL_FILE.PUT_LINE(filehandle,'PARALLEL PCTFREE 0');
3460 -- UTL_FILE.PUT_LINE(filehandle,'NOLOGGING;');
3461 -- UTL_FILE.NEW_LINE(filehandle,2);
3462 END LOOP;
3463 partList.DELETE;
3464 -- UTL_FILE.NEW_LINE(filehandle,2);
3465 -- UTL_FILE.PUTF(filehandle,'execute dbms_stats.gather_table_stats(''%s'', ',
3466 -- eOwnerName);
3467 -- UTL_FILE.PUTF(filehandle,'''%s'', ',eMVName);
3468 -- UTL_FILE.PUT_LINE(filehandle,' degree=>dbms_stats.default_degree, estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''for all hidden columns size 254'' , granularity=>''GLOBAL'') ;');
3469 -- UTL_FILE.NEW_LINE(filehandle,1);
3470
3471 END LOOP;
3472
3473
3474 -- commit;
3475 -- UTL_FILE.FCLOSE(filehandle);
3476 -- EXCEPTION
3477 -- WHEN others THEN
3478 -- UTL_FILE.PUT('others');
3479
3480 --END ;
3481
3482 commit;
3483 EXCEPTION
3484 -- WHEN UTL_FILE.INVALID_PATH THEN
3485 -- UTL_FILE.FCLOSE(filehandle);
3486 -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Path');
3487 -- WHEN UTL_FILE.INVALID_OPERATION THEN
3488 -- UTL_FILE.FCLOSE(filehandle);
3489 -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid Operation');
3490 -- WHEN UTL_FILE.INVALID_FILEHANDLE THEN
3491 -- UTL_FILE.FCLOSE(filehandle);
3492 -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Invalid File Handle');
3493 -- WHEN UTL_FILE.WRITE_ERROR THEN
3494 -- UTL_FILE.FCLOSE(filehandle);
3495 -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Write Error');
3496 -- WHEN UTL_FILE.INTERNAL_ERROR THEN
3497 -- UTL_FILE.FCLOSE(filehandle);
3498 -- DBMS_OUTPUT.PUT_LINE('OlapMaterialView.CreateFatMVTable: Internal Error');
3499 WHEN others THEN
3500 -- UTL_FILE.FCLOSE(filehandle);
3501 RAISE;
3502 END ;
3503
3504 END;