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.

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:

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

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: