下面这个SQL,执行时间15.8分钟:
SELECT P.APPLY_POLICY_NO,
MIN(CASEWHEN H.STATUS_CODE = 'QP001' THEN
H.OPERATION_START_DATE
ELSE NULL
END) AS DATE_CONFIRMATION,
MIN(CASE
WHEN H.STATUS_CODE = 'QP002' THEN
H.OPERATION_START_DATE
ELSE NULL
END) AS DATE_FIRST_QUOTATION
FROM PNBS_OPERATION_HISTORY H, QT_PLAN_INFO P, QT_BASE_INFO N
WHERE N.ID_QT_BASE_INFO = P.ID_QT_BASE_INFO
AND N.QUOTATION_NO = H.DOCUMENT_NO
AND H.PROCESS_TYPE = 'P01'
AND H.STATUS_CODE IN ('QP001', 'QP002')
AND H.DATE_CREATED >= ADD_MONTHS(TO_DATE('20150111', 'YYYYMMDD'), -3)
AND H.DATE_CREATED < TO_DATE('20150112', 'YYYYMMDD