More SQL PL/SQL optimization

From: Chris Stephens <cstephens16_at_gmail.com>
Date: Fri, 14 Jan 2005 13:05:42 -0500
Message-ID: <d95e860705011410051be05ba5@mail.gmail.com>

Well, thanks to previous responses I have succussfully been able to turn much of the code in a loading package into sql. The following piece of code takes about 30% of the loading time and I can't for the life of me figure out how to turn it in to sql.

It is passed in the unit_id, year (pk) of the table to be updated (unit_fact) and from that a string is assembled from 2 source tables to be returned and used in an update statement. so for every row in the update statement this function is called. After giving up on writing a sql statement, I attempted to just turn this into a procedure and use bulk processing to do all the updates. I put the unit_fact table into the cursor to get the data back that i need to build the string but i can't figure out how to get 1 string per group of controls associated with a unit_id/year key and the update the appropriate column in unit_fact.

i realize this borders on pretty much asking someone to do my work for me but i just can't seem to get my brain around this and i figured...what the h_at_ll. it really feels (again) like this can be done in sql or at least turn it into a procedure that only needs to be called once instead of a function that is called ~ 100,000 times. anyways...any clues, hints, links, solutions are welcomed. i attempted to format the fcn.

if it matters this is a 9.2 database that will run on windows and linux platforms.

 FUNCTION UNIT_CTL_LIST(V_UNIT_ID IN CONTROL_YEAR_DIM.UNIT_ID%TYPE,

                                      V_OP_YEAR IN
CONTROL_YEAR_DIM.OP_YEAR%TYPE,
                                     ) RETURN VARCHAR2 IS

    RESULT VARCHAR2(500);
    ROWVAL VARCHAR2(500);      CURSOR CNT_CUR IS

      SELECT UC.UNIT_ID, 
          C.CONTROL_DESCRIPTION,
          UC.CE_PARAM PARAMETER,
          NVL(UC.INSTALL_DATE,  UC.OPT_DATE) INSTALL_DATE,
          UC.RETIRE_DATE
       FROM CONTROL C, UNIT_CONTROL UC
       WHERE UC.CONTROL_CD = C.CONTROL_CD AND
             (NVL(UC.INSTALL_DATE, UC.OPT_DATE) IS NULL OR
             EXTRACT(YEAR FROM NVL(UC.INSTALL_DATE, UC.OPT_DATE)) <=
                    V_OP_YEAR) AND
             (UC.RETIRE_DATE IS NULL OR EXTRACT(YEAR FROM
UC.RETIRE_DATE) >=
                    V_OP_YEAR) AND
             UC.UNIT_ID = V_UNIT_ID AND
             UC.CE_PARAM = V_PARAMETER
       ORDER BY NVL(UC.INSTALL_DATE, UC.OPT_DATE),
             UC.CONTROL_CD;
 

    CNT_REC CNT_CUR%ROWTYPE;   BEGIN     RESULT := NULL;

     FOR CNT_REC IN CNT_CUR LOOP
        ROWVAL := CNT_REC.CONTROL_DESCRIPTION;
      
      IF V_OP_YEAR = EXTRACT(YEAR FROM CNT_REC.INSTALL_DATE) AND


         (EXTRACT(MONTH FROM CNT_REC.INSTALL_DATE) <> 1 OR

         EXTRACT(DAY FROM CNT_REC.INSTALL_DATE) <> 1) THEN
        ROWVAL := ROWVAL || ' (Began ' || CNT_REC.INSTALL_DATE || ')';
      END IF;

      IF V_OP_YEAR = EXTRACT(YEAR FROM CNT_REC.RETIRE_DATE) AND


         (EXTRACT(MONTH FROM CNT_REC.RETIRE_DATE) <> 12 OR

         EXTRACT(DAY FROM CNT_REC.RETIRE_DATE) <> 31) THEN
        ROWVAL := ROWVAL || ' (Retired ' || CNT_REC.RETIRE_DATE || ')';
      END IF;
      
      IF LENGTH(RESULT) IS NULL THEN
        RESULT := ROWVAL;
      ELSE
        RESULT := RESULT || '<br>' || ROWVAL;
      END IF;

    END LOOP;     RETURN RESULT;
  EXCEPTION
   ...
END UNIT_CONTROL_LIST2; tia!
chris

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 14 2005 - 12:00:58 CST