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