非常复杂的SQL语句,邀高手优化.我在线测试优化结果.并公布之,分数不是问题.

Question

如果非要这么写的话，建议增加排序缓冲区参数的值。
根本的解决办法是利用存储过程返回游标处理（我保证在1秒内返回结果）
给个例子
CREATE OR REPLACE PACKAGE pkg_test
AS
   TYPE myrctype IS REF CURSOR;
END pkg_test;
/
CREATE PROCEDURE sp_fauleinfo(pAgentID in varchar2,p_rc out pkg_test.myrctype)
AS
begin
open p_rc for 'Select Agent_id,Agent_name from failinfo where AgentID='||pAgentID;
end;

Answer 1

谁写这样的SQL语句该打 PP，也许设计数据库的人也该连罚，呵呵

Answer 2

我优化了一上午，现在查询全部数据14条耗时9秒，尽力了，结构所限。
要打PP要飞去印度打那几个最初设计数据库的印度软件工程硕士
结果如下
CREATE OR REPLACE VIEW VW_GUEST_BUSINESS AS
SELECT C.ROOM_NO, --房间号
       c.ROOM_TYPE_ID,
       E.RESV_SHARE_WITH_ID,
       E.NAME AS GUEST_NAME, --客人名
       H.GROUP_NAME ,
       L.NAME AS BUSINESS_NAME, --旅行社或者公司MINGZI
       L.business_id,
       E.GUEST_STATUS_ID,
       C.NO_OF_NIGHTS, --入住天数
       E.ACT_ARRIVAL_DATE,
       E.ARRIVAL_DATE,
       E.ACT_DEPART_DATE,
       E.DEPART_DATE,
       E.GROUP_ID,
       J.BUSINESS_CAT_ID, --商务分类（是公司还是旅行社）
       SUM(DECODE(SUBSTR(A.CHARGE_CODE_ID, 0, 2),
           '10',
            nvl(F.AMOUNT,0) + nvl(g.TAX_AMOUNT,0) ,
            0)
          ) AS RMRATEAMOUNT, --总房费
       SUM(nvl(F.AMOUNT,0) + nvl(g.TAX_AMOUNT,0) ) AS EPAMOUNT, --总消费（包括房费）
         E.IS_GROUP_MASTER, --是否团体主单
         K.RATE, --房价
         K.RATE_CODE_ID --房价代码
  FROM VW_TRANSACTION_MASTER   A,
       VW_RESERVATION          C,
       VW_RESV_SHARE_WITH_JOIN D,
       VW_RESV_SHARE_WITH      E,
       VW_TRANSACTION_SEQ      F,
      (SELECT TRANSACTION_SEQ_ID, SUM(NVL(AMOUNT,0)) TAX_AMOUNT
      FROM vw_TRANSACTION_TAX_JOIN
      WHERE TO_RESV_SHARE_WITH_ID IS NULL
      GROUP BY TRANSACTION_SEQ_ID) G,
       VW_GROUP_MASTER         H,
       CHARGE_CODE             I,
       VW_RESV_BUSINESS        J,
       (SELECT A.RATE_CODE_ID,A.RATE,A.RESV_SHARE_WITH_ID,TO_CHAR(MAX(TO_DATE(A.END_DATE,'DD/MM/YYYY')),'DD/MM/YYYY')
 FROM vw_resv_rate_plan A GROUP BY A.resv_share_with_ID,A.RATE_CODE_ID,A.RATE)
       K,
       BUSINESS_MASTER         L,
       room_TYPE_master        M
 WHERE L.BUSINESS_ID = J.BUSINESS_ID  AND
       j.RESV_BUSINESS_ID = e.RESV_BUSINESS_ID AND
       D.RESV_ID = C.RESV_ID AND
       E.RESV_SHARE_WITH_ID = D.RESV_SHARE_WITH_ID AND       
       E.GROUP_ID = H.GROUP_ID(+) AND
       C.ROOM_TYPE_ID = M.room_type_id AND
       M.function_guest_dummY='G' AND
       E.RESV_SHARE_WITH_ID = F.RESV_SHARE_WITH_ID(+) AND 
       (F.TRANSACTION_ID IS NULL OR (F.TRANSACTION_ID = A.TRANSACTION_ID))  and
       F.TRANSACTION_SEQ_ID = G.TRANSACTION_SEQ_ID(+) AND
       (I.CHARGE_CODE_ID = A.CHARGE_CODE_ID OR
           A.TRANSACTION_ID IS NULL) AND
        e.RESV_SHARE_WITH_ID = k.RESV_SHARE_WITH_ID and
       NVL(F.IS_MERGE, 'N') <> 'Y' AND NVL(I.IS_PAYMENT, 'N') <> 'Y' AND
       NVL(I.IS_AR, 'N') <> 'Y' 
 GROUP BY C.ROOM_NO,
          c.ROOM_TYPE_ID,
          E.RESV_SHARE_WITH_ID,
          E.NAME,
          E.GUEST_STATUS_ID,
          E.ARRIVAL_DATE,
          E.ACT_ARRIVAL_DATE,
          E.ACT_DEPART_DATE,
          E.DEPART_DATE,
          E.GROUP_ID,
          H.GROUP_NAME,
          E.IS_GROUP_MASTER,
          J.BUSINESS_CAT_ID,
          C.NO_OF_NIGHTS,
          L.NAME,
          L.business_id ,
          K.RATE, --房价
          K.RATE_CODE_ID
UNION ALL
SELECT '', --房间号
       K.ROOM_TYPE_ID,
       E.RESV_SHARE_WITH_ID,
       E.NAME AS GUEST_NAME, --客人名
       H.GROUP_NAME ,
       L.NAME AS BUSINESS_NAME, --旅行社或者公司MINGZI
       L.business_id,
       E.GUEST_STATUS_ID,
       0, --入住天数
       E.ACT_ARRIVAL_DATE,
       E.ARRIVAL_DATE,
       E.ACT_DEPART_DATE,
       E.DEPART_DATE,
       E.GROUP_ID,
       J.BUSINESS_CAT_ID, --商务分类（是公司还是旅行社）
       SUM(DECODE(SUBSTR(A.CHARGE_CODE_ID, 0, 2),
           '10',
            nvl(F.AMOUNT,0) + nvl(g.TAX_AMOUNT,0) ,
            0)
          ) AS RMRATEAMOUNT, --总房费
       SUM(nvl(F.AMOUNT,0) + nvl(g.TAX_AMOUNT,0) ) AS EPAMOUNT, --总消费（包括房费）
         E.IS_GROUP_MASTER, --是否团体主单
         K.RATE, --房价
         K.RATE_CODE_ID --房价代码
  FROM VW_TRANSACTION_MASTER   A,
       VW_RESV_SHARE_WITH      E,
       VW_TRANSACTION_SEQ      F,
      (SELECT TRANSACTION_SEQ_ID, SUM(NVL(AMOUNT,0)) TAX_AMOUNT
      FROM vw_TRANSACTION_TAX_JOIN
      WHERE TO_RESV_SHARE_WITH_ID IS NULL
      GROUP BY TRANSACTION_SEQ_ID) G,
       VW_GROUP_MASTER         H,
       CHARGE_CODE             I,
       VW_RESV_BUSINESS        J,
       vw_grproom_type_rate_plan_join  K,
       BUSINESS_MASTER         L
 WHERE L.BUSINESS_ID = J.BUSINESS_ID  AND
       j.RESV_BUSINESS_ID = e.RESV_BUSINESS_ID AND
       E.RESV_SHARE_WITH_ID = H.RESV_SHARE_WITH_ID(+) AND
       E.RESV_SHARE_WITH_ID = F.RESV_SHARE_WITH_ID(+) AND 
       (F.TRANSACTION_ID IS NULL OR (F.TRANSACTION_ID = A.TRANSACTION_ID))  and
       F.TRANSACTION_SEQ_ID = G.TRANSACTION_SEQ_ID(+) AND
       (I.CHARGE_CODE_ID = A.CHARGE_CODE_ID OR
           A.TRANSACTION_ID IS NULL) AND
       NVL(F.IS_MERGE, 'N') <> 'Y' AND NVL(I.IS_PAYMENT, 'N') <> 'Y' AND
       NVL(I.IS_AR, 'N') <> 'Y' and
       H.GROUP_ID = k.GROUP_ID and
       (nvl(e.ACT_DEPART_DATE,e.DEPART_DATE) = k.END_DATE ) 
 GROUP BY E.RESV_SHARE_WITH_ID,
          E.NAME,
          E.GUEST_STATUS_ID,
          E.ARRIVAL_DATE,
          E.ACT_ARRIVAL_DATE,
          E.ACT_DEPART_DATE,
          E.DEPART_DATE,
          E.GROUP_ID,
          H.GROUP_NAME,
          E.IS_GROUP_MASTER,
          J.BUSINESS_CAT_ID,
          L.NAME,
          L.business_id ,
          K.RATE, --房价
          K.RATE_CODE_ID,
          K.ROOM_TYPE_ID