Help to optimize procedure (concatenation of rows)
Date: 2 Mar 2004 09:45:41 -0800
Message-ID: <cbf067a7.0403020945.56bd27f1_at_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 - 18:45:41 CET