SELECT  count(*) 
FROM 
(SELECT DISTINCT DMPNN.ID AS NEED_ID, 
'V2' VDMPSX, DMPNN.DMP_NUM AS DMPNN_NUM, DTT.TASK_ID AS TASK_ID, 
/*任務ID*/ 
(SELECT NVL(TO_CHAR(workload),'') 
FROM DMP_ALLOCATION_NEED_RESULT dnr 
WHERE dnr.anr_id =DTT.Anr_Id 
) GUIBANWORKLOAD, 
/*歸版 工時*/ 
DTT.TASK_NUM AS TASK_NUM, DTT.TASK_TYPE AS TASK_TYPE, 
/*任務類型*/ 
DMPNN.NAME AS DMPNN_NAME, 
/*需求名稱*/ 
DMPNN.APPLICANT_DEP AS APPLICANT_DEP, 
/*申請部門*/ 
DMPNN.APPLICANT_DEP_NAME AS APPLICANT_DEP_NAME, 
DMPNN.APPLICANT AS APPLICANT, 
/*申請人*/ 
DMPNN.CREATE_TIME AS CREATE_TIME, 
/*創建時間*/ 
DMPNN.FINISH_DATE AS FINISH_DATE, 
/*要求完成時間 */ 
DTT.TASK_YJSXSJDATE AS PLAN_TIME, 
/*預計上線時間 */ 
'two' IS_ONE_QI_OR_TWO_QI, 
/*判斷一期還是二期需求 */ 
DMPNN.RESPONSIBLE_USERID AS RESPONSIBLE_USER_ID, 
/*需求負責人 */ 
DTT.NEED_PERSON AS RESPONSIBLE_USER_NAME, 
/*需求負責人名稱*/ 
DMPNN.DEVELOP_USERID AS DEVELOP_USER_ID, 
/*需求開發人 */ 
DTT.ESTIMATE_FINISH_TIME AS STR_E_F_T, 
/*預計完成時間 */ 
DMPNN.ESTIMATE_ONLINE_TIME AS ESTIMATE_ONLINE_TIME, 
/*預計上線時間*/ 
DMPNN.NEED_STATE AS NEED_STATE, 
/*需求狀態*/ 
XQ.ZT AS NEED_STATE_NAME, 
/*需求狀態*/ 
DMPNN.WORK_LOAD_ASSESS AS WORKLOAD_ASSESS, 
DTT.WORKLOAD AS WORKLOAD, 
/*工作量*/ 
NULL AS VERSION_ID, 
/*版本ID */ 
NULL AS VERSION_TYPE, 
/*版本類型*/ 
'' AS versionNum, 
/*版本號*/ 
'' AS versionTypeName, 
/*版本名稱*/ 
DTT.REALITY_WORKLOAD AS REALITY_WORKLOAD, 
/*實際工作量*/ 
'' AS VERSION_NUM, 
/*版本編號*/ 
DMPNN.PROCESSINSTANCEID AS PROCESSINSTANCEID, 
/*流程實例id*/ 
DMPNN.BUSINESS_SYS AS BUSINESS_SYS, 
/*歸屬系統*/ 
DMPNN.NEED_CLASSES AS need_class, 
/*需求單類別*/ 
DMPNN.IS_ELECTRON_CHANNEL AS IS_ELECTRON_CHANNEL, 
DMPNN.NEED_TYPE_SUB AS NEED_TYPE_SUB, 
DMPNN.NEED_TYPE AS NEED_TYPE, 
DMPNN.TEMPLATE_TYPE AS TEMPLATE_TYPE, 
DMPNN.NEED_STATE AS NEED_STATEN, 
DMPNN.APPLICANT_STATION AS APPLICANT_STATION, 
DMPNN.RESPONSIBLE_USERID AS RESPONSIBLE_USERID, 
xq.kaifamanagerid KAIFAMANAGERID 
/* 開發主管ID*/ 
, 
xq.kaifamanagername KAIFAMANAGERNAME 
/* 開發主管*/ 
FROM 
(SELECT * 
FROM DMP_N_NEED 
WHERE EXISTS 
(SELECT 1 
FROM ACT_HI_TASKINST hi 
WHERE hi.ASSIGNEE_ = '8a03d61a35bdc6670135be019f73020d' 
AND hi.PROC_INST_ID_ = Processinstanceid 
) 
) DMPNN 
LEFT JOIN 
(SELECT A.TASK_ID, 
A.NEED_ID, 
B.TASK_YJSXSJDATE, 
A.TASK_NUM, 
A.TASK_TYPE, 
A.NEED_PERSON, 
A.ANR_ID, 
B.ESTIMATE_FINISH_TIME, 
B.WORKLOAD, 
B.REALITY_WORKLOAD 
FROM DMP_T_TASK A 
LEFT JOIN 
(SELECT MAX(TO_CHAR(TASK_YJSXSJDATE,'yyyy-MM-dd')) AS TASK_YJSXSJDATE, 
NEED_ID, 
MAX( 
CASE TASK_TYPE 
WHEN '1' 
THEN TO_CHAR(MAYBE_FINAL_DATE,'yyyy-MM-dd hh24:mi:ss') 
ELSE TO_CHAR(TASK_VENDORSFINISHTIME,'yyyy-MM-dd hh24:mi:ss') 
END ) AS ESTIMATE_FINISH_TIME, 
SUM(NVL(DEVELOP_FORECAST_WORKLOAD, 0)) + SUM(NVL(TEST_WORKLOAD, 0)) AS WORKLOAD, 
CASE 
WHEN MAX(TASK_TYPE) = '2' 
THEN TO_CHAR(SUM(NEED_TOTEL_WORKLOAD)) 
ELSE TO_CHAR(SUM(FACT_WORKLOAD)) 
END AS REALITY_WORKLOAD 
FROM DMP_T_TASK 
GROUP BY NEED_ID 
) B 
ON A.NEED_ID = B.NEED_ID 
) DTT ON DTT.NEED_ID = DMPNN.ID 
LEFT JOIN XUQIUMINGXB XQ 
ON ((xq.task_num IS NOT NULL 
AND dtt.task_num = xq.task_num) 
OR (xq.task_num IS NULL 
AND DMPNN.dmp_num = xq.oabh)) 
WHERE DMPNN.ID IS NOT NULL 
AND DMPNN.PROCESSINSTANCEID IS NOT NULL 
AND DMPNN.DMP_NUM IS NOT NULL 
AND (xq.dmp_type = '2' 
OR xq.dmp_type IS NULL ) 
) DMP 
WHERE 1 = 1 
AND (DMP.need_class IS NULL 
OR (DMP.need_class IS NOT NULL 
AND DMP.NEED_CLASS <> 'DmpNChannelNeedPO') ) 
AND DMPNN_NUM LIKE '[2015]%' 
ORDER BY DMP.CREATE_TIME DESC 

為了分頁，也不知道啥原因，去掉count正常執行查詢很快，加上count統計，第一遍快，往后越來月末。用count(主鍵)也實驗了，不行，最后加的

/*+rule*/

SELECT /*+rule*/ count(*) 
FROM 
(SELECT DISTINCT DMPNN.ID AS NEED_ID, 
'V2' VDMPSX, DMPNN.DMP_NUM AS DMPNN_NUM, DTT.TASK_ID AS TASK_ID, (SELECT NVL(TO_CHAR(workload),'') 
FROM DMP_ALLOCATION_NEED_RESULT dnr 
WHERE dnr.anr_id =DTT.Anr_Id 
) GUIBANWORKLOAD, DTT.TASK_NUM AS TASK_NUM, DTT.TASK_TYPE AS TASK_TYPE, DMPNN.NAME AS DMPNN_NAME, DMPNN.APPLICANT_DEP AS APPLICANT_DEP, DMPNN.APPLICANT_DEP_NAME AS APPLICANT_DEP_NAME, 
DMPNN.APPLICANT AS APPLICANT, DMPNN.CREATE_TIME AS CREATE_TIME, DMPNN.FINISH_DATE AS FINISH_DATE, DTT.TASK_YJSXSJDATE AS PLAN_TIME, 'two' IS_ONE_QI_OR_TWO_QI, DMPNN.RESPONSIBLE_USERID AS RESPONSIBLE_USER_ID, 
/*需求負責人 */ 
DTT.NEED_PERSON AS RESPONSIBLE_USER_NAME, 
/*需求負責人名稱*/ 
DMPNN.DEVELOP_USERID AS DEVELOP_USER_ID, 
/*需求開發人 */ 
DTT.ESTIMATE_FINISH_TIME AS STR_E_F_T, 
/*預計完成時間 */ 
DMPNN.ESTIMATE_ONLINE_TIME AS ESTIMATE_ONLINE_TIME, 
/*預計上線時間*/ 
DMPNN.NEED_STATE AS NEED_STATE, 
/*需求狀態*/ 
XQ.ZT AS NEED_STATE_NAME, 
/*需求狀態*/ 
DMPNN.WORK_LOAD_ASSESS AS WORKLOAD_ASSESS, 
DTT.WORKLOAD AS WORKLOAD, 
/*工作量*/ 
NULL AS VERSION_ID, 
/*版本ID */ 
NULL AS VERSION_TYPE, 
/*版本類型*/ 
'' AS versionNum, 
/*版本號*/ 
'' AS versionTypeName, 
/*版本名稱*/ 
DTT.REALITY_WORKLOAD AS REALITY_WORKLOAD, 
/*實際工作量*/ 
'' AS VERSION_NUM, 
/*版本編號*/ 
DMPNN.PROCESSINSTANCEID AS PROCESSINSTANCEID, 
/*流程實例id*/ 
DMPNN.BUSINESS_SYS AS BUSINESS_SYS, 
/*歸屬系統*/ 
DMPNN.NEED_CLASSES AS need_class, 
/*需求單類別*/ 
DMPNN.IS_ELECTRON_CHANNEL AS IS_ELECTRON_CHANNEL, 
DMPNN.NEED_TYPE_SUB AS NEED_TYPE_SUB, 
DMPNN.NEED_TYPE AS NEED_TYPE, 
DMPNN.TEMPLATE_TYPE AS TEMPLATE_TYPE, 
DMPNN.NEED_STATE AS NEED_STATEN, 
DMPNN.APPLICANT_STATION AS APPLICANT_STATION, 
DMPNN.RESPONSIBLE_USERID AS RESPONSIBLE_USERID, 
xq.kaifamanagerid KAIFAMANAGERID 
/* 開發主管ID*/ 
, 
xq.kaifamanagername KAIFAMANAGERNAME 
/* 開發主管*/ 
FROM 
(SELECT * 
FROM DMP_N_NEED 
WHERE EXISTS 
(SELECT 1 
FROM ACT_HI_TASKINST hi 
WHERE hi.ASSIGNEE_ = '8a03d61a35bdc6670135be019f73020d' 
AND hi.PROC_INST_ID_ = Processinstanceid 
) 
) DMPNN 
LEFT JOIN 
(SELECT A.TASK_ID, 
A.NEED_ID, 
B.TASK_YJSXSJDATE, 
A.TASK_NUM, 
A.TASK_TYPE, 
A.NEED_PERSON, 
A.ANR_ID, 
B.ESTIMATE_FINISH_TIME, 
B.WORKLOAD, 
B.REALITY_WORKLOAD 
FROM DMP_T_TASK A 
LEFT JOIN 
(SELECT MAX(TO_CHAR(TASK_YJSXSJDATE,'yyyy-MM-dd')) AS TASK_YJSXSJDATE, 
NEED_ID, 
MAX( 
CASE TASK_TYPE 
WHEN '1' 
THEN TO_CHAR(MAYBE_FINAL_DATE,'yyyy-MM-dd hh24:mi:ss') 
ELSE TO_CHAR(TASK_VENDORSFINISHTIME,'yyyy-MM-dd hh24:mi:ss') 
END ) AS ESTIMATE_FINISH_TIME, 
SUM(NVL(DEVELOP_FORECAST_WORKLOAD, 0)) + SUM(NVL(TEST_WORKLOAD, 0)) AS WORKLOAD, 
CASE 
WHEN MAX(TASK_TYPE) = '2' 
THEN TO_CHAR(SUM(NEED_TOTEL_WORKLOAD)) 
ELSE TO_CHAR(SUM(FACT_WORKLOAD)) 
END AS REALITY_WORKLOAD 
FROM DMP_T_TASK 
GROUP BY NEED_ID 
) B 
ON A.NEED_ID = B.NEED_ID 
) DTT ON DTT.NEED_ID = DMPNN.ID 
LEFT JOIN XUQIUMINGXB XQ 
ON ((xq.task_num IS NOT NULL 
AND dtt.task_num = xq.task_num) 
OR (xq.task_num IS NULL 
AND DMPNN.dmp_num = xq.oabh)) 
WHERE DMPNN.ID IS NOT NULL 
AND DMPNN.PROCESSINSTANCEID IS NOT NULL 
AND DMPNN.DMP_NUM IS NOT NULL 
AND (xq.dmp_type = '2' 
OR xq.dmp_type IS NULL ) 
) DMP 
WHERE 1 = 1 
AND (DMP.need_class IS NULL 
OR (DMP.need_class IS NOT NULL 
AND DMP.NEED_CLASS <> 'DmpNChannelNeedPO') ) 
AND DMPNN_NUM LIKE '[2015]%' 
ORDER BY DMP.CREATE_TIME DESC