Oracle Database Discussions

Hello people,

I have a specific issue for which I have been contemplating a solution, It would be great if you guys help me out with this issue.

I have a business need to update 10 million rows in a table which has 1 billion records. Although I have read a lot about cursors I am unable to decide at this moment if using cursors is a good approach in this situation:

1) I have 1 million rows in a emp_temp_table.

2) I have to select the 1 million rows in emp_temp_table and update these records in EMP table.

I have looked into "CREATE TABLE new_table as select <do the update "here"> from old_table;" but space is a concern to use this approach (cannot allocate temporary table which would occupy 1 TB. )

Here is the procedure, I wanted to know if this approach is good. Its acceptable in our environment to execute this code for block of 6- 10 hrs :

SET lines 80 pages 100
SET serverout on size 1000000
SET echo on verify on timing on time on trimspool on wrap on

DECLARE

v_record_updt_cnt NUMBER:=0;
v_commit_cnt NUMBER:=0;
v_rec_cnt NUMBER:=0;
v_err_code VARCHAR2(10) := SQLCODE;
v_err_msg VARCHAR2(256) := SQLERRM;


CURSOR get_temp_records IS
SELECT ett.emp_name, ett.emp_id, ett.emp_phone,
ett.date
FROM emp_admn.emp_temp_table ett
;

BEGIN

/* Count of records to update EMP table. */
SELECT count(*) INTO v_rec_cnt
FROM emp_admn.emp_temp_table ett
;

/* Update the EMP table records in the cursor */
FOR emp_info IN get_temp_records
LOOP
BEGIN

UPDATE emp_admn.emp
SET emp_name = emp_info.emp_name
WHERE emp_id = emp_info.emp_id
AND emp_phone = emp_info.emp_phone
;

v_record_updt_cnt := v_record_updt_cnt+SQL%ROWCOUNT;
v_commit_cnt := v_commit_cnt+SQL%ROWCOUNT;

IF v_commit_cnt = 100000
THEN
COMMIT;
dbms_output.put_line(' 100000 Rows commited.');
v_commit_cnt :=0;
END IF;

EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('Error: '|| SQLCODE || ' ' || SQLERRM);
dbms_output.put_line('Error Record: ' || ' ID : ' || emp_info.emp_id || ' Name: ' || emp_info.emp_name );
END;

END LOOP;

COMMIT;

/* To verify all the records updated EMP table. */
IF v_record_updt_cnt = v_rec_cnt
THEN
dbms_output.put_line('All the records are updated.');
ELSE
dbms_output.put_line('# records EMP_TEMP_TABLE table: ' || v_rec_cnt);
dbms_output.put_line('# records updated in EMP TABLE: ' || v_record_updt_cnt);
END IF;

EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('Error: ' || ' ' || SQLCODE);
dbms_output.put_line('Error: ' || ' ' || SQLERRM);
END;
/

exit

Edited by: 954051 on Aug 21, 2012 5:55 PM

Edited by: 954051 on Aug 21, 2012 5:55 PM

Edited by: 954051 on Aug 21, 2012 5:58 PM

Edited by: 954051 on Aug 22, 2012 1:06 PM