Oracle 查询语句优化方案

[Tz_Sword]

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

[Tz_Sword]

我上面这个SQL 没有问题 当APSIS_PART 很多时 目前检测 2W条记录时 查询时间大概有1分钟 太慢了。

[Tz_Sword]

及时结贴 求大神支招

[sych888]

CASE WHEN 部分能否抽像成 WITH？

[小灰狼W]

列出执行计划看看

[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

[Tz_Sword]

引用 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 里面了 结果也还行

[Tz_Sword]

同时也感谢 其他小伙伴

[Tiger_Zhao]

SELECT 子句中不要用子查询，数据量大了性能下降很明显。

[卖水果的net]

LZ 能提供一些基础数据吗？ 不大好分析 。

[Tz_Sword]

我把表 简单抽象出来了

[Tz_Sword]

最终要的结果为: 标识(产品表)、类型(产品表)、价格1、价格2、价格3、价格(基本价格表)、最低价格(基本价格表)、最高价格(基本价格表) 价格1：当产品表 类型为01时 取政策编号为01 且价格政策从表录入时间最新的且状态为0 的政策价格 价格2：当产品表 类型为02时 取政策编号为02 且价格政策从表录入时间最新的且状态为0 的政策价格 价格3：当产品表 类型为02时 取政策编号为03 且价格政策从表录入时间最新的且状态为0 的政策价格

[Tz_Sword]

我的上面语句为 select ... case when then price1 case when then price2 case when then price3 from left join .... case when then 部分很消耗时间 特别是 产品表数据大的时候 产品表 2W条记录 政策表 20条记录 政策从表 6000条记录 基本价格表 1000条记录