select    TO_DATE(V_DATE,'YYYYMMDD'),     --数据日期
            A.PLAN_ID,                           --定制编号
            null ,                          --机构编号
            A.INST_ID ,                     --机构ID
            NULL,                          --机构名称
            null,                           --机构级别代码
            A.DEPT_BAL ,                    --基期储蓄余额
            A.DEPT_AVG ,                    --基期储蓄日均
            A.CARD_AVG ,                    --基期卡日均
            A.CARD_BAL                      --基期卡余额
            FROM (SELECT D.INST_ID --机构编号
                         ,C.PLAN_ID --定制编号
                         ,SUM(B.AMAL_YEAR/TO_NUMBER(B.ETL_DATE-TRUNC(B.ETL_DATE,'YY')+1)) DEPT_AVG--基期储蓄日均
                         ,SUM(CASE WHEN TRIM(B.PRIM_ACCT_NUM) NOT IN (SELECT TRIM(PRIM_ACCT_NUM) FROM CRM_M_CARD /* 23万条数据左右*/) THEN 0 ELSE B.AMAL_YEAR/TO_NUMBER(B.ETL_DATE-TRUNC(B.ETL_DATE,'YY')+1) END) CARD_AVG--基期卡日均
                         ,SUM(BALANCE) DEPT_BAL --基期储蓄余额
                         ,SUM(CASE WHEN TRIM(B.PRIM_ACCT_NUM) NOT IN (SELECT TRIM(PRIM_ACCT_NUM) FROM CRM_M_CARD) THEN 0 ELSE BALANCE END) CARD_BAL --基期卡余额
                  FROM  CRM_G_DEPT_AMAL       B--100万条数据左右
                       ,CRM_A_PLANCARD_TEMP10 C--三条数据
                       ,CRM_M_BRANCH          D--14条数据
                  WHERE B.ETL_DATE=C.ETL_DATE-1 
                        AND SUBSTR(TRIM(B.PRIM_ACCT_NUM),1,5)=D.INST_NO
                  GROUP BY  D.INST_ID
                           ,C.PLAN_ID) A ;
这个sql运行了一个多小时都出不来,请高人帮忙优化下,谢谢   

解决方案 »

  1.   

    从SQL角度来看,感觉你的SQL里只有一个位置是影响速度的,就是两个IN子查询语句TRIM(B.PRIM_ACCT_NUM) NOT IN
       (SELECT TRIM(PRIM_ACCT_NUM) FROM CRM_M_CARD /* 23万条数据左右*/) THEN建议将该子查询改为以下结构         SUM(CASE
                   WHEN E.PRIM_ACCT_NUM IS NULL
             ......
        FROM CRM_G_DEPT_AMAL       B, --100万条数据左右
             CRM_A_PLANCARD_TEMP10 C, --三条数据
             CRM_M_BRANCH          D, --14条数据
             (SELECT DISTINCT TRIM(PRIM_ACCT_NUM) PRIM_ACCT_NUM FROM CRM_M_CARD) E
       WHERE B.ETL_DATE = C.ETL_DATE - 1
         AND SUBSTR(TRIM(B.PRIM_ACCT_NUM), 1, 5) = D.INST_NO
         AND B.PRIM_ACCT_NUM = E.PRIM_ACCT_NUM (+)
      

  2.   

    恩  同意楼上的2个in
    另外
    CRM_G_DEPT_AMAL的查询字段加索引
      

  3.   

    这个SQL应该不是视图吧
    如果是数据迁移的话,可以换种实现方式,比如将其中NOT IN内的子句生成一定条件的临时表
    1是更清晰,2是性能效率更高
    仅是建议
      

  4.   

    --not in 换成not exists试试
    --还有,在你必要的连接字段上加上index
    select TO_DATE(V_DATE,'YYYYMMDD'), --数据日期
      A.PLAN_ID, --定制编号
      null , --机构编号
      A.INST_ID , --机构ID
      NULL, --机构名称
      null, --机构级别代码
      A.DEPT_BAL , --基期储蓄余额
      A.DEPT_AVG , --基期储蓄日均
      A.CARD_AVG , --基期卡日均
      A.CARD_BAL --基期卡余额
      FROM (SELECT D.INST_ID --机构编号
      ,C.PLAN_ID --定制编号
      ,SUM(B.AMAL_YEAR/TO_NUMBER(B.ETL_DATE-TRUNC(B.ETL_DATE,'YY')+1)) DEPT_AVG--基期储蓄日均
      ,SUM(CASE WHEN  NOT EXISTS(SELECT 1 FROM CRM_M_CARD WHERE TRIM(PRIM_ACCT_NUM)=TRIM(B.PRIM_ACCT_NUM)) THEN 0 ELSE B.AMAL_YEAR/TO_NUMBER(B.ETL_DATE-TRUNC(B.ETL_DATE,'YY')+1) END) CARD_AVG--基期卡日均
      ,SUM(BALANCE) DEPT_BAL --基期储蓄余额
      ,SUM(CASE WHEN  NOT EXISTS(SELECT 1 FROM CRM_M_CARD WHERE TRIM(PRIM_ACCT_NUM)=TRIM(B.PRIM_ACCT_NUM)) THEN 0 ELSE BALANCE END) CARD_BAL --基期卡余额
      FROM CRM_G_DEPT_AMAL B--100万条数据左右
      ,CRM_A_PLANCARD_TEMP10 C--三条数据
      ,CRM_M_BRANCH D--14条数据
      WHERE B.ETL_DATE=C.ETL_DATE-1  
      AND SUBSTR(TRIM(B.PRIM_ACCT_NUM),1,5)=D.INST_NO
      GROUP BY D.INST_ID
      ,C.PLAN_ID) A ;
      

  5.   

    sum哪裡效率超低啊,估計做索引也沒有用。
      

  6.   

    光看sql语句是不知道哪里有性能问题的,你要得到该语句的执行计划,然后分析其中cost较高的步骤,这样才能对症下药。