[Home] [Help]
19: NSINGHAI 14-JAN-2005 Modifying hints after review of perf team(Bug 3951752)
20: NSINGHAI 20-JAN-2005 Modified exception handling to return ERROR (retcode=2)
21: instead of WARNING (retcode=1) (Bug 4099782)
22: NSINGHAI 29-SEP-2005 Bug 4642145: removing APPEND from 2nd INSERT stmt of
23: JTF_RS_GRP_DEN_LTST_REL_2_TMP table because
24: of ORA-08176 error in GSI env. Doing it after
25: confirming with APPS PERF team (29-SEP-2005)
26: RGOKAVAR 13-AUG-2008 Bug 6800249: Changing Insert statement into
27: jtf_rs_grp_den_ltst_rel_1_tmp Table to Improve
179: -- so it becomes difficult to rollup their cost. So we need to find
180: -- the last group to which they reported.
181:
182: -- before that truncate the table since it has unique index
183: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_jtfu||'.JTF_RS_GRP_DEN_LTST_REL_2_TMP';
184: -- DELETE FROM JTF_RS_GRP_DEN_LTST_REL_2_TMP;
185:
186: INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_grp_den_ltst_rel_2_tmp a
187: (denorm_grp_id)
180: -- the last group to which they reported.
181:
182: -- before that truncate the table since it has unique index
183: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_jtfu||'.JTF_RS_GRP_DEN_LTST_REL_2_TMP';
184: -- DELETE FROM JTF_RS_GRP_DEN_LTST_REL_2_TMP;
185:
186: INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_grp_den_ltst_rel_2_tmp a
187: (denorm_grp_id)
188: SELECT /*+ use_hash(x den2 den3) PARALLEL(x) PARALLEL(den2) PARALLEL(den3)*/
182: -- before that truncate the table since it has unique index
183: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_jtfu||'.JTF_RS_GRP_DEN_LTST_REL_2_TMP';
184: -- DELETE FROM JTF_RS_GRP_DEN_LTST_REL_2_TMP;
185:
186: INSERT /*+ APPEND PARALLEL(a) NOLOGGING */ INTO jtf_rs_grp_den_ltst_rel_2_tmp a
187: (denorm_grp_id)
188: SELECT /*+ use_hash(x den2 den3) PARALLEL(x) PARALLEL(den2) PARALLEL(den3)*/
189: distinct den3.denorm_grp_id
190: FROM
246:
247: -- Bug 4642145: removing APPEND from INSERT stmt because of ORA-08176 error in GSI env.
248: -- doing it after confirming with APPS PERF team (29-SEP-2005)
249:
250: INSERT /*+ PARALLEL(a) NOLOGGING */ INTO jtf_rs_grp_den_ltst_rel_2_tmp a
251: (a.denorm_grp_id)
252: SELECT /*+ PARALLEL(den) */
253: den.denorm_grp_id
254: FROM jtf_rs_groups_denorm den
253: den.denorm_grp_id
254: FROM jtf_rs_groups_denorm den
255: WHERE den.denorm_level = 0
256: AND den.latest_relationship_flag IS NULL
257: AND NOT EXISTS (SELECT 1 FROM jtf_rs_grp_den_ltst_rel_2_tmp tmp
258: WHERE tmp.denorm_grp_id = den.denorm_grp_id)
259: ;
260:
261: COMMIT;
263: -- check if there is any data to be updated
264: BEGIN
265: SELECT 'Y'
266: INTO l_data_to_update
267: FROM jtf_rs_grp_den_ltst_rel_2_tmp
268: WHERE ROWNUM = 1;
269: EXCEPTION
270: WHEN NO_DATA_FOUND
271: THEN NULL;
273:
274: -- commenting out code on 02/18/2004 because 8i does not support gathering stats
275: -- on Temporary Tables.
276: --FND_STATS.GATHER_TABLE_STATS(ownname=>'JTF'
277: -- ,tabname=>'JTF_RS_GRP_DEN_LTST_REL_2_TMP'
278: -- );
279:
280: -- do the real update of table if anything is there to be updated
281: -- have put this check to avoid unnecessary update operation improve performance.
281: -- have put this check to avoid unnecessary update operation improve performance.
282: IF (l_data_to_update IS NOT NULL) THEN
283:
284: UPDATE ( SELECT /*+ PARALLEL(gd) PARALLEL(x) */ gd.latest_relationship_flag
285: FROM jtf_rs_groups_denorm gd, jtf_rs_grp_den_ltst_rel_2_tmp x
286: WHERE x.denorm_grp_id = gd.denorm_grp_id
287: )
288: SET latest_relationship_flag = 'Y'
289: ;