使用Hint优化复杂SQL案例-2

下面这个SQL，执行时间15.8分钟：

SELECT P.APPLY_POLICY_NO,

       MIN(CASE
             WHEN 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')

 GROUP BY P.APPLY_POLICY_NO;

执行计划如下：

执行计划的第一步的估值错误（115K VS 17M），导致整个执行计划不够优化。根据实际返回的行数，3个表之间做hash join是最佳选择，通过Hint控制执行计划：

SELECT /*+ leading(H N P) use_hash(N) use_hash(P) */
P.APPLY_POLICY_NO,
       MIN(CASE
             WHEN 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
   ANDH.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')
 GROUP BY P.APPLY_POLICY_NO;

执行计划如下：

增加Hint后SQL执行时间为4分28秒，性能提升3.5倍。

为什么优化器估算行源误差较大？

因为H表上存在多个谓词条件，而且表上没有收集直方图信息，这种情况就非常容易出现估值偏差。

类似这种SQL，收集表上的直方图信息可能会帮助优化器做出正确的行源估算，但是如果字段间存在关联关系，可能还需要创建扩展统计信息，才能帮助优化器对行源正确估值。这些方法需要在了解业务的情况下使用。