Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    41

    Unanswered: Want a help in Optimize a query.

    I need help to optimize the following update query which is updating 14 fields having null value.the problem is,for each field we have to get a maximum value from the source table and also it should satisfy some conditions.
    //Cursor

    CURSOR UPDATE_CURSOR IS
    SELECT *
    FROM UPDATE_TABLE
    FOR UPDATE OF
    federal
    , state
    , county
    , city
    , other
    , pkg_inst_id
    , type_code
    , certificate
    , status;
    ce_record EXEMPT_UPDATE_CURSOR%ROWTYPE;

    //UPdate

    OPEN UPDATE_CURSOR;
    FETCH UPDATE_CURSOR INTO ce_record;
    WHILE UPDATE_CURSOR %FOUND
    LOOP
    UPDATE UPDATE_TABLE TPC
    SET TPC.federal =
    (SELECT /*+ CHOOSE */
    NVL(MAX(CE.federal),TPC.federal)
    FROM SOURCE CE
    WHERE ((CE.pkg_inst_id = ce_record.pkg_inst_id
    AND CE.type_code = 0)
    OR (CE.pkg_inst_id = 0
    AND CE.type_code = ce_record.type_code)
    OR (CE.pkg_inst_id = 0
    AND CE.type_code = 0))
    AND CE.account_no = ce_record.account_no
    AND (ce_record.exempt_federal != 1
    OR ce_record.exempt_federal IS NULL)
    )
    , TPC.state =
    (SELECT /*+ CHOOSE */
    NVL(MAX(CE.state),TPC.state)
    FROM SOURCE CE
    WHERE ((CE. pkg_inst_id = ce_record.pkg_inst_id
    AND CE. type_code = 0)
    OR (CE. pkg_inst_id = 0
    AND CE. type_code = ce_record.type_code)
    OR (CE. pkg_inst_id = 0
    AND CE. type_code = 0))
    AND CE.account_no = ce_record.account_no
    AND (ce_record.state != 1
    OR ce_record.state IS NULL)
    )
    ....

    WHERE CURRENT OF UPDATE_CURSOR
    ;
    FETCH UPDATE_CURSOR INTO ce_record;

    END LOOP;

    CLOSE UPDATE_CURSOR;

    In the above update query the folwong conditions are common for all the 14 fields.

    ((CE. pkg_inst_id = ce_record.pkg_inst_id
    AND CE. type_code = 0)
    OR (CE. pkg_inst_id = 0
    AND CE. type_code = ce_record.type_code)
    OR (CE. pkg_inst_id = 0
    AND CE. type_code = 0))
    AND CE.account_no = ce_record.account_no

    this update query should executed for each records in update_table and for updation of each field a select query should be executed.Following are the approximate record counts for the each table.

    source table may contain more than 85,000 records.
    update_table may contain more than 11,000 records.

    Note:
    I have created a index for the source table.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    1) You can dispense with the cursor and procedural code and just perform one big update.
    2) You can SET many columns at once from a single subquery.
    3) You can use the CASE statement to handle the conditional updates.

    I think this does it:
    Code:
    BEGIN
      UPDATE UPDATE_TABLE TPC
      SET (TPC.federal, TPC.state, ...) =
      (SELECT 
        CASE WHEN (TPC.exempt_federal != 1 OR TPC.exempt_federal IS NULL) THEN NVL(MAX(CE.federal),TPC.federal) ELSE TPC.federal END,
        CASE WHEN (TPC.state != 1 OR TPC.state IS NULL) THEN NVL(MAX(CE.state),TPC.state) ELSE TPC.state END,
        ...
        FROM SOURCE CE
        WHERE ((CE.pkg_inst_id = TPC.pkg_inst_id AND CE.type_code = 0)
        OR (CE.pkg_inst_id = 0 AND CE.type_code = TPC.type_code)
        OR (CE.pkg_inst_id = 0 AND CE.type_code = 0))
        AND CE.account_no = TPC.account_no
      );
    END;

  3. #3
    Join Date
    Mar 2004
    Location
    India
    Posts
    41

    Andrew -could u pls throw some light on the idea u proposed.

    Hi Andrew,

    I understand that because of cursor the code is taking time,but the account no in the tables are unique and the SELECT stmnt returns more than one row.So the MAX(CE.federal),TPC.federal) may not produce the correct result.Could u pls justify ur soln.

    Also its throwing error while executing the code given by u.
    Its showing error in the CASE stmnt.pls let me know the soln.

    Thanks and Regds,
    Arun

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The justification for my solution is that all your subqueries were the same, except for the last 2 lines which acted as a kind of yes/no switch for selecting CE records or not; I moved the yes/no part into the CASE expressions and combined all the identical subqueries into one. I don't know what you mean about it "may not produce the correct result", since I believe the query does the same as yours.

    If Oracle is objecting to the CASE statement you are probably on a pre-9i version. In that case, you could use DECODE instead:

    Code:
    BEGIN
      UPDATE UPDATE_TABLE TPC
      SET (TPC.federal, TPC.state, ...) =
      (SELECT 
        DECODE (TPC.exempt_federal, 1, TPC.federal, NVL(MAX(CE.federal),TPC.federal)),
        DECODE (TPC.state, 1, TPC.state, NVL(MAX(CE.state),TPC.state)),
        ...
        FROM SOURCE CE
        WHERE ((CE.pkg_inst_id = TPC.pkg_inst_id AND CE.type_code = 0)
        OR (CE.pkg_inst_id = 0 AND CE.type_code = TPC.type_code)
        OR (CE.pkg_inst_id = 0 AND CE.type_code = 0))
        AND CE.account_no = TPC.account_no
      );
    END;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •