两表关联 ,删除数据, 谁有啥好办法么?tbl_his_ids_riskcon 8k万
tbl_ids_moneysch 17k万tbl_his_ids_riskcon a, tbl_ids_moneysch b 这两个表 select b.rowid
from tbl_his_ids_riskcon a, tbl_ids_moneysch b
where a.policyno = b.policyno
a.classcode = b.classcode;
根据 tbl_his_ids_riskcon 表来删除 tbl_ids_moneysch 表的数据
这两个表都按照branch字段进行了分区目前我们的办法是:
DECLARE
maxrows number default 100000;
row_id_table dbms_sql.Urowid_Table; CURSOR cur_rowid IS
select b.rowid
from tbl_his_ids_riskcon a, tbl_ids_moneysch b
where a.policyno = b.policyno
a.classcode = b.classcode
and a.branch = '00000000000028'
and b.branch = '00000000000028';
BEGIN
OPEN cur_rowid;
LOOP
EXIT WHEN cur_rowid%NOTFOUND; --没有记录,则退出
FETCH cur_rowid BULK COLLECT INTO row_id_table LIMIT maxrows;
FORALL I IN 1..row_id_table.COUNT --批量更新
delete tbl_ids_moneysch where rowid=row_id_table(I);
commit;
END LOOP;
CLOSE cur_rowid;
commit work;
EXCEPTION
WHEN OTHERS THEN
IF cur_rowid%ISOPEN THEN
CLOSE cur_rowid;
END IF;
RAISE_APPLICATION_ERROR(-20604,'sqlcode:'||sqlcode||'***sqlerrm:delete_tbl_ids_moneysch error');
END;准备一个一个分公司来做.
还有别的方法么???
tbl_ids_moneysch 17k万tbl_his_ids_riskcon a, tbl_ids_moneysch b 这两个表 select b.rowid
from tbl_his_ids_riskcon a, tbl_ids_moneysch b
where a.policyno = b.policyno
a.classcode = b.classcode;
根据 tbl_his_ids_riskcon 表来删除 tbl_ids_moneysch 表的数据
这两个表都按照branch字段进行了分区目前我们的办法是:
DECLARE
maxrows number default 100000;
row_id_table dbms_sql.Urowid_Table; CURSOR cur_rowid IS
select b.rowid
from tbl_his_ids_riskcon a, tbl_ids_moneysch b
where a.policyno = b.policyno
a.classcode = b.classcode
and a.branch = '00000000000028'
and b.branch = '00000000000028';
BEGIN
OPEN cur_rowid;
LOOP
EXIT WHEN cur_rowid%NOTFOUND; --没有记录,则退出
FETCH cur_rowid BULK COLLECT INTO row_id_table LIMIT maxrows;
FORALL I IN 1..row_id_table.COUNT --批量更新
delete tbl_ids_moneysch where rowid=row_id_table(I);
commit;
END LOOP;
CLOSE cur_rowid;
commit work;
EXCEPTION
WHEN OTHERS THEN
IF cur_rowid%ISOPEN THEN
CLOSE cur_rowid;
END IF;
RAISE_APPLICATION_ERROR(-20604,'sqlcode:'||sqlcode||'***sqlerrm:delete_tbl_ids_moneysch error');
END;准备一个一个分公司来做.
还有别的方法么???
大表删除操作,会占用大量的undo,建议多执行几次小批量删除(rownum<1000)