Updating using cursor oracle


03-Mar-2015 18:16

Anytime you can process by using the row rowid in an Oracle table you will get the best performance. Time reductions for some massive UPDATEs were reduced from 12-13 hours to 6 or less as reported by several sites using ROWID processing. Implicit type conversions result in the statement not being able to use indexes thus forcing a full table scan.

TO_CHAR(maxrows)); OPEN proc_row(rowcount,maxrows); FETCH proc_row into temp_id; LOOP EXIT WHEN proc_row%NOTFOUND; sql_com:='UPDATE '

The easiest to see is a bulk delete type operation. Figure 14 shows a simple delete procedure using this logic. Be sure loop logic is correct Using DBMS_PROFILER the developer can easily see how many times a specific set of statements is executed.

CREATE OR REPLACE PROCEDURE delete_it ( rows IN NUMBER, tab_name IN VARCHAR2, delete_date IN DATE) AS cur INTEGER; ret INTEGER; row_count NUMBER; com_string VARCHAR2(100); trunc_date DATE; BEGIN com_string := 'SELECT count(1) row_count FROM '