qq_28809601 2016-11-23 03:04 采纳率: 0%
浏览 858

oralce语句如何优化查询速度,这个查询用了5秒左右。

SELECT CORP_NAME,WAREHOUSE_NAME,TO_CHAR(X.DAYS,'YYYY-MM-DD') AS DAYS ,NVL(SUM(X.MPIECES),0) TOTALPIECES,NVL(SUM(Y.MPIECES),0) SCANPIECES ,ROUND(NVL(SUM(Y.MPIECES),0)/NVL(SUM(X.MPIECES),1),2)*100||'%' RATE
,NVL(SUM(Z.LoadPieces),0) LOADTOTALPIECES,NVL(SUM(U.MPIECES),0) LOADSCANPIECES ,decode(SUM(Z.LoadPieces),0,0,ROUND(NVL(SUM(U.MPIECES),0)/NVL(SUM(Z.LoadPieces),1),2))*100||'%' LOADRATE
FROM (
SELECT C.CORP_NAME,B.WAREHOUSE_NAME,TRUNC(A.UPDATE_TIME) DAYS,A.MANIFEST_NO,SUM(PIECES) MPIECES
FROM WARE_STOCK_IN_OUT A
LEFT JOIN BASE_WAREHOUSE B ON A.WAREHOUSE_ID = B.PKID
LEFT JOIN BASE_BRANCH_CORP C ON B.CORP_ID = C.PKID
WHERE A.REMARK='出港入库' AND ACTION_TYPE=0
AND to_char(A.UPDATE_TIME,'hh24miss')>=to_char(:timeFrom,'hh24miss')
AND to_char(A.UPDATE_TIME,'hh24miss')<=to_char(:timeTo,'hh24miss')
GROUP BY TRUNC(A.UPDATE_TIME), B.WAREHOUSE_NAME,C.CORP_NAME,A.MANIFEST_NO
) X LEFT JOIN
(
SELECT TRUNC(A.UPDATE_TIME) DAYS ,A.MANIFEST_NO,COUNT(DISTINCT BARCODE) MPIECES
FROM WARE_CARGO_BARCODE_SCAN A
WHERE PURPOSE='StockIn' AND DELETE_FLAG IN (0,1)
{0}
GROUP BY TRUNC(A.UPDATE_TIME),A.MANIFEST_NO
) Y ON X.DAYS = Y.DAYS AND X.MANIFEST_NO = Y.MANIFEST_NO
LEFT JOIN (
SELECT A.MANIFEST_NO, LoadPieces-NVL(B.UnLoadPieces,0) AS LoadPieces
FROM (SELECT MANIFEST_NO,SUM(PIECES) AS LoadPieces
FROM WARE_CARRIER_USED
WHERE ACTION_TYPE = 0 AND REMARK in ('载具装货','出港入库')
GROUP BY MANIFEST_NO ) A
LEFT JOIN (SELECT MANIFEST_NO,SUM(PIECES) AS UnLoadPieces
FROM WARE_CARRIER_USED
WHERE ACTION_TYPE = 1 AND REMARK='载具卸货'
GROUP BY MANIFEST_NO ) B ON A.MANIFEST_NO = B.MANIFEST_NO
) Z ON X.MANIFEST_NO = Z.MANIFEST_NO
LEFT JOIN (
SELECT A.MANIFEST_NO,COUNT(DISTINCT A.BARCODE) MPIECES
FROM ware_cargo_barcode_scan A
LEFT JOIN (select barcode,max(update_time) lastUpdateTime
from ware_cargo_barcode_scan
where update_time > = :startTime and update_time< = :endTime
group by barcode ) B on a.barcode = b.barcode
WHERE a.update_time = b.lastUpdateTime and update_time > = :startTime and update_time< = :endTime
AND purpose = 'CarrierLoad' and DELETE_FLAG IN (0,1,5)
{0}
GROUP BY A.MANIFEST_NO) U ON X.MANIFEST_NO = U.MANIFEST_NO
WHERE 1=1 ", strWhere);图片说明

  • 写回答

1条回答 默认 最新

  • ServiceGood 2016-11-23 05:13
    关注

    F5 执行计划 看看有没有全表 或者那个花费大。多给条件加上索引建议组合索引。如果数据量很大的话 5秒还可以的

    评论

报告相同问题?

悬赏问题

  • ¥15 数学的三元一次方程求解
  • ¥20 iqoo11 如何下载安装工程模式
  • ¥15 本题的答案是不是有问题
  • ¥15 关于#r语言#的问题:(svydesign)为什么在一个大的数据集中抽取了一个小数据集
  • ¥15 C++使用Gunplot
  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 蓝桥杯单片机第十三届第一场,整点继电器吸合,5s后断开出现了问题