Oracle 查询语句优化方案

Tz_Sword 2015-04-13 05:05:52
APSIS_PART 产品主表
SALES_POLICY_BASE_PRICE 价格基本表
SALES_POLICY_PRICE 价格政策表
SALES_POLICY_PRICE_ENTRY 产品价格表

DISTRIBUTION_CHANNEL_CODE为“002”的,取价格政策 为“apsis001 ” 且 EFFECTIVE_DATE最大的一条 PRICE
DISTRIBUTION_CHANNEL_CODE为“001”的,取价格政策 为“2sOC” 且 EFFECTIVE_DATE最大的一条 PRICE
DISTRIBUTION_CHANNEL_CODE为“001”的,取价格政策 为“Ds2sSC”或“2sSC” 且 EFFECTIVE_DATE最大的一条 PRICE


SELECT ERP_PART_NAME,ERP_CAR_MODEL,AUTO_BRAND_CODE_ALL,OEM_PART_CODE_ALL ,
ERP_ASSIST_NAME,APSIS_PART_UNIT_NAME,APSIS_PART_CODE,DISTRIBUTION_CHANNEL_NAME,PART_BRAND_NAME,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '002' THEN
(SELECT PRICE FROM (
SELECT MATERIAL_ID,PRICE,SPPE.EFFECTIVE_DATE,SALE_ORG_CODE,CODE
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='0101' AND CODE='apsis001'
ORDER BY EFFECTIVE_DATE
) WHERE MATERIAL_ID = AP.ID AND ROWNUM=1)
END) as SALE_PRICE,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '001' THEN
(SELECT PRICE FROM (
SELECT MATERIAL_ID,PRICE,SPPE.EFFECTIVE_DATE,SALE_ORG_CODE,CODE
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='01' AND CODE='2sOC'
ORDER BY EFFECTIVE_DATE
) WHERE MATERIAL_ID = AP.ID AND ROWNUM=1)
END) as OC_PRICE,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '001' THEN
(SELECT PRICE FROM (
SELECT MATERIAL_ID,PRICE,SPPE.EFFECTIVE_DATE,SALE_ORG_CODE,CODE
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='01' AND (CODE='Ds2sSC' OR CODE='2sOC')
ORDER BY EFFECTIVE_DATE
) WHERE MATERIAL_ID = AP.ID AND ROWNUM=1)
END) as SC_PRICE,
PRICE,FLOOR_PRICE,MAX_PRICE ,MINIMUM_PACKING
FROM APSIS_PART AP
LEFT JOIN (
SELECT PRICE,FLOOR_PRICE,MAX_PRICE,MATERIAL_ID FROM SALES_POLICY_BASE_PRICE
WHERE AUDIT_FLAG='1' AND BLOCKED_FLAG='0') SPBP
ON SPBP.MATERIAL_ID=AP.ID
ORDER BY ERP_PART_NAME DESC,AUTO_BRAND_CODE_ALL DESC
...全文
290 12 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
Tz_Sword 2015-04-14
  • 打赏
  • 举报
回复
我的上面语句为 select ... case when then price1 case when then price2 case when then price3 from left join .... case when then 部分很消耗时间 特别是 产品表数据大的时候 产品表 2W条记录 政策表 20条记录 政策从表 6000条记录 基本价格表 1000条记录
Tz_Sword 2015-04-14
  • 打赏
  • 举报
回复
最终要的结果为: 标识(产品表)、类型(产品表)、价格1、价格2、价格3、价格(基本价格表)、最低价格(基本价格表)、最高价格(基本价格表) 价格1:当产品表 类型为01时 取政策编号为01 且价格政策从表录入时间最新的且状态为0 的政策价格 价格2:当产品表 类型为02时 取政策编号为02 且价格政策从表录入时间最新的且状态为0 的政策价格 价格3:当产品表 类型为02时 取政策编号为03 且价格政策从表录入时间最新的且状态为0 的政策价格
Tz_Sword 2015-04-14
  • 打赏
  • 举报
回复

我把表 简单抽象出来了
卖水果的net 2015-04-14
  • 打赏
  • 举报
回复
LZ 能提供一些基础数据吗? 不大好分析 。
Tiger_Zhao 2015-04-14
  • 打赏
  • 举报
回复
SELECT 子句中不要用子查询,数据量大了性能下降很明显。
Tz_Sword 2015-04-14
  • 打赏
  • 举报
回复
同时也感谢 其他小伙伴
Tz_Sword 2015-04-14
  • 打赏
  • 举报
回复
引用 9 楼 Tiger_Zhao 的回复:
     SELECT ERP_PART_NAME,ERP_CAR_MODEL,AUTO_BRAND_CODE_ALL,OEM_PART_CODE_ALL ,
       ERP_ASSIST_NAME,APSIS_PART_UNIT_NAME,APSIS_PART_CODE,DISTRIBUTION_CHANNEL_NAME,PART_BRAND_NAME,
       (CASE WHEN DISTRIBUTION_CHANNEL_CODE = '002' THEN 
                t1.PRICE
              END) as SALE_PRICE,
       (CASE WHEN DISTRIBUTION_CHANNEL_CODE = '001' THEN
                t2.PRICE
             END) as OC_PRICE, 
       (CASE WHEN DISTRIBUTION_CHANNEL_CODE = '001' THEN
                t3.PRICE 
              END) as SC_PRICE,
      PRICE,FLOOR_PRICE,MAX_PRICE ,MINIMUM_PACKING  
      FROM APSIS_PART AP
      LEFT JOIN (
           SELECT PRICE,FLOOR_PRICE,MAX_PRICE,MATERIAL_ID FROM SALES_POLICY_BASE_PRICE 
           WHERE  AUDIT_FLAG='1' AND BLOCKED_FLAG='0') SPBP 
      ON SPBP.MATERIAL_ID=AP.ID
      LEFT JOIN (
              SELECT MATERIAL_ID,PRICE,
                     ROW_NUMBER() OVER(PARTITION BY MATERIAL_ID ORDER BY EFFECTIVE_DATE) rn
							FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP 
							WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
							AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
							AND SALE_ORG_CODE='0101' AND CODE='apsis001' 
                ) t1
        ON t1.MATERIAL_ID = AP.ID AND t1.rn = 1
      LEFT JOIN (
              SELECT MATERIAL_ID,PRICE,
                     ROW_NUMBER() OVER(PARTITION BY MATERIAL_ID ORDER BY EFFECTIVE_DATE) rn
							FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP 
							WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
							AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
							AND SALE_ORG_CODE='01' AND CODE='2sOC' 
                ) t2
        ON t2.MATERIAL_ID = AP.ID AND t2.rn = 1
      LEFT JOIN (
              SELECT MATERIAL_ID,PRICE,
                     ROW_NUMBER() OVER(PARTITION BY MATERIAL_ID ORDER BY EFFECTIVE_DATE) rn
							FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP 
							WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
							AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
							AND SALE_ORG_CODE='01' AND (CODE='Ds2sSC' OR CODE='2sOC')
                ) t3
        ON t3.MATERIAL_ID = AP.ID AND t3.rn = 1

      ORDER BY ERP_PART_NAME DESC,AUTO_BRAND_CODE_ALL DESC
灰常感谢 解决问题 我也想了一个思路 我的是 left join SALES_POLICY_PRICE_ENTRY ,SALES_POLICY_PRICE这两张表 按照产品ID分组的价格 然后我把 SALE_ORG_CODE='01' AND (CODE='Ds2sSC' OR CODE='2sOC') 这些条件 写在when 里面了 结果也还行
Tiger_Zhao 2015-04-14
  • 打赏
  • 举报
回复
     SELECT ERP_PART_NAME,ERP_CAR_MODEL,AUTO_BRAND_CODE_ALL,OEM_PART_CODE_ALL ,
ERP_ASSIST_NAME,APSIS_PART_UNIT_NAME,APSIS_PART_CODE,DISTRIBUTION_CHANNEL_NAME,PART_BRAND_NAME,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '002' THEN
t1.PRICE
END) as SALE_PRICE,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '001' THEN
t2.PRICE
END) as OC_PRICE,
(CASE WHEN DISTRIBUTION_CHANNEL_CODE = '001' THEN
t3.PRICE
END) as SC_PRICE,
PRICE,FLOOR_PRICE,MAX_PRICE ,MINIMUM_PACKING
FROM APSIS_PART AP
LEFT JOIN (
SELECT PRICE,FLOOR_PRICE,MAX_PRICE,MATERIAL_ID FROM SALES_POLICY_BASE_PRICE
WHERE AUDIT_FLAG='1' AND BLOCKED_FLAG='0') SPBP
ON SPBP.MATERIAL_ID=AP.ID
LEFT JOIN (
SELECT MATERIAL_ID,PRICE,
ROW_NUMBER() OVER(PARTITION BY MATERIAL_ID ORDER BY EFFECTIVE_DATE) rn
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='0101' AND CODE='apsis001'
) t1
ON t1.MATERIAL_ID = AP.ID AND t1.rn = 1
LEFT JOIN (
SELECT MATERIAL_ID,PRICE,
ROW_NUMBER() OVER(PARTITION BY MATERIAL_ID ORDER BY EFFECTIVE_DATE) rn
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='01' AND CODE='2sOC'
) t2
ON t2.MATERIAL_ID = AP.ID AND t2.rn = 1
LEFT JOIN (
SELECT MATERIAL_ID,PRICE,
ROW_NUMBER() OVER(PARTITION BY MATERIAL_ID ORDER BY EFFECTIVE_DATE) rn
FROM SALES_POLICY_PRICE_ENTRY SPPE,SALES_POLICY_PRICE SPP
WHERE SPPE.PARENT_ID = SPP.ID AND SPPE.BLOCKED_STATUS='0'
AND SPPE.EFFECTIVE_DATE<=SYSDATE AND SPPE.EXPIRE_DATE>=SYSDATE
AND SALE_ORG_CODE='01' AND (CODE='Ds2sSC' OR CODE='2sOC')
) t3
ON t3.MATERIAL_ID = AP.ID AND t3.rn = 1

ORDER BY ERP_PART_NAME DESC,AUTO_BRAND_CODE_ALL DESC

小灰狼W 2015-04-14
  • 打赏
  • 举报
回复
列出执行计划看看
sych888 2015-04-14
  • 打赏
  • 举报
回复
CASE WHEN 部分能否抽像成 WITH?
Tz_Sword 2015-04-13
  • 打赏
  • 举报
回复
及时结贴 求大神支招
Tz_Sword 2015-04-13
  • 打赏
  • 举报
回复
我上面这个SQL 没有问题 当APSIS_PART 很多时 目前检测 2W条记录时 查询时间大概有1分钟 太慢了。

17,382

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧