select ... from (SELECT TOP 5000 * FROM CRM_SUBSCRIBES where NUM_PROVINCE_ID = 2 and DAT_MARKETING_TIME >= to_date('2006-01-01', 'yyyy-mm-dd') order by NUM_SUBSCRIBE_ID desc ) s left join CRM_CLIENTS c on c.NUM_CLIENT_ID = s.NUM_CLIENT_ID left join CRM_CLIENT_PERSONS cp on cp.NUM_CLIENT_ID = c.NUM_CLIENT_ID order by s.NUM_SUBSCRIBE_ID desc
建个索引试试 create index idx_CRM_CLIENTS_01 on CRM_CLIENTS ( NUM_CLIENT_ID )
SELECT * FROM (SELECT S.NUM_CLIENT_ID, C.VC2_LOGIN_ID, CASE C.NUM_CLIENT_TYPE WHEN 1 THEN CP.VC2_REAL_NAME WHEN 2 THEN (SELECT CC.VC2_NAME FROM CRM_CLIENT_CORPORATES CC WHERE NUM_CLIENT_ID = S.NUM_CLIENT_ID) END AS CLIENT_NAME, S.NUM_PROVINCE_ID, S.NUM_SERVICE_ID AS SERVICE_CATEGORY, S.NUM_SERVICE_ID, S.NUM_FEE, S.NUM_STATUS, S.DAT_SUBSCRIBE_TIME, S.DAT_FEE_START_DATE, S.DAT_FEE_END_DATE, S.DAT_UNSUBSCRIBE_TIME, ROW_NUMBER() OVER(ORDER BY S.NUM_SUBSCRIBE_ID DESC) ROW_NUM FROM CRM_SUBSCRIBES S LEFT JOIN CRM_CLIENTS C ON C.NUM_CLIENT_ID = S.NUM_CLIENT_ID LEFT JOIN CRM_CLIENT_PERSONS CP ON CP.NUM_CLIENT_ID = C.NUM_CLIENT_ID WHERE S.NUM_PROVINCE_ID = 2 AND S.DAT_MARKETING_TIME >= TO_DATE('2006-01-01', 'yyyy-mm-dd')) ROW_ WHERE ROW_NUM <= 5000;
加上order的话,需要把所有数据都取出来,然后排序,然后取前5000条
这个操作量差很多,时间差这么多就不足为怪了
数据量较大的时候,建议使用分析函数row_number()截取前5000条
from (SELECT TOP 5000 *
FROM CRM_SUBSCRIBES
where NUM_PROVINCE_ID = 2
and DAT_MARKETING_TIME >= to_date('2006-01-01', 'yyyy-mm-dd')
order by NUM_SUBSCRIBE_ID desc
) s
left join CRM_CLIENTS c
on c.NUM_CLIENT_ID = s.NUM_CLIENT_ID
left join CRM_CLIENT_PERSONS cp
on cp.NUM_CLIENT_ID = c.NUM_CLIENT_ID
order by s.NUM_SUBSCRIBE_ID desc
2.数据量也不小了吧,可以考虑下基于NUM_PROVINCE_ID做list分区或DAT_MARKETING_TIME的做range分区方法有效的话记得给分哦
create index idx_CRM_CLIENTS_01 on CRM_CLIENTS ( NUM_CLIENT_ID )
FROM (SELECT S.NUM_CLIENT_ID,
C.VC2_LOGIN_ID,
CASE C.NUM_CLIENT_TYPE
WHEN 1 THEN
CP.VC2_REAL_NAME
WHEN 2 THEN
(SELECT CC.VC2_NAME
FROM CRM_CLIENT_CORPORATES CC
WHERE NUM_CLIENT_ID = S.NUM_CLIENT_ID)
END AS CLIENT_NAME,
S.NUM_PROVINCE_ID,
S.NUM_SERVICE_ID AS SERVICE_CATEGORY,
S.NUM_SERVICE_ID,
S.NUM_FEE,
S.NUM_STATUS,
S.DAT_SUBSCRIBE_TIME,
S.DAT_FEE_START_DATE,
S.DAT_FEE_END_DATE,
S.DAT_UNSUBSCRIBE_TIME,
ROW_NUMBER() OVER(ORDER BY S.NUM_SUBSCRIBE_ID DESC) ROW_NUM
FROM CRM_SUBSCRIBES S
LEFT JOIN CRM_CLIENTS C
ON C.NUM_CLIENT_ID = S.NUM_CLIENT_ID
LEFT JOIN CRM_CLIENT_PERSONS CP
ON CP.NUM_CLIENT_ID = C.NUM_CLIENT_ID
WHERE S.NUM_PROVINCE_ID = 2
AND S.DAT_MARKETING_TIME >= TO_DATE('2006-01-01', 'yyyy-mm-dd')) ROW_
WHERE ROW_NUM <= 5000;