平台环境：OEL+K/3 Cloud 6.2+ORACLE 11g

最近发现一客户，对物料查询超级慢，平均执行时间为80s，发现物料表T_BD_MATERIAL 有10多个G,执行超级的慢，碰到业务高峰期，经常出现卡死得情况

SQL语句如下：

SELECT *

FROM (SELECT t0.FUSEORGID FUSEORGID_ID,

t0.FNUMBER FNUMBER,

t0_L.FNAME FNAME,

t0_L.FSPECIFICATION FSPECIFICATION,

t0.FDOCUMENTSTATUS FDOCUMENTSTATUS,

t0.FFORBIDSTATUS FFORBIDSTATUS,

t0.PAEZ_OLDNAME F_PAEZ_OLDNAME,

t0.FUSEORGID FUSEORGID,

t0.FMATERIALGROUP FMATERIALGROUP,

t0.FMATERIALID FMATERIALID,

t0.FMASTERID FMASTERID,

ROW_NUMBER() OVER(ORDER BY t0.FNUMBER ASC) FIDENTITYID

FROM T_BD_MATERIAL T0

LEFT OUTER JOIN t_BD_MaterialBase T1

ON t0.FMATERIALID = t1.FMATERIALID

LEFT OUTER JOIN T_BD_MATERIAL_L T0_L

ON (t0.FMATERIALID = t0_L.FMATERIALID AND t0_L.FLocaleId = 1012)

WHERE ((((1 = 1 AND t1.FISSALE = '1') AND

(t0.FDOCUMENTSTATUS = 'C' AND t0.FFORBIDSTATUS = 'A')) AND

t0.FFORBIDSTATUS = 'A') AND t0.FUSEORGID = 100101)) TLIST

WHERE ((fidentityid >= 1) AND (fidentityid <= 10000))

分析原因如下：

物料表由于过大，且SQL语句中包含ROW_NUMBER 的聚合函数，导致该SQL执行计划只能走full table scan，导致性能低下，消耗CPU和IO资源

通过对SQL语句的改写如下：

select a.*, rownum as rn

from (SELECT t0.FUSEORGID FUSEORGID_ID,

产品问题CLOUD物料列表查询超级慢，SQL执行异常

产品问题 CLOUD物料列表查询超级慢，SQL执行异常