SQL Performance (MOSC)

MOSC Banner

Required to get latest top 5 record

edited Aug 28, 2015 10:05AM in SQL Performance (MOSC) 5 commentsAnswered

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 ).

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center