ORA-29886: feature not supported for domain indexes
来源:互联网 发布:海德椭圆机 知乎 编辑:程序博客网 时间:2024/05/17 06:09
使用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 bWHERE a.id=b.id) tSET 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 bWHERE a.id=b.id) tSET 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;
- ORA-29886: feature not supported for domain indexes
- ORA-32773: operation not supported for smallfile tablespace
- ORA-32773: operation not supported for smallfile tablespace TOOLS
- ORA-00845 MEMORY_TARGET not supported on this system (oracle11g for asianux3 )
- ORA-00401: the value for parameter compatible is not supported by this release
- ORA-00845 MEMORY_TARGET not supported on this system (oracle11g for asianux3 )
- ORA-00439 feature not enabled: Partitioning
- ORA-00439: feature not enabled: Managed Standby
- ORA-00439 feature not enabled: Partitioning
- ORA-29857: domain indexes and/or secondary objects exist in the tablespace tips
- Error : Servname not supported for ai_socktype 错误
- ORA-00845: MEMORY_TARGET not supported on this system 错误解决方法!
- ORA-00845: MEMORY_TARGET not supported on this system - Linux Servers
- ORA-00845: MEMORY_TARGET not supported on this system
- ORA-00845: MEMORY_TARGET not supported on this system
- ora-00845 MEMORY_TARGET not supported on this system 的问题
- ORA-00845: MEMORY_TARGET not supported on this system
- ORA-00845: MEMORY_TARGET not supported on this system
- 海思外部看门狗驱动
- vysor原理以及Android同屏方案
- 使用HttpSessionListener接口监听Session的创建和失效
- WPF操作SQLite
- LR项目中性能测试觉见指标
- ORA-29886: feature not supported for domain indexes
- springboot与redis使用(1)
- Git配置多账号登录不同项目
- MTK平台根据特定项目定义一个驱动中用到的宏方法
- 使用CallerMemberName简化InotifyPropertyChanged的实现
- 欢迎使用CSDN-markdown编辑器
- Eclipse常用配置
- 同步异步
- jsp两种申明变量的区别