请如何优化下面的SQL,select速度0.1秒(数据量为300条),整个SQL执行为34.4秒。
insert into fx_accountdetails (id, checkcash, invoiceid, detailid, bookid, scale, netamount, amount, checkscale, checkdiscount, checkamount, invoicetype, modifytime) select SYS_GUID(), 'F00049085', v.invoiceid, v.id, v.bookid, v.scale, v.netamount, v.amount, v.scale - nvl(v.accountscale, 0), v.discount, (v.scale - nvl(v.accountscale, 0)) * (v.netamount / v.scale) * v.discount / 100, v.invoicetype, sysdate from d_r_detailview v where v.invoiceid in ('D00062888','D00062889','D00062890','D00062891','D00062893','D00062894','D00062895','D00062896','D00062897','D00062899') and v.scale > nvl(v.accountscale, 0) and not exists (select 1 from fx_accountdetails where checkcash = 'F00049085' and invoiceid = v.invoiceid)
insert into fx_accountdetails (id, checkcash, invoiceid, detailid, bookid, scale, netamount, amount, checkscale, checkdiscount, checkamount, invoicetype, modifytime) select SYS_GUID(), 'F00049085', v.invoiceid, v.id, v.bookid, v.scale, v.netamount, v.amount, v.scale - nvl(v.accountscale, 0), v.discount, (v.scale - nvl(v.accountscale, 0)) * (v.netamount / v.scale) * v.discount / 100, v.invoicetype, sysdate from d_r_detailview v where v.invoiceid in ('D00062888','D00062889','D00062890','D00062891','D00062893','D00062894','D00062895','D00062896','D00062897','D00062899') and v.scale > nvl(v.accountscale, 0) and not exists (select 1 from fx_accountdetails where checkcash = 'F00049085' and invoiceid = v.invoiceid)
解决方案 »
- 某两列值相同,则把数据记录合并成一条,请教怎么完成?
- oracle同一个方案下的表,有的能访问,有的不能访问到为什么呢?
- C#下能用thin方式连接oracle数据库?oracle8.17是否有64位的版本?
- 我查不到记录了,但数据库中确实是有记录的
- 在同一张表上建两个类型一样的trigger会不会有问题
- Oracle 新手,求助各位前辈!
- 如何将一个Oracle的数据文件挂到数据库上
- ORA-00036: 超过递归 SQL (50) 级的最大值
- 请问oem到底提供了那些可示化操作功能?oem的用户为森么部时数据库用户!
- 查询结果按照特定值在前排序
- oracle 怎么把表的数据和注释一起导入到另外一个用户下的表中?
- in out存储过程如何调用
全部记录有多少条?
看看fx_accountdetails表上是否有触发器
USING ( select SYS_GUID(), 'F00049085', v.invoiceid, v.id, v.bookid, v.scale, v.netamount, v.amount, v.scale - nvl(v.accountscale, 0),
v.discount, (v.scale - nvl(v.accountscale, 0)) * (v.netamount / v.scale) * v.discount / 100, v.invoicetype, sysdate
from d_r_detailview v
where v.invoiceid in ('D00062888','D00062889','D00062890','D00062891','D00062893','D00062894','D00062895','D00062896','D00062897','D00062899')
and v.scale > nvl(v.accountscale, 0) ) B
ON ( checkcash = 'F00049085' and invoiceid = v.invoiceid )
WHEN MATCHED THEN INSERT(A.id, A.checkcash, A.invoiceid, A.detailid, A.bookid, A.scale, A.netamount, A.amount, A.checkscale, A.
A.checkdiscount, A.checkamount, A.invoicetype, A.modifytime)
VALUES(SYS_GUID(), 'F00049085', B.invoiceid, B.id, B.bookid, B.scale, B.netamount, B.amount, B.scale - nvl(B.accountscale, 0),
B.discount, (B.scale - nvl(B.accountscale, 0)) * (B.netamount / B.scale) * B.discount / 100, B.invoicetype, sysdate );
fx_accountdetails 表上面触发器、索引去掉看看~