Is hope is to be able to achieve the purpose of this query paging query, but according to the plan, if the cap on the query number is 800, subsequent to participate in the calculation of base rather than 49, 799 will inquire the upper limit of the number rises to 190000, the follow-up to participate in the calculation of base is 189950, I think such a serious influence on the performance of the SQL execution

The SQL code is as follows:
With material_a as
(select *
The from (select row_. *, rownum rownum_
The from t_sap_material row_
Where rownum & lt; 800
The order by row_. Material_info_id)
Where rownum_ & gt; 750),
Factory_a as
(select materail_id,
Listagg (factory, ', ') within GROUP (order by factory) factory_list
The from (select distinct ma. Materail_id, ma. Factory
The 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
The from (select distinct material_info_id, organization_id from (select m.m aterial_info_id, m.org anization_id
The from t_sap_materal_sale_map m where m.m aterial_info_id (in the select m_a. Material_info_id from material_a m_a)
))
Group by material_info_id)
The select k. *, fa factory_list, oa.org _list
The from material_a k
Left the join factory_a fa
On fa. Materail_id=k.m aterial_info_id
Left the join org_a oa
On oa. Material_info_id=k.m aterial_info_id




This page is the upper limit of number rises to 190000, the follow-up to participate in the calculation is the base of 189950
as follows
With material_a as
(select *
The from (select row_. *, rownum rownum_
The from t_sap_material row_
Where rownum & lt; 190000
The order by row_. Material_info_id)
Where rownum_ & gt; 189950),
Factory_a as
(select materail_id,
Listagg (factory, ', ') within GROUP (order by factory) factory_list
The from (select distinct ma. Materail_id, ma. Factory
The 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
The from (select distinct material_info_id, organization_id from (select m.m aterial_info_id, m.org anization_id
The from t_sap_materal_sale_map m where m.m aterial_info_id (in the select m_a. Material_info_id from material_a m_a)
))
Group by material_info_id)
The select k. *, fa factory_list, oa.org _list
The from material_a k
Left the join factory_a fa
On fa. Materail_id=k.m aterial_info_id
Left the join org_a oa
On oa. Material_info_id=k.m aterial_info_id





I think such a serious influence on the performance of the SQL execution, but I don't know if this is the SQL optimization, consult everybody how can better optimize the SQL?

CodePudding user response:

Saw, if indeed it was, you take the main is your hash join right outer, indeed as expected the first record you less words can reduce a lot of data matching,
But you can also optimize, indexed.

CodePudding user response:

T_sap_material_factory_map t_sap_materal_sale_map t_sap_material child table SQL is mean to t_sap_material_factory_map t_sap_materal_sale_map table a column in the merged, comma separated good summary query results

CodePudding user response:

Which is the appropriate index to add?

CodePudding user response:

The driver table selection problem, can compare the first perform fast execution plan, plus a hint/select/* + use_hash (k, fa) leading use_hash (k) (k, oa) */k. *, fa. Factory_list, oa.org _list, you can try the effect