在使用左，右，或者内连接的时候，在需要使用排序的时候，不妨先以一个表为标准，先进行排序，这样可以提供语句的性能
比如:
优化前:
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