More SQL PL/SQL optimization

From: Chris Stephens <cstephens16@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Fri, 14 Jan 2005 13:05:42 -0500
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@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
--
//www.freelists.org/webpage/oracle-l
More SQL PL/SQL optimization

Other related posts:
