Required to get latest top 5 record
Dears,
I am facing a challenge that i have one table in my database whose size is 70 GB and having record 13 Billion and Customer want to keep only latest five record against each . i used below pl/sql code but its taking too much time even can say taking days.
DECLARE
CNT NUMBER(10) := 1;
BEGIN
FOR i IN (SELECT DISTINCT a FROM x) LOOP
INSERT /*+ append */ INTO TEMP (SELECT * FROM ( SELECT * FROM x WHERE x.a=i.a ORDER BY b desc) a where rownum <=5);
IF mod(CNT,50000) = 0 THEN
COMMIT;
END IF;
end loop;
commit;
END;
/
We have only 6 hrs of Maintenance window to perform this activity. kindly suggest which is the best and fastest way to implement with in time frame ( 6 Hrs ).