oracle效能最佳化

標籤：

 

 情境：對2千萬個資料，修改他們的名字加上尾碼“生日”。普通sql： 

update calendar_info set title =concat(title, ‘生日‘) where specialtype = 1 and not regexp_like(title, ‘生日‘);

最佳化sql：

declaretype rid_Array is table of rowid index by binary_integer;v_rid rid_Array;v_Count PLS_INTEGER := 0;v_Cur sys_refcursor;v_SQL varchar2(2000) := ‘select t.rowid from calendar_info t where specialtype = 1 and title not like ‘‘%生日‘‘‘;beginopen v_Cur for v_SQL;loopfetch v_Cur bulk collectinto v_rid limit 1000;forall i in 1 .. v_rid.count()update calendar_info set title =title||‘生日‘ where rowid = v_rid(i);v_Count := v_Count + sql%rowcount;commit;exit when v_Cur%notfound;end loop;commit;close v_Cur;dbms_output.put_line(v_Count);exceptionwhen others thenrollback;end;

 

 

http://blog.csdn.net/dba_waterbin/article/details/8581448

oracle效能最佳化