oracle order by 2W条数据要2分钟 求帮优化

Question

select a1.fd_id,
                 a1.fd_motive,
                 a1.fd_proposer,
                 a1.fd_employee_id,
                 (select d.fd_name
                    from sys_org_dept d
                   where fd_parent_no is not null
                     and exists (select 1
                            from sys_org_dept x
                           where x.fd_no = d.fd_parent_no
                             and x.fd_parent_no is null)
                   start with d.fd_no = a2.fd_dept_no
                  connect by prior d.fd_parent_no = d.fd_no) as fd_sys,
                 a2.fd_dept as fd_dept,
                 a2.fd_dept_no,
                 to_char(a1.fd_create_time, 'yyyy-mm-dd hh24:mi:ss') as fd_create_time,
                 to_char(a1.fd_publish_time, 'yyyy-mm-dd hh24:mi:ss') as fd_publish_time,
                 a1.fd_status,
                 a1.fd_category,
                 (select to_char(wm_concat(t.fd_employee_id))
                    from web_share_people t
                   where t.fd_whole_innovate_id = a1.fd_id) as fd_employee_ids,
                 (select to_char(wm_concat(t.fd_name))
                    from web_share_people t
                   where t.fd_whole_innovate_id = a1.fd_id) as fd_names,
                 (select to_char(wm_concat(t.fd_weight || '%'))
                    from web_share_people t
                   where t.fd_whole_innovate_id = a1.fd_id) as fd_weights,
                 a1.fd_detailed_information,
                 a1.fd_improve,
                 a1.fd_effect,
                 a1.fd_rank,
                 a1.fd_score,
                 (select fd_name
                    from sys_org_dept s
                   where s.fd_no = a2.fd_dept_no) as fd_model,
                 a1.fd_auditor,
                 a1.FD_EFFICIENCY_ELEVATE1,
                 a1.FD_EFFICIENCY_ELEVATE2,
                 a1.FD_PROFIT_ADD,
                 a1.FD_PROBLEM_LESSEN,
                 a1.FD_AREA_SAVE
            from web_whole_innovate a1
            left join sys_org_person a2
              on a1.fd_employee_id = a2.fd_no
                 order by a1.fd_create_time去掉order by 就毫秒级 加上就2分钟  已添加索引 但是没什么效果  大神们棒棒忙

Answer 1

你建这么一条索引先试试：
create index idx_test on web_whole_innovate(fd_create_time,0);

Answer 2

我理解错了……这个SQL外面有分页吗？2万条记录是不是指的这个SQL最后返回的数据量就是2万？另外，最好弄个执行计划上来，好分析性能瓶颈