原开发代码:
SELECT
rp_ord_no
FROM
(
SELECT
ROW_NUMBER() over(PARTITION BY rp_ord_no ORDER BY post_ac_dt DESC) rn,
a.rp_ord_no ,
post_ac_dt,
ord_sts
FROM
t_ppd_ordr a )
WHERE
rn =2
AND rp_ord_no !=' '
AND ord_sts='S1'
and post_ac_dt='20200901';
此代码虽然post_ac_dt有索引但是没用到,导致了全表扫描。
原因是rn=2 后,没有必要用到索引。
改成:
select rp_ord_no from (SELECT ROW_NUMBER() over(PARTITION BY rp_ord_no ORDER BY post_ac_dt DESC) rn,rp_ord_no from
(SELECT a.rp_ord_no,
post_ac_dt,
ord_sts
FROM gsdpay.t_ppd_ordr a
WHERE
rp_ord_no != ' '
AND ord_sts = 'S1'
and post_ac_dt = '20200901')) where rn=2;
走的索引。
之后因为查询结果是3条数据,需要在一列中展示。
create or replace function FUNC_C4_ORDER return varchar2 is
v_Result varchar2(1000); --declare,长度定义。
V_ename VARCHAR2(100);
CURSOR emp_cursor IS
select rp_ord_no
from (SELECT ROW_NUMBER() over(PARTITION BY rp_ord_no ORDER BY post_ac_dt DESC) rn,
rp_ord_no
from (SELECT a.rp_ord_no, post_ac_dt, ord_sts
FROM gsdpay.t_ppd_ordr a
WHERE rp_ord_no != ' '
AND ord_sts = 'S1'
and post_ac_dt = '20200901'))
where rn = 2;
BEGIN
v_Result := ' ';
OPEN emp_cursor;
loop
FETCH emp_cursor
INTO v_ename;
exit when emp_cursor%notfound;
v_Result := v_ename || ',' || v_Result;
END LOOP;
CLOSE emp_cursor;
return v_Result;
end FUNC_C4_ORDER;
select FUNC_C4_ORDER from dual; 给予业务监控