优化Oracle中的以下存储过程

Question

我有下面的存储过程，这基本上是一个存储过程，而做一个表清理基础上，意见刷新日期现在我担心的是，我的存储过程可以正常使用，但作为 一个开发人员我更专注于optminzing和不使用不正确synta，

所以请求你们乡亲，请指教和GUID，如果在下面存储PROCRE我使用任何不正确的语法或我如何优化他下面存储prcedure，请指教因为我更倾向于优化和效率，请告知。

create or replace procedure TABLE_CLEAN_UPABLE_CLEAN_UP 
is 
v_last_cleanup_time timestamp(6); 
v_last_mv_refresh_time timestamp(6); 
v_last_mv0_refresh_time timestamp(6); 
outcome VARCHAR2(100); 
i NUMBER := 0; 
begin 


Select LAST_START_DATE+LAST_RUN_DURATION into v_last_cleanup_time from USER_SCHEDULER_JOBS 
where JOB_NAME='DELTA_TABLE_CLEAN_UP_JOB';  

SELECT count(*) into i FROM all_mviews WHERE owner = 'XP_TM_TO' AND 
mview_name in ('CASHFLOW_VIEW', 'CASHFLOW_VIEW_0') and LAST_REFRESH_TYPE='COMPLETE' 
and CAST(LAST_REFRESH_DATE AS TIMESTAMP) > v_last_cleanup_time; 


if i=2 then 

SELECT CAST(LAST_REFRESH_DATE AS TIMESTAMP) into v_last_mv_refresh_time FROM all_mviews WHERE owner = 'XP_TM_TO' AND mview_name = 'CASHFLOW_VIEW' and LAST_REFRESH_TYPE='COMPLETE'; 
SELECT CAST(LAST_REFRESH_DATE AS TIMESTAMP) into v_last_mv0_refresh_time FROM all_mviews WHERE owner = 'XP_TM_TO' AND mview_name = 'CASHFLOW_VIEW_0' and LAST_REFRESH_TYPE='COMPLETE'; 

delete CASHFLOW_DELTA where (TRADE_ID,SOURCE_SYSTEM,TRADE_VERSION) 
    NOT in 
     (select table1.TRADE_ID,table1.SOURCE_SYSTEM,table1.TRADE_VERSION from CASHFLOW_DELTA table1 
       INNER JOIN (select TRADE_ID,SOURCE_SYSTEM,max(TRADE_VERSION) as MAX_TRADE_VERSION 
       from CASHFLOW_DELTA GROUP BY TRADE_ID, SOURCE_SYSTEM) table2 
       on table1.TRADE_ID=table2.TRADE_ID 
       where table1.SOURCE_SYSTEM=table2.SOURCE_SYSTEM 
       and table1.TRADE_VERSION=table2.MAX_TRADE_VERSION 
       and table1.BROKERAGE_REALISED='YES') 
       and createddate<v_last_mv_refresh_time 
       and createddate<v_last_mv0_refresh_time; 

end if;   
end; 
/

Answer 1

CREATE OR REPLACE PROCEDURE TABLE_CLEAN_UPABLE_CLEAN_UP 
IS 
    v_last_mv_refresh_time TIMESTAMP (6); 
    i       NUMBER := 0; 
BEGIN 
    SELECT MIN(CAST (LAST_REFRESH_DATE AS TIMESTAMP)), COUNT (*) 
    INTO v_last_mv_refresh_time,i 
    FROM all_mviews 
    WHERE owner = 'XP_TM_TO' 
     AND mview_name IN ('CASHFLOW_VIEW', 'CASHFLOW_VIEW_0') 
     AND LAST_REFRESH_TYPE = 'COMPLETE' 
     AND CAST (LAST_REFRESH_DATE AS TIMESTAMP) > (SELECT LAST_START_DATE + LAST_RUN_DURATION FROM USER_SCHEDULER_JOBS WHERE JOB_NAME = 'DELTA_TABLE_CLEAN_UP_JOB'); 


    IF i = 2 THEN 
     DELETE CASHFLOW_DELTA 
     WHERE ((TRADE_ID, SOURCE_SYSTEM, TRADE_VERSION) NOT IN (SELECT TRADE_ID, SOURCE_SYSTEM, MAX (TRADE_VERSION) AS MAX_TRADE_VERSION 
                    FROM CASHFLOW_DELTA 
                   GROUP BY TRADE_ID, SOURCE_SYSTEM) 
       OR NVL(BROKERAGE_REALISED,'NO') <> 'YES') 
     AND createddate < v_last_mv_refresh_time; 
    END IF; 
END; 
/