SQL:
select A.SSEM_voyage_id,
sum(A.HC_CONTAINER_COUNTER) pre_SUM_HC
from (select SSEM_voyage_id,
sum(decode(SECR_cnt_type, 'HC', 1, 0)) HC_CONTAINER_COUNTER
from SExportManifest
left join SContainerRequirement
on SSEM_exp_bl_id = SECR_exp_bl_id
and secr_org_id = :"1"
where SSEM_SC_BEFORE_AFTER_FLAG in (0, 2, 4)
and (:"2" IS NULL OR SSEM_voyage_id = :"7")
and (:"3" IS NULL OR :"1" IS NULL OR
SSEM_modify_time between
TO_DATE(:"4", 'YYYY-MM-DD HH24:MI:SS') and
TO_DATE(:"5", 'YYYY-MM-DD HH24:MI:SS'))
and ssem_Org_Id = :"6"
group by
SSEM_voyage_id,
SSEM_exp_bl_id
) A
group by A.SSEM_voyage_id目前执行计划中SContainerRequirement是全表扫描,SExportManifest是TABLE ACCESS BY GLOBAL INDEX ROWID,2张表都是业务表,所以执行起来肯定慢了,大家有没有好的解决方案
select A.SSEM_voyage_id,
sum(A.HC_CONTAINER_COUNTER) pre_SUM_HC
from (select SSEM_voyage_id,
sum(decode(SECR_cnt_type, 'HC', 1, 0)) HC_CONTAINER_COUNTER
from SExportManifest
left join SContainerRequirement
on SSEM_exp_bl_id = SECR_exp_bl_id
and secr_org_id = :"1"
where SSEM_SC_BEFORE_AFTER_FLAG in (0, 2, 4)
and (:"2" IS NULL OR SSEM_voyage_id = :"7")
and (:"3" IS NULL OR :"1" IS NULL OR
SSEM_modify_time between
TO_DATE(:"4", 'YYYY-MM-DD HH24:MI:SS') and
TO_DATE(:"5", 'YYYY-MM-DD HH24:MI:SS'))
and ssem_Org_Id = :"6"
group by
SSEM_voyage_id,
SSEM_exp_bl_id
) A
group by A.SSEM_voyage_id目前执行计划中SContainerRequirement是全表扫描,SExportManifest是TABLE ACCESS BY GLOBAL INDEX ROWID,2张表都是业务表,所以执行起来肯定慢了,大家有没有好的解决方案
解决方案 »
- 急求:请各位仁兄帮帮看看这个SQL怎么回事?在Ibatis里始终没法通过,而在PL/SQL中单独执行完全没有问题。
- 使用PL\SQL连接oracle10g数据库出现错误,求指教~~!
- dbconsole不能启动
- 将多条记录合并为一条 oracle (记录数不定,如何sql动态实现)
- 五一将至,先提前预祝坛子里面的兄弟姐妹们节日快乐
- 提示错误"ORA-01502: 索引''或这类索引的分区处于不可用状态"
- oracle 10g Net Manager 连接问题
- 一个统计视图,如何实现?
- 怎样从oracle的表里导出数据
- 请问哪里有关于oracle存储过程的资料下载?
- oracle中游标是否可以和sql server一样取得任意位置上的数据行?
- 连接持续增加,不能释放,请帮忙看看是不是递归引起,如何解决?
不然你可以改成exists
SSEM_exp_bl_id SECR_exp_bl_id 两列有索引,就可以了