Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\reposasst\SnapshotTrigger.pkb

CREATE OR REPLACE PACKAGE BODY SnapshotTrigger AS

PROCEDURE syncAssociationTables IS
    changelogRows NUMBER := 0;
    BEGIN
      /*
       * Delete the element ids in changelog from the association tables
       *
       * Performance: Up to OWB 10g R2 (Paris), due to weaknesses in SQL optimizer
       * and lots of indexes on interlink, intralink, and pctree, we needed to
       * help the optimizer in case analyze stats were not present with these hints:
       *
       *    pctree    p: use_nl(p) index(p, idx_pctree_childid)
       *    intralink i: use_nl(i) index(i, idx_intralink_linkfrom)
       *    interlink i: use_nl(i) index(i, idx_interlink_linkfrom)
       *
       * but for OWB 11g releases (Tokyo and up), we will rely on dbms_stats
       *
       * Performance: Calling this as part of MCMService.afterPersist processing
       * is common, but deletion is much less common.  Avoid any locking unless
       * the population of changelog is greater than 0.
       */
      select count(1) into changelogRows from changelog;

if (changelogRows >= 1) then
        delete /*+ index(allpctree allpctree_pk) */ from allpctree 
        where (workspaceid, childid) in 
          (select /*+ result_cache */ workspaceid, elementid from changelog where rownum>0);
        delete /*+ index(allintralink AllIntraLink_PK) */ from allintralink
        where (workspaceid, linkfrom) in
          (select workspaceid, elementid from changelog where rownum>0);
        delete /*+ index(allinterlink AllInterLink_PK) */ from allinterlink
        where (workspaceid, linkfrom) in
          (select workspaceid, elementid from changelog where rownum>0);
        delete from changelog;
      end if;
    END syncAssociationTables;

END SnapshotTrigger;
/

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de