请帮忙指点sql优化

Question

这个查询的目的是希望是能够实现分页查询，但是根据执行计划 ,如果查询的上限数是800，后续参与计算的基数就是799 而不是49，这个将查询的上限数上调到190000 ，则后续参与计算的基数就是189950，我觉得这样严重影响sql的执行性能sql的代码如下 ：  
with material_a as
  (select *
     from (select row_.*, rownum rownum_
             from t_sap_material row_
            where rownum < 800 
            order by row_.material_info_id)
    where rownum_ > 750),
  factory_a as
  (select materail_id,
          listagg(factory, ',') within GROUP(order by factory) factory_list
     from (select distinct ma.materail_id, ma.factory
             from t_sap_material_factory_map ma inner join material_a m_a on m_a.material_info_id = ma.materail_id)
    group by materail_id),
  org_a as
  (select material_info_id,
          listagg(organization_id, ',') within GROUP(order by organization_id) org_list
     from (select distinct material_info_id,organization_id from (select  m.material_info_id,m.organization_id 
             from t_sap_materal_sale_map m where m.material_info_id  in (select m_a.material_info_id from material_a m_a)
            ))
    group by material_info_id)
 select k.*, fa.factory_list, oa.org_list
   from material_a k
   left join factory_a fa
     on fa.materail_id = k.material_info_id
   left join org_a oa
     on oa.material_info_id = k.material_info_id
这个将分页的上限数上调到190000 ，则后续参与计算的基数就是189950  如下with material_a as
  (select *
     from (select row_.*, rownum rownum_
             from t_sap_material row_
            where rownum < 190000 
            order by row_.material_info_id)
    where rownum_ > 189950),
  factory_a as
  (select materail_id,
          listagg(factory, ',') within GROUP(order by factory) factory_list
     from (select distinct ma.materail_id, ma.factory
             from t_sap_material_factory_map ma inner join material_a m_a on m_a.material_info_id = ma.materail_id)
    group by materail_id),
  org_a as
  (select material_info_id,
          listagg(organization_id, ',') within GROUP(order by organization_id) org_list
     from (select distinct material_info_id,organization_id from (select  m.material_info_id,m.organization_id 
             from t_sap_materal_sale_map m where m.material_info_id  in (select m_a.material_info_id from material_a m_a)
            ))
    group by material_info_id)
 select k.*, fa.factory_list, oa.org_list
   from material_a k
   left join factory_a fa
     on fa.materail_id = k.material_info_id
   left join org_a oa
     on oa.material_info_id = k.material_info_id我觉得这样严重影响sql的执行性能，但我不知道这是不是这个sql优化的重点，请教各位怎么能能更好的优化这个sql？

Answer 1

看了下，好像还真是，你的耗时主要是你的hash join right outer，果然你第一条记录少的话可以减少很多数据匹配。
不过你这个还可以优化的，加索引吧。

Answer 2

t_sap_material_factory_map  t_sap_materal_sale_map  是t_sap_material  的子表 sql的意思是要把t_sap_material_factory_map t_sap_materal_sale_map  表中的一列合并，用逗号分割汇总好查询结果里面   

Answer 3

驱动表选择问题，可以对比第一个执行快的执行计划，  加上hint/   select /*+ use_hash(k,fa) leading(k) use_hash(k,oa)*/  k.*, fa.factory_list, oa.org_list,你可以试下效果