Help to optimize procedure (concatenation of rows)

From: Juraj Longauer <longauer_at_centrum.sk>
Date: 2 Mar 2004 09:45:41 -0800
Message-ID: <cbf067a7.0403020945.56bd27f1@posting.google.com>

Hi,

I need to concatenate rows with description of account branch into one column.

Example:
I have created temporary table to retrieve client and branches for his accounts.(5 mil. rows)

 CUS_NO	 MSP_ID	MSP_DESC
5072130	 2112	MsP Bratislava
5072130	 2182	MsP Poprad

I would like to concatenate msp_desc into one row so the result will look like this.

 CUS_NO	 MSP_ID	MSP_DESC
5072130	 2112	MsP Bratislava :: MsP Poprad

I have created this procedure to perform the challenge. The procedure is using 2 cursors. 1. cursor retrieves customer number and passes customer number to 2. cursor. 2. cursor retrieves branches for a customer and concatenates the branches. Concatenated values are passed to variable. Then I insert customer number, name .... and variable with concatenated branches. Its working but, its running toooo long (2 days). I would appreciate ideas to improve performance. I think the problem is that I open second cursor for each customer. Many thanks

Juraj

DECLARE
  type mycur is ref cursor;
  L_TNAME VARCHAR2(30) DEFAULT 'temp_table_' || USERENV('sessionid');
  V_UCET_MSP VARCHAR2(200);
  CURSOR C1 IS(
    SELECT CUS.CUS_NO, CUS.LOB_ID, CUS.NAME, CUS.TAXIDNO, CUS.AS_OF_DATE

      FROM CUSTOMER CUS
     WHERE CUS_NO IN (SELECT CUS_NO FROM ACCT)
     
     );

  c2 mycur;
  v_msp_desc varchar2(300);
i INTEGER;
BEGIN
--create temporary table with client info and branches
  EXECUTE IMMEDIATE 'create global temporary table ' || L_TNAME ||

                    ' on commit delete rows
                        as
                        SELECT DISTINCT CUS.CUS_NO, ORG.MSP_ID,
ORG.MSP_DESC
      FROM CUSTOMER CUS, ACCT A, ICARUS.ORG_HIER_TMP ORG
     WHERE 1=0';

--fill temporary table

  EXECUTE IMMEDIATE 'insert into ' || L_TNAME ||

                    ' SELECT DISTINCT CUS.CUS_NO, ORG.MSP_ID,
ORG.MSP_DESC
                        FROM CUSTOMER CUS, ACCT A, ICARUS.ORG_HIER_TMP
ORG
     WHERE CUS.CUS_NO = A.CUS_NO AND A.ORG_UNIT_ID = ORG.LEAF_NODE';

  --delete target table
  DELETE FROM ICARUS.CUSTOMER;
  --insert into target table
  FOR C1_REC IN C1 LOOP
    OPEN c2 FOR 'select msp_desc from ' || l_tname || ' where cus_no = '||c1_rec.cus_no|| '';

    LOOP
    FETCH c2 INTO v_msp_desc;
    exit when c2%notfound;
    V_UCET_MSP := V_UCET_MSP || ' :: ' || v_msp_desc;     END LOOP;
    CLOSE c2;
    INSERT INTO ICARUS.CUSTOMER
    VALUES

      (C1_REC.CUS_NO,
       C1_REC.LOB_ID,
       C1_REC.NAME,
       C1_REC.TAXIDNO,
       V_UCET_MSP,
       C1_REC.AS_OF_DATE);
    V_UCET_MSP := NULL;
    I          := I+1;

  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Processed rows: ' || I);   EXECUTE IMMEDIATE 'drop table ' || L_TNAME ||'';   COMMIT;
  END; Received on Tue Mar 02 2004 - 11:45:41 CST