Updating using cursor oracle
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 '