1 package body edw_alter_indexes AS
2 /* $Header: EDWINDXB.pls 115.6 2004/02/13 05:11:06 smulye noship $ */
3
4 g_file utl_file.file_type;
5 OUT number := 0;
6 LOG number := 1;
7 BOTH number :=2;
8
9 /*-------------------------------------------------------------------
10
11 Write to the log or out files.
12
13 --------------------------------------------------------------------*/
14
15 Procedure writelog( l_text varchar2) IS
16
17 BEGIN
18 fnd_file.put_line(fnd_file.log, l_text);
19 END;
20
21 /*-------------------------------------------------------------------
22
23 Given a table name, get the schema.
24
25 --------------------------------------------------------------------*/
26
27 FUNCTION getTableSchema(tableName in varchar2) RETURN varchar2 IS
28
29
30 Type CurTyp is Ref Cursor;stmt varchar2(100) := 'select table_owner from user_synonyms where table_name = :s1';
31 schema varchar2(100):= null;
32 cv CurTyp;
33 BEGIN
34 open cv for stmt using tableName;
35 fetch cv into schema;
36 close cv;
37 return schema;
38 END;
39
40
41 /*-------------------------------------------------------------------
42
43 Given a table name and owner, return all the indexes.
44
45 --------------------------------------------------------------------*/
46
47
48 FUNCTION getIndexes(tableName in varchar2, schema in varchar2)
49 RETURN tab_indexes IS
50
51 stmt varchar2(400) := ' SELECT column_name, index_name FROM all_ind_columns '||
52 ' where table_name = :s1 AND table_owner= :s2';
53
54 indexList tab_indexes;
55 ind indexInfo;
56 Type CurTyp is Ref Cursor;
57 cv CurTyp;
58 counter number :=0;
59
60 BEGIN
61
62 OPEN cv FOR stmt USING tableName, schema;
63 LOOP
64 FETCH cv into ind.columnName, ind.indexName;
65 EXIT WHEN cv%NOTFOUND;
66
67 indexList(counter).columnName := ind.columnName;
68 indexList(counter).indexName := ind.indexName;
69 counter := counter + 1;
70 END LOOP;
71 return indexList;
72 END;
73
74
75 /*-------------------------------------------------------------------
76
77 Given a index_name , table_owner, get the status of the index.
78 Statuses are
79
80 --------------------------------------------------------------------*/
81 FUNCTION getIndexStatus(p_index in varchar2, p_table in varchar2, p_owner in varchar2) return VARCHAR2 IS
82 l_status varchar2(20);
83
84 l_stmt varchar2(200) :=
85 'SELECT status
86 FROM all_indexes
87 WHERE table_name = :s1 and
88 table_owner = :s2 and index_name = :s3';
89
90 Type CurTyp is Ref Cursor;
91 cv CurTyp;
92 BEGIN
93 utl_file.put_line(g_file, 'Inside getIndexStatus for :'||p_index);
94
95 OPEN cv FOR l_stmt USING p_table, p_owner, p_index;
96 FETCH cv into l_status;
97 CLOSE cv;
98 utl_file.put_line(g_file, 'Completed getIndexStatus for :'||p_index||' : '||l_status);
99 RETURN l_status;
100
101 END;
102
103 FUNCTION getFactFKS(p_fact_name in varchar2) RETURN tab_fact_fks IS
104 tabfks tab_fact_fks;
105 fk c_fact_fks%ROWTYPE;
106 counter number :=0;
107 BEGIN
108 OPEN c_fact_fks(p_fact_name);
109 LOOP
110 FETCH c_fact_fks INTO fk;
111 EXIT WHEN c_fact_fks%NOTFOUND;
112 tabfks(counter).name := fk.name;
113 tabfks(counter).skip := fk.skip;
114 counter := counter + 1;
115 END LOOP;
116 CLOSE c_fact_fks;
117 return tabfks;
118 END;
119
120
121 FUNCTION getIndexForFK(p_fk_name in varchar2) return varchar2 IS
122
123 counter number;
124 indexName varchar2(30) := null;
125
126 BEGIN
127
128 utl_file.put_line(g_file, 'Inside getIndexForFK for :'||p_fk_name);
129
130 counter := g_indexes.first;
131
132 LOOP
133 EXIT WHEN (NOT g_indexes.exists(counter));
134
135 IF (g_indexes(counter).columnName = p_fk_name) THEN
136 indexName := g_indexes(counter).indexName;
137 --g_indexes.delete(counter);
138 EXIT;
139 END IF;
140 counter := counter + 1;
141 END LOOP;
142 utl_file.put_line(g_file, 'Completed getIndexForFK for :'||p_fk_name || ' returning '||indexName);
143
144 return indexName;
145
146 END;
147 /*-------------------------------------------------------------------
148
149 Disable/enable indexes based on settings changed using
150 Apps Integrator.
151 --------------------------------------------------------------------*/
152
153 Procedure alterIndexesforFact(p_fact_name in varchar2) IS
154
155 fkCounter number := 0;
156 itemSetCounter number :=0;
157 schema varchar2(30);
158
159 l_fact_fks tab_fact_fks;
160 l_stmt varchar2(100);
161 l_index varchar2(100);
162 BEGIN
163 schema := getTableSchema(p_fact_name);
164 l_fact_fks := getFactFKs(p_fact_name);
165 g_indexes := getIndexes(p_fact_name, schema);
166 fkCounter := l_fact_fks.first;
167
168 writelog( 'Inside alterIndexesForFact for :'||p_fact_name);
169 writelog( 'Altering indexes for '||p_fact_name);
170
171
172
173 LOOP
174
175 EXIT WHEN (NOT l_fact_fks.exists(fkCounter));
176
177 l_index := getIndexForFK(l_fact_fks(fkCounter).name);
178
179
180 IF (l_index is not null) THEN
181 IF(l_fact_fks(fkCounter).skip = 'Y') THEN
182 /* index should be unusable */
183
184 IF (getIndexStatus(l_index, p_fact_name, schema) <> 'UNUSABLE') THEN
185 writelog( ' Status for '||l_index||
186 ' is not unusable... Altering index to make it unusable');
187 l_stmt := 'ALTER INDEX '||schema||'.'||l_index||' UNUSABLE';
188 writelog( l_stmt);
189 execute immediate l_stmt;
190 END IF;
191 ELSE /* Index should be valid */
192 IF (getIndexStatus(l_index, p_fact_name, schema) <> 'VALID') THEN
193 writelog( ' Status for '||l_index||
194 ' is not valid... Rebuilding index to make it valid');
195
196 l_stmt := 'ALTER INDEX '||schema||'.'||l_index||' REBUILD';
197 writelog(l_stmt);
198 execute immediate l_stmt;
199 END IF;
200 END IF;
201 END IF;
202
203 fkCounter := fkCounter + 1;
204 END LOOP;
205
206 END;
207
208
209 /*-------------------------------------------------------------------
210
211 This API is called from the concurrent program. If fact name
212 is passed then alter indexes for this fact. Else alter indexes
213 for all facts.
214
215 --------------------------------------------------------------------*/
216
217 Procedure alterIndexes(errbuf in varchar2, retcode in number,
218 p_fact_name in varchar2 default null) IS
219 cursor c_getFacts is
220 SELECT distinct object_short_name from edw_attribute_properties
221 where nvl(level_name, 'null') = 'null';
222 l_fact varchar2(50);
223 l_dir varchar2(100);
224
225 BEGIN
226
227 /*
228 l_dir:=fnd_profile.value('EDW_LOGFILE_DIR');
229
230 IF l_dir is null THEN
231 l_dir:='/sqlcom/log';
232 END IF;
233
234 FND_FILE.put_names(p_fact_name||'.log',p_fact_name||'.out',l_dir);
235
236 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Alter Indexes for Configured Objects');
237
238
239 OPEN c_getFacts;
240 LOOP
241 fetch c_getFacts into l_fact;
242 EXIT WHEN c_getFacts%NOTFOUND;
243
244 alterIndexesForFact(l_fact);
245 END LOOP;
246
247 CLOSE c_getFacts;
248 utl_file.fclose(g_file);
249
250 */
251
252 null;
253
254 END;
255
256 end edw_alter_indexes;
[Home] [Help]