在Pro*C中可以为SQL中的绑定变量传入一个数组, 进行批量处理, 减少User Call和Execute Count, 尤其是在客户端服务器的情况下, 这个性能提升十份明显. 在Java中也有类似的executeBatch调用, 最新版本的Perl中也可以用数组来绑定一个变量. Oracle在9i的PL/SQL中正式支持了批量操作, 下面是一个批量删除的例子:
DECLARE
TYPE ARRROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
tbrows ARRROWID;
row PLS_INTEGER;
cursor delete_table is select row_id from testobj_rowid;
BEGIN
open delete_table;
loop
fetch delete_table bulk collect into tbrows limit 250;
FORALL row IN 1 .. tbrows.count()
DELETE TEST.TESTOBJ WHERE rowid = tbrows(row);
commit;
-- dbms_lock.sleep(1);
exit when delete_table%notfound;
end loop;
close delete_table;
END;
/
致于批量的插入或更新角本, 照着这个改改应当很容易出来的. 写在这儿, 也是为了方便我自已抄写.
留言 (6)
DELETE TEST.TESTOBJ WHERE current of delete_table;
这个方法看来更加简单，不用把 rowid 弄出来比较了。
Posted by joe jiang | Feb 26, 2007 3:33 PM
http://www.anysql.net/developer/jdbc_fetch_size.html
你可以看看这一篇.
Posted by anysql | Feb 26, 2007 3:43 PM
首先还是Single SQL DELETE.
Here is a de_dup(排除重复行) example:
DELETE FROM abelisting.listsbyrsrchattrs
WHERE ROWID IN
(
SELECT rid --, listingsid, bsacode, rn
FROM (SELECT /*+ parallel(a) */ ROWID rid, listingsid, bsacode,
ROW_NUMBER () OVER (PARTITION BY listingsid, bsacode ORDER BY listingsid) rn
FROM abelisting.listsbyrsrchattrs a)
WHERE rn > 1
);
但是在OLTP环境中, 不能长时间锁住事务表.
这时候就需要Collect Bulk DML.
PL/SQL example:
--这个样本里面,Logging(Instrument code)作的还是不够好.
PROCEDURE del_abecats
AS
l_row_cnt NUMBER(12);
lt_data DBMS_SQL.NUMBER_TABLE;
--l_rid dbms_sql.VARCHAR2_TABLE;
lt_urowid DBMS_SQL.UROWID_TABLE;
TYPE ROWIDList IS TABLE OF abelisting.gt_listing_id.rid%TYPE;
l_rid ROWIDList;
--CURSOR c_list IS SELECT RID FROM abelisting.gt_listing_id;
CURSOR c_list IS SELECT ROWID FROM abelisting.LISTINGSABECATS WHERE ABECATEGORIESGROUPID = 0;
l_bulk_limit NUMBER := 5000;
BEGIN
DEBUG.f('start');
DBMS_APPLICATION_INFO.SET_MODULE('DEL abecats', 'delete list_abecats child tables');
OPEN c_list;
LOOP
FETCH c_list BULK COLLECT INTO l_rid LIMIT l_bulk_limit;
FORALL i IN l_rid.FIRST .. l_rid.LAST DELETE /*+ */ FROM ABELISTING.LISTINGSABECATS a WHERE ROWID = l_rid(i) AND ABECATEGORIESGROUPID = 0;
l_row_cnt := SQL%ROWCOUNT;
DEBUG.f('deleted abecats %s rows',l_row_cnt);
COMMIT;
EXIT WHEN c_list%NOTFOUND;
END LOOP;
CLOSE c_list;
COMMIT;
DEBUG.f('end');
EXCEPTION
WHEN OTHERS THEN
errmsg := SUBSTR(TO_CHAR(SQLCODE)||': '||SQLERRM,1,100);
DEBUG.f(errmsg);
RAISE;
END;
Posted by zhu1 (木匠) | Feb 27, 2007 3:00 AM
关于如何EXIT loop. 以下两种都行.
1)EXIT WHEN table_type_variable.COUNT = 0; 循环开始位置
2)EXIT WHEN cursor%NOTFOUND; 循环结束位置
新手推荐使用 EXIT WHEN table_type_variable.COUNT = 0;
Example:
OPEN c_list;
LOOP
FETCH c_list BULK COLLECT INTO l_rid, lt_catid LIMIT l_bulk_limit;
EXIT WHEN l_rid.COUNT = 0;
-- EXIT WHEN c1%NOTFOUND; -- incorrect, can omit some data
FORALL i IN l_rid.FIRST .. l_rid.LAST DELETE /*+ */ FROM abelisting.LISTINGSABECATS a
WHERE ROWID = l_rid(i) AND ABECATEGORIESGROUPID = lt_catid(i);
l_row_cnt := l_row_cnt + SQL%ROWCOUNT;
DEBUG.f('deleted abecats %s rows',l_row_cnt);
COMMIT;
EXIT WHEN c_list%NOTFOUND;
END LOOP;
CLOSE c_list;
Posted by zhu1 (木匠) | Feb 27, 2007 3:22 AM
1,不能强制人家一定要装DEBUG包!
2,Cursor和Delete的SQL中引用同一个表易引起ORA-1555错误.
Posted by anysql | Feb 27, 2007 8:39 AM
comment备注不就得了,你老兄真够严厉的.
Posted by zhu1 (木匠) | Feb 27, 2007 12:43 PM