有关Sql order by 优化查询的建议

在使用左，右，或者内连接的时候，在需要使用排序的时候，不妨先以一个表为标准，先进行排序，这样可以提供语句的性能
比如:
   优化前:
      SELECT *
  FROM (SELECT a.*, ROW_NUMBER() OVER(ORDER BY createDate desc) row_num
          FROM (SELECT a.ID as id,
                       a.CODE as code,
                       a.LAST_STORE_JOB_ID as lastStoreJobId,
                       a.JOB_DATE as jobDate,
                       a.JOB_STORE_ID as jobStoreId,
                       c.NAME as jobStoreName,
                       a.TRANSFER_STORE_ID as transferStoreId,
                       d.NAME as transferStoreName,
                       a.STORE_COMPANY_ID as storeCompanyId,
                       e.COMPANY_FULLNAME as companyName,
                       a.EXTRACT_COMPANY_ID as extractCompanyId,
                       f.COMPANY_FULLNAME as extractCompanyName,
                       a.SOURCE_TYPE as sourceType,
                       a.EXTERNAL_CODE as externalCode,
                       a.REMARK as remark,
                       a.ALLOT_TYPE as allotType,
                       a.ALLOT_STATUS as allotStatus,
                       a.STATUS as status,
                       a.PDA_STATUS as pdaStatus,
                       a.JOB_TYPE_ID as jobTypeId,
                       a.ACTION_TYPE_ID as actionTypeId,
                       a.SOLVE_STATUS as solveStatus,
                       a.UPDATE_DATE as updateDate,
                       a.CREATE_DATE as createDate,
                       a.VEHICLE_CODE as vehicleCode,
                       a.USER_ID as userId,
                       g.USER_NAME as userName,
                       a.FLOW_STATUS as flowStatus,
                       a.TARGET_ALLOCATION_ID as targetAllocationId,
                       h.NAME as allocationName,
                       (select count(*)
                          FROM STORE_JOB_FLOW_NODES i
                          left join STORE_JOB_FLOW_MODES j
                            on j.id = i.JOB_FLOW_MODE_ID
                           and i.ACTION_TYPE_ID = 'HWMAN02'
                          left join STORE_JOB_FLOWS k
                            on k.job_flow_mode_id = j.id
                         where k.STORE_JOB_ID = a.ID) as nextIsPlan
                  FROM  STORE_JOBS  a                  LEFT JOIN STORE_JOBS b
                    on a.last_store_job_id = b.id
                  LEFT JOIN STORES c
                    on a.job_store_id = c.id
                  LEFT JOIN STORES d
                    on a.transfer_store_id = d.id
                  LEFT JOIN COMPANYS e
                    on a.store_company_id = e.id
                  LEFT JOIN COMPANYS f
                    on a.extract_company_id = f.id
                  LEFT JOIN USERS g
                    on a.user_id = g.id
                  LEFT JOIN ALLOCATIONS h
                    on a.target_allocation_id = h.id
                 where 1 = 1
                   AND a.ACTION_TYPE_ID = 'HWMAN01'
                   AND a.JOB_DATE between
                       to_date('2014-07-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
                       to_date('2014-08-02 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                   AND a.JOB_TYPE_ID = 'HWMSN02'
                  order by a.CREATE_DATE desc ) a
                   )
WHERE row_num BETWEEN 1 AND 30
ORDER BY createDate desc

优化后:
     SELECT *
  FROM (SELECT a.*, ROW_NUMBER() OVER(ORDER BY createDate desc) row_num
          FROM (SELECT a.ID as id,
                       a.CODE as code,
                       a.LAST_STORE_JOB_ID as lastStoreJobId,
                       a.JOB_DATE as jobDate,
                       a.JOB_STORE_ID as jobStoreId,
                       c.NAME as jobStoreName,
                       a.TRANSFER_STORE_ID as transferStoreId,
                       d.NAME as transferStoreName,
                       a.STORE_COMPANY_ID as storeCompanyId,
                       e.COMPANY_FULLNAME as companyName,
                       a.EXTRACT_COMPANY_ID as extractCompanyId,
                       f.COMPANY_FULLNAME as extractCompanyName,
                       a.SOURCE_TYPE as sourceType,
                       a.EXTERNAL_CODE as externalCode,
                       a.REMARK as remark,
                       a.ALLOT_TYPE as allotType,
                       a.ALLOT_STATUS as allotStatus,
                       a.STATUS as status,
                       a.PDA_STATUS as pdaStatus,
                       a.JOB_TYPE_ID as jobTypeId,
                       a.ACTION_TYPE_ID as actionTypeId,
                       a.SOLVE_STATUS as solveStatus,
                       a.UPDATE_DATE as updateDate,
                       a.CREATE_DATE as createDate,
                       a.VEHICLE_CODE as vehicleCode,
                       a.USER_ID as userId,
                       g.USER_NAME as userName,
                       a.FLOW_STATUS as flowStatus,
                       a.TARGET_ALLOCATION_ID as targetAllocationId,
                       h.NAME as allocationName,
                       (select count(*)
                          FROM STORE_JOB_FLOW_NODES i
                          left join STORE_JOB_FLOW_MODES j
                            on j.id = i.JOB_FLOW_MODE_ID
                           and i.ACTION_TYPE_ID = 'HWMAN02'
                          left join STORE_JOB_FLOWS k
                            on k.job_flow_mode_id = j.id
                         where k.STORE_JOB_ID = a.ID) as nextIsPlan
                  FROM (SELECT * FROM STORE_JOBS order by CREATE_DATE desc) a                  LEFT JOIN STORE_JOBS b
                    on a.last_store_job_id = b.id
                  LEFT JOIN STORES c
                    on a.job_store_id = c.id
                  LEFT JOIN STORES d
                    on a.transfer_store_id = d.id
                  LEFT JOIN COMPANYS e
                    on a.store_company_id = e.id
                  LEFT JOIN COMPANYS f
                    on a.extract_company_id = f.id
                  LEFT JOIN USERS g
                    on a.user_id = g.id
                  LEFT JOIN ALLOCATIONS h
                    on a.target_allocation_id = h.id
                 where 1 = 1
                   AND a.ACTION_TYPE_ID = 'HWMAN01'
                   AND a.JOB_DATE between
                       to_date('2014-07-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
                       to_date('2014-08-02 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                   AND a.JOB_TYPE_ID = 'HWMSN02') a)
WHERE row_num BETWEEN 1 AND 30
ORDER BY createDate desc