Updating a list from a similar list from another schema

From: cschang <cschang_at_maxinter.net>
Date: Mon, 28 Jun 2004 23:02:19 -0400
Message-ID: <40E0DBBB.1050608@maxinter.net>

I have two database schema installed on separate machines (both 8.1.7 on NT 4 with 2 CPU)
I want to update a list ( ~ 10K rows) of a table on the schema A with a more detailed list (~ 7M rows )on the schema B. First I created a database link from A to B, then wrote a procedure to do the job. Basically, first I created procedure in schema B to produce a varchar2 datatype object to contain the detail from the schema B and passing the object list to the schema A for updating by the procedure below. However, the procedure is very slow. It took about 3 minutes to finish about 6k rows for the schema A. I noticed that from the task manages of both machines, it seemed most of the process is spent on the updating process in the machine A within the schema A. I had revised the procedure to pass and create the object type list in A locally then use then to update the list in A, rather then to passing object one of a time for updating (like the one I have), but apparently it worsen the process. Is there any better procedure to speed up the update. Your suggestion is appreciated.

CREATE OR REPLACE PROCEDURE sp_matchFEDLOG( p_siteID IN VARCHAR2,

                         o_err  OUT VARCHAR2) AS
                                            
   v_niin           FL$FL_MGMTSINFO.tbl_niin;   -- object type on schema B
   v_fsc            FL$FL_MGMTSINFO.tbl_fsc;    -- object type on schema B
   v_errorText      VARCHAR2(200);

   v_ContractID     catalog_items.contract_id%TYPE DEFAULT NULL;
   v_count          NUMBER(8) DEFAULT 0;
   l_niin           catalog_items.niin%TYPE DEFAULT NULL;
   l_fsc            catalog_items.fsc%TYPE DEFAULT NULL;
 
   l_fedlog         FL$FL_MGMTSINFO.tbl_fedinfo;  -- object type on schema B
   l_msg            VARCHAR2(200);
 
   CURSOR c_sites (p_siteID IN sites.site_id%TYPE) IS
       SELECT  NVL(s.dflt_contract_id, s.site_id)
       FROM   sites s
       WHERE  s.site_id = p_siteID;
       

   CURSOR c_catalog_NSN (p_contractID IN catalog_items.contract_id%TYPE) IS

       SELECT fsc, niin
       FROM catalog_items
       WHERE vendor_id = vd2_util.SAMMS_ORDER
       AND contract_id = p_contractID;
       

BEGIN     

    OPEN  c_sites (p_siteID);
    FETCH c_sites INTO v_ContractID;
    CLOSE c_sites;
    

    OPEN c_catalog_NSN(v_ContractID);
    FETCH c_catalog_NSN BULK COLLECT INTO v_fsc, v_niin;       

    IF c_catalog_NSN%ROWCOUNT > 0 THEN

       BEGIN
        
          FL$FL_MGMTSINFO.FedlogInfo(v_niin, p_siteID,l_fedlog, l_msg);
                
          FOR i IN 1 .. l_fedlog.COUNT LOOP
           
             l_fsc:= SUBSTR(l_fedlog(i),1,4);
             l_niin:= SUBSTR(l_fedlog(i),6,9);
      
          
             FOR j IN 1 .. v_niin.COUNT LOOP
                IF v_fsc(j) <> l_fsc AND v_niin(j) = l_niin THEN
                   UPDATE catalog_items
                   SET fsc = l_fsc
                   WHERE contract_id = v_ContractID
                   AND niin = v_niin(j);
                   v_count:=v_count+1;
                 --  dbms_output.put_line(l_fsc || '-'|| v_niin(j));
                END IF;
             END LOOP;
 
          END LOOP;
          o_err:=  l_msg || '. '|| 'Updating ' || v_count;
       EXCEPTION
          WHEN OTHERS THEN
             v_errorText := SUBSTR(SQLERRM, 1, 200);
             o_err:= l_msg || '-' || v_errorText;
       END;
    ELSE
        o_err:= 'catalog not found';

    END IF;
    CLOSE c_catalog_NSN;        

END sp_matchFEDLOG;
/

C Chang Received on Mon Jun 28 2004 - 22:02:19 CDT