使用merge into进行批量更新报错:
ORA-29886: feature not supported for domain indexes
需求是将B的两个字段每天晚上同步更新到A表,两张表大概都有30多万条数据,这样的数据量使用Update一晚上都跑不完。
一般情况批量更新有3种方法,如果merge into行不通,可以使用其他两种方法:
1、使用merge into 进行更新
但是发现会有报错:ORA-29886: feature not supported for domain indexes
查了一下,在11G以前merge into不支持domain indexes,虽然我的更新和domain indexes所在的列没有任何关系,既不用来关联,也不用于更新,但仍然报错。如果必须使用merge,可以考虑把 domain indexes删除,更新之后在重建,直接禁用索引是否可以没尝试过。
查询domain indexes的方法:
查询A表的建表语句根本没有domain index,但是在dba_indexes中能查到在该表下有domain indexes
然后可以根据在dba_indexes中查到的索引名和用户名查询索引内容:
select dbms_lob.substr(dbms_metadata.get_ddl('INDEX','A593_IX1','ABC')) from dual;
其中A593_IX1为索引名,ABC为索引所属用户。
2、使用内联视图更新,效率也不差
以下为举例,用tb2的fname和lname更新tb1的fname和lname:
UPDATE
(SELECT a.id AS id1,b.id AS id2,a.fname AS f1,b.fname AS f2,a.lname AS l1,b.lname AS l2
FROM
tb1 a,tb2 b
WHERE a.id=b.id) t
SET t.f1=t.f2,t.l1=t.l2;
要求tb2的id列必须有唯一索引或者主键,否则会报错:
ORA-01779: 无法修改与非键值保存表对应的列
如果不能加主键或者唯一索引,可以加Hint忽视检查,但要保证t2的id列是唯一的,否则结果会有问题。
UPDATE /*+ BYPASS_UJVC */
(SELECT a.id AS id1,b.id AS id2,a.fname AS f1,b.fname AS f2,a.lname AS l1,b.lname AS l2
FROM
tb1 a,tb2 b
WHERE a.id=b.id) t
SET t.f1=t.f2,t.l1=t.l2;
3、使用bulk collect+forall,我把tb2表进行分割,每次取10000条数据放进临时表,然后使用临时表进行update。
create or replace procedure pd_bdc_tmp as
type cur_type is ref cursor;
cur_qlr cur_type;
str_sql varchar2(2000);
--10g bulk collect 不支持记录类型
--type rec_type is record(
-- BDCDYH VARCHAR2(200),
-- BDCQZH VARCHAR2(500),
-- QLRMC VARCHAR2(500));
type dyh_type is table of VARCHAR2(200);
type QZH_type is table of VARCHAR2(500);
type mc_type is table of VARCHAR2(500);
dyh_tab dyh_type;
qzh_tab qzh_type;
mc_tab mc_type;
--v NUMBER;
v_limit pls_integer;
V_ERR_MSG VARCHAR2(200);
V_ERR_CODE NUMBER;
BEGIN
--添加临时表
str_sql:='CREATE GLOBAL TEMPORARY TABLE tmp (id NUMBER,fname VARCHAR2(20),lname VARCHAR2(20))
ON COMMIT DELETE ROWS';
EXECUTE IMMEDIATE str_sql;
v_limit := 10000;
open cur_qlr for 'SELECT id,fname,lname from tb2';
loop
fetch cur_qlr bulk collect INTO dyh_tab,qzh_tab,mc_tab limit v_limit;
exit when dyh_tab.count = 0;
forall i in 1 .. dyh_tab.count
EXECUTE IMMEDIATE 'insert into tmp values (:1,:2,:3)'
USING dyh_tab(i),qzh_tab(i),mc_tab(i);
--for i in name_tab.first .. name_tab.last loop
-- insert into tmp_tb1
-- (ID,fname, lname)
-- values
-- (name_tab(i).bdcdyh, name_tab(i).bdcqzh, name_tab(i).qlrmc);
-- end loop;
--EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM tmp' INTO v;
-- dbms_output.put_line('v:'||v);
EXECUTE IMMEDIATE 'UPDATE tb1 A
SET (A.fname, A.lname) = (SELECT T1.fname, T1.lname
FROM tmp T1
WHERE A.id = T1.id)
WHERE EXISTS (SELECT 1 FROM tmp T1 WHERE A.id = T1.id)
AND A.id IS NOT NULL';
str_sql := 'truncate table tmp';
execute immediate str_sql;
-- exit when cur_qlr%notfound;
end loop;
CLOSE cur_qlr;
commit;
str_sql := 'drop table tmp';
execute immediate str_sql;
exception
when others THEN
ROLLBACK;
CLOSE cur_qlr;
str_sql := 'drop table tmp';
execute immediate str_sql;
V_ERR_CODE := SQLCODE;
V_ERR_MSG := SUBSTR(SQLERRM, 1, 200);
dbms_output.put_line(V_ERR_CODE||':'||V_ERR_MSG);
end;
最后创建执行计划:每天凌晨1点运行一次存储过程
Declare
i Integer;
Begin
dbms_job.submit(i,
'PD_BDC_ZD;',
to_date('20-03-2017 20:00:00','dd/mm/yyyy hh24:mi:ss'),
'trunc(sysdate)+1+1/24');
commit;
end;