问题引出
今天领导告诉某个菜单效率太慢,调查半天,发现似乎row_number over的效率问题(分组求最大的全记录), 简单试了一下,发现max keep可以间接实现这个功能,
思路:用max keep求最大后, 在纪录表相连接。初步认为要快不少!等待修改效果。
-----row_number() OVER PARTITION
select tab.*
from (select t1.*,row_number() OVER(PARTITION BY FLOWINSTID ORDER BY FLOW_RECEIVEDATE desc) as ROW_FLG
from (SELECT ID,
FLOW_STATUS_MC,
FLOW_DATATITLE,
FLOW_CYZ,
IDBIZ,
STATUS,
TITLE,
TYPE,
EMERGENCY,
SECRETLEVEL,
BMQX,
SENDOUT,
KEYWORDS,
EDITOR,
FRONTFOR,
CBDWTEL,
REG_DATE,
SENDTO,
COPYTO,
ATTACHMENT,
WORD,
YEAR,
CODE,
PRINTER,
CNUM,
PUBLISHDATE,
CREATE_USER,
CREATE_DATE,
UPDATE_DATE,
UPDATE_USER,
FLOWINSTID,
GWLB,
SJLY,
FLEXSTRUCTCOL,
DATAID,
ACTIVITYINSTID,
POINTID,
FLOWDEFID,
FLOW_DEFTITLE,
FLOW_OPENDATE,
FLOW_USERID,
FLOW_STATUS,
FLOW_LOGINID,
FLOW_SEQUENCE,
FLOW_MAXSEQUENCE,
DATATYPE,
QIANFAREN,
FLOW_RECEIVEDATE,
FLOW_FINISHDATE,
QFRQ,
GWPBD_TITLE
FROM V_OA_GW_FLOW_PBD_EXT
WHERE 1 = 1
AND GWLB = 'GWFWLB'
AND ((FLOW_STATUS = '1' or FLOW_STATUS = '2' or
FLOW_STATUS = '4' or FLOW_STATUS = '3') and exists
(select 1
from v_agi_deptemp t
where (t.did = '6d593e5330164b30ac216955422c6c91' or
t.parentdid =
'6d593e5330164b30ac216955422c6c91')
and t.loginid = V_OA_GW_FLOW_PBD_EXT.FLOW_LOGINID))) t1) tab
where ROW_FLG = '1'
---------------max keep
select max(FLOWINSTID) keep(dense_rank first order by FLOW_RECEIVEDATE desc)
from (SELECT ID,
FLOW_STATUS_MC,
FLOW_DATATITLE,
FLOW_CYZ,
IDBIZ,
STATUS,
TITLE,
TYPE,
EMERGENCY,
SECRETLEVEL,
BMQX,
SENDOUT,
KEYWORDS,
EDITOR,
FRONTFOR,
CBDWTEL,
REG_DATE,
SENDTO,
COPYTO,
ATTACHMENT,
WORD,
YEAR,
CODE,
PRINTER,
CNUM,
PUBLISHDATE,
CREATE_USER,
CREATE_DATE,
UPDATE_DATE,
UPDATE_USER,
FLOWINSTID,
GWLB,
SJLY,
FLEXSTRUCTCOL,
DATAID,
ACTIVITYINSTID,
POINTID,
FLOWDEFID,
FLOW_DEFTITLE,
FLOW_OPENDATE,
FLOW_USERID,
FLOW_STATUS,
FLOW_LOGINID,
FLOW_SEQUENCE,
FLOW_MAXSEQUENCE,
DATATYPE,
QIANFAREN,
FLOW_RECEIVEDATE,
FLOW_FINISHDATE,
QFRQ,
GWPBD_TITLE
FROM V_OA_GW_FLOW_PBD_EXT
WHERE 1 = 1
AND GWLB = 'GWFWLB'
AND ((FLOW_STATUS = '1' or FLOW_STATUS = '2' or FLOW_STATUS = '4' or
FLOW_STATUS = '3') and exists
(select 1
from v_agi_deptemp t
where (t.did = '6d593e5330164b30ac216955422c6c91' or
t.parentdid = '6d593e5330164b30ac216955422c6c91')
and t.loginid = V_OA_GW_FLOW_PBD_EXT.FLOW_LOGINID))) a
group by FLOWINSTID