oracle sql调优 加order by 速度很慢

[yong821821]

sql语句：

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
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')
order by s.NUM_SUBSCRIBE_ID desc ) row_
where rownum <= 5000;

问题描述：
加入order by s.NUM_SUBSCRIBE_ID desc 速度很慢，大概30S，不加大概10秒


下面是执行计划：


加上order by后：

[Tiger_Zhao]

    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

[yong821821]

按照这种写法，查询速度还是很慢！

[bw555]

不加order的话，只要读取前5000条数据就可以 加上order的话，需要把所有数据都取出来，然后排序，然后取前5000条 这个操作量差很多，时间差这么多就不足为怪了 数据量较大的时候，建议使用分析函数row_number()截取前5000条

[TTOS3302041]

1.建议按NUM_SUBSCRIBE_ID、NUM_CLIENT_ID、DAT_MARKETING_TIME 依次添加索引调试看性能如何 2.数据量也不小了吧，可以考虑下基于NUM_PROVINCE_ID做list分区或DAT_MARKETING_TIME的做range分区 方法有效的话记得给分哦

[一颗向往数据的心丶]

引用 1 楼 bw555 的回复:

不加order的话，只要读取前5000条数据就可以 加上order的话，需要把所有数据都取出来，然后排序，然后取前5000条 这个操作量差很多，时间差这么多就不足为怪了 数据量较大的时候，建议使用分析函数row_number()截取前5000条

++

[CR7RM]

楼主贴的SQL与执行计划似乎不对应啊！

[liu3617]

可以先把数据都拿到以后才order by ，把order by 套在最外面应该就会快了

[没什么是无所不能的]

建个索引试试 create index idx_CRM_CLIENTS_01 on CRM_CLIENTS ( NUM_CLIENT_ID )

[lyangsoft]

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;