同事发来一个核心业务库的TOP SQL，占据并浪费了大量的服务器CPU资源，此SQL执效率信息如下：

SELECT a.ID,
       a.SEG_FLIGHT_ID,
       a.SEG_DEP_STATION_CODE,
       a.SEG_ARR_STATION_CODE,
       a.SEG_CLASS_SEQUENCE,
       a.SEG_TRAFFIC_RESTRICTION_CODE,
       a.SEG_DEP_TERMINAL,
       a.SEG_ARR_TERMINAL,
       a.SEG_OC_TYPE,
       a.SEG_OC_MAP_NUMBER,
       a.SEG_OC_CLASS_MAPPING,
       a.SEG_DATA_SOURCE
FROM T_SEGMENT a,
  (SELECT id
   FROM
     (SELECT t.id
      FROM T_FLIGHT t,
           T_TASK ta
      WHERE 1=1
        AND ta.id IN ('11008',
                      '11003',
                      '11014',
                      '11012',
                      '14001',
                      '11001')
        AND t.FLT_AIRLINE_CODE = ta.FLT_AIRLINE_CODE
        AND t.FLT_FLIGHT_NO = ta.FLT_FLIGHT_NO
        AND t.FLT_FLIGHT_DATE = ta.FLT_FLIGHT_DATE
        AND t.FLT_OC_FLIGHT_ID IS NULL
        AND t.IS_VALID = 1
        AND nullif('AAAA',t.FLT_OFFICE) IS NOT NULL )
   LEFT JOIN t_dep_schema s ON id = s.flt_id
   WHERE s.flt_id IS NULL
     OR (s.flt_id IS NOT NULL
         AND (s.FLT_IN_TCARD_PROCESS_FLAG ='N'
              OR s.FLT_IN_TCARD_PROCESS_FLAG IS NULL))
     OR (s.flt_id IS NOT NULL
         AND (s.FLT_IN_INIT_PROCESS_FLAG IS NULL
              OR s.FLT_IN_INIT_PROCESS_FLAG ='N')) ) f
WHERE 1=1
  AND a.SEG_FLIGHT_ID = f.ID
  AND a.IS_VALID = 1

SQL执行计划如下：

从执行计划中看到，估算行数与实际行数差别巨大，实际行数只是899行，但是NESTED LOOP之后估算结果集为46K，并且TASK表与S表选择了HASH JOIN，在未收集个性化扩展统计信息的情况，针对这种复杂情况，估算不准的情况是比较常见的，通常需要利用HINT来调整表连接顺序来优化。此SQL的涉及表都不存在索引缺失的情况，只是行数估算错误导致表连接顺序有误，从而选择了低效的执行计划。