首选定义三个临时表:第一个表为生成最终结果的表,第二,第三为临时性存放数据的表。我的想法是当取来第二第三个数据后,insert将数据插入最终表,update更新表内的记录,可是我发现update用两表关联,当数据量很大到100万条时,回滚很慢,insert似乎也不快,请问各怎么写能优化我的update和 insert性能,谢谢!
CREATE TABLE TEMP_0006_131552 ( ACCT_DATE VARCHAR2(30) null,COMP_CODE VARCHAR2(30) null,DIMENSION2 VARCHAR2(30) null,DIMENSION3 VARCHAR2(30) null, FDI_DATA1 VARCHAR2(100) null, FDI_DATA2 VARCHAR2(100) null , FDI_DATA3 VARCHAR2(100) null , FDI_DATA4 VARCHAR2(100) null ) create table TEMP2_1313(ACCT_DATE,COMP_CODE,DIMENSION2,DIMENSION3,FDI_DATA) As select ACCT_DATE,COMP_CODE,DIMENSION2,DIMENSION3, SUM(FDI_DATA) as FDI_DATA from tbdimendata (数据表)create table TEMP3_13175(ACCT_DATE,COMP_CODE,DIMENSION2,DIMENSION3,FDI_DATA) As select ACCT_DATE,COMP_CODE,DIMENSION2,DIMENSION3, SUM(FDI_DATA) as FDI_DATA from tbdimendata1 (数据表)
insert into TEMP_0006_131552(ACCT_DATE,COMP_CODE,DIMENSION2,DIMENSION3,FDI_DATA2)
select * from TEMP2_1313 where ( ACCT_DATE,COMP_CODE,DIMENSION2,DIMENSION3) not in
( select ACCT_DATE,COMP_CODE,DIMENSION2,DIMENSION3 from TEMP_0006_131552 )
Update TEMP_0006_131552 a set a.FDI_DATA3 =
(select FDI_DATA from TEMP3_13175 b where (b.ACCT_DATE,b.COMP_CODE,b.DIMENSION2,b.DIMENSION3)
in (select a.ACCT_DATE,a.COMP_CODE,a.DIMENSION2,a.DIMENSION3 from TEMP_0006_131552))
where (a.ACCT_DATE,a.COMP_CODE,a.DIMENSION2,a.DIMENSION3)
in ( select ACCT_DATE,COMP_CODE,DIMENSION2,DIMENSION3 from TEMP3_13175)insert into TEMP_0006_131552(ACCT_DATE,COMP_CODE,DIMENSION2,DIMENSION3,FDI_DATA3) select * from TEMP3_13175 where ( ACCT_DATE,COMP_CODE,DIMENSION2,DIMENSION3) not in ( select ACCT_DATE,COMP_CODE,DIMENSION2,DIMENSION3 from TEMP_0006_131552 )
select * from TEMP2_1313 where ( ACCT_DATE,COMP_CODE,DIMENSION2,DIMENSION3) not in
( select ACCT_DATE,COMP_CODE,DIMENSION2,DIMENSION3 from TEMP_0006_131552 )
Update TEMP_0006_131552 a set a.FDI_DATA3 =
(select FDI_DATA from TEMP3_13175 b where (b.ACCT_DATE,b.COMP_CODE,b.DIMENSION2,b.DIMENSION3)
in (select a.ACCT_DATE,a.COMP_CODE,a.DIMENSION2,a.DIMENSION3 from TEMP_0006_131552))
where (a.ACCT_DATE,a.COMP_CODE,a.DIMENSION2,a.DIMENSION3)
in ( select ACCT_DATE,COMP_CODE,DIMENSION2,DIMENSION3 from TEMP3_13175)
小小的疑问 判断是否在一表中为何要这么多列?没有主键么?
比较这么多当然会慢
个人观点
select FDI_DATA from TEMP3_13175 b where (b.ACCT_DATE,b.COMP_CODE,b.DIMENSION2,b.DIMENSION3)
in (select a.ACCT_DATE,a.COMP_CODE,a.DIMENSION2,a.DIMENSION3 from TEMP_0006_131552)
如果对其不进行两表关联,会返回多个结果对应单行记录会报错的。
其次:
在Update中加入两表关联,可以避免对表进行全扫描,提升了速度。
Update TEMP_0006_131552 a set a.FDI_DATA3 =
(select FDI_DATA from TEMP3_13175 b where (b.ACCT_DATE,b.COMP_CODE,b.DIMENSION2,b.DIMENSION3)
in (select a.ACCT_DATE,a.COMP_CODE,a.DIMENSION2,a.DIMENSION3 from TEMP_0006_131552))
where (a.ACCT_DATE,a.COMP_CODE,a.DIMENSION2,a.DIMENSION3)
in ( select ACCT_DATE,COMP_CODE,DIMENSION2,DIMENSION3 from TEMP3_13175)谁有好的方法来提升速度?
语句1把表B中存在但表C中不存在的记录插入表A
语句2更新同时存在于表A和表C中的记录,但是当时不存在这样的纪录,怎么回事?
语句3把表C中存在但表B中不存在的记录插入表A
也就是说如果某纪录同时存在于表B和表C,那就不会被插入,这样的逻辑恐怕不是为了除去重复吧?楼主把要求说清楚一些,应该很容易的。我的感觉是用一句insert就能完成。