SQL & PL/SQL

Hi all,

I am attempting to update 800k records in a table with 18.5 million records.

The SQL I need to use will take corrective data from another table to update records in the mailn table (which is live and transactional).

The update would look like this:

update update_test ut set ut.customer_reference =
(select cru.customer_reference
from cust_ref_update cru
where ut.transaction_number = cru.transaction_number);

What would be the most efficient way to do this?

I was thinking of using a procedure like this:

CREATE OR REPLACE PROCEDURE Pr_Batch_Commit
( p_statement IN VARCHAR2,
p_commit_batch_size IN NUMBER DEFAULT 10000)
IS
cid INTEGER;
changed_statement VARCHAR2(2000);
finished BOOLEAN;
nofrows INTEGER;
lrowid ROWID;
rowcnt INTEGER;
errpsn INTEGER;
sqlfcd INTEGER;
errc INTEGER;
errm VARCHAR2(2000);
BEGIN
/* If the actual statement contains a WHERE clause, then append a
rownum < n clause after that using AND, else use WHERE
rownum < n clause */
IF ( UPPER(p_statement) LIKE '% WHERE %') THEN
changed_statement := p_statement||' AND rownum < '
||TO_CHAR(p_commit_batch_size + 1);
ELSE
changed_statement := p_statement||' WHERE rownum < '
||TO_CHAR(p_commit_batch_size + 1);
END IF;
BEGIN
cid := DBMS_SQL.OPEN_CURSOR; -- Open a cursor for the task
DBMS_SQL.PARSE(cid,changed_statement, dbms_sql.native);
-- parse the cursor. Pleae note that in Oracle 7.2.2
-- parsing does a execute too. But that does not
-- pose a problem here as we want that.
rowcnt := DBMS_SQL.LAST_ROW_COUNT;
-- store for some future reporting
EXCEPTION
WHEN OTHERS THEN
errpsn := DBMS_SQL.LAST_ERROR_POSITION;
-- gives the error position in the changed sql
-- delete statement if anything happens
sqlfcd := DBMS_SQL.LAST_SQL_FUNCTION_CODE;
-- function code can be found in the OCI manual
lrowid := DBMS_SQL.LAST_ROW_ID;
-- store all these values for error reporting. However
-- all these are really useful in a stand-alone proc
-- execution for dbms_output to be successful, not
-- possible when called from a form or front-end tool.
errc := SQLCODE;
errm := SQLERRM;
DBMS_OUTPUT.PUT_LINE('Error '||TO_CHAR(errc)||
' Posn '||TO_CHAR(errpsn)||
' SQL fCode '||TO_CHAR(sqlfcd)||
' rowid '||ROWIDTOCHAR(lrowid));
RAISE_APPLICATION_ERROR(-20000,errm);
-- this will ensure the display of atleast the error
-- message if someething happens, even in a frontend
-- tool.
END;
finished := FALSE;
WHILE NOT (finished)
LOOP -- keep on executing the cursor till there is no more to process.
BEGIN
nofrows := DBMS_SQL.EXECUTE(cid);
rowcnt := DBMS_SQL.LAST_ROW_COUNT;
EXCEPTION
WHEN OTHERS THEN
errpsn := DBMS_SQL.LAST_ERROR_POSITION;
sqlfcd := DBMS_SQL.LAST_SQL_FUNCTION_CODE;
lrowid := DBMS_SQL.LAST_ROW_ID;
errc := SQLCODE;
errm := SQLERRM;
DBMS_OUTPUT.PUT_LINE('Error '||TO_CHAR(errc)||
' Posn '||TO_CHAR(errpsn)||
' SQL fCode '||TO_CHAR(sqlfcd)||
' rowid '||ROWIDTOCHAR(lrowid));
RAISE_APPLICATION_ERROR(-20000,errm);
END;
IF nofrows = 0 THEN
finished := TRUE;
ELSE
finished := FALSE;
END IF;
COMMIT;
END LOOP;
BEGIN
DBMS_SQL.CLOSE_CURSOR(cid);
-- close the cursor for a clean finish
EXCEPTION
WHEN OTHERS THEN
errpsn := DBMS_SQL.LAST_ERROR_POSITION;
sqlfcd := DBMS_SQL.LAST_SQL_FUNCTION_CODE;
lrowid := DBMS_SQL.LAST_ROW_ID;
errc := SQLCODE;
errm := SQLERRM;
DBMS_OUTPUT.PUT_LINE('Error '||TO_CHAR(errc)||
' Posn '||TO_CHAR(errpsn)||
' SQL fCode '||TO_CHAR(sqlfcd)||
' rowid '||ROWIDTOCHAR(lrowid));
RAISE_APPLICATION_ERROR(-20000,errm);
END;
END;
/

But it seems to be taking an age to complete on TEST.

Any ideas?