row_number OVER 与 max keep 对比
来源:互联网 发布:淘宝开店方案 编辑:程序博客网 时间:2024/05/09 05:36
问题引出
今天领导告诉某个菜单效率太慢,调查半天,发现似乎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
- row_number OVER 与 max keep 对比
- ROWNUM 与ROW_NUMBER()OVER()
- Oracle rank() over 与 row_number() over
- 关于row_number() over与rank() over
- ROW_NUMBER OVER()
- ROW_NUMBER() OVER()
- ROW_NUMBER() OVER
- row_number() OVER
- row_number() over()
- ROW_NUMBER() over
- row_number() OVER(
- ROW_NUMBER() OVER()
- row_number() over(order by) 与count(1)
- row_number() over 与partition by 用法
- ROWNUM 与 ROW_NUMBER()OVER() 的区别
- ROW_NUMBER() OVER ,ROWNUM , OVER()
- max() over()
- 数据库分页的两种方法对比(row_number()over() 和 top的对比)
- HDU 4610(数论+枚举状态)
- Android多线程任务优化1:探讨AsyncTask的缺陷
- Android自定义Switch控件
- vector -- STL中容器的常用函数
- HorizontalScrollView实现画廊
- row_number OVER 与 max keep 对比
- 查询Mysql状态
- 如何解决oracle数据库中“记录被另一个用户锁住”的问题
- CentOS6平台编译安装MPEG4IP
- 使用truss、strace或ltrace诊断"疑难杂症" Segmentation fault排查
- jdbctype javatype 转换
- [Linux][2013-09-23] shell脚本加密
- C# 委托
- form button