PL/SQL (MOSC)

MOSC Banner

improve execution time

edited May 31, 2016 3:02PM in PL/SQL (MOSC) 27 commentsAnswered

How to improve execution time for the below procedure?

CREATE OR REPLACE PROCEDURE del_data(

    timestmp IN number,

    interval IN number,

    cnt      IN number

)

AS

CURSOR c1 is

SELECT /*+ nologging */

col1,

col2,

col3

.

.

.

.

.

col20

rowid

FROM table_name

WHERE TIME_STAMP = timestmp AND INTERVAL = interval;

V_COUNT NUMBER :=0;

BEGIN

   for rec in c1 LOOP

    DELETE FROM table_nameWHERE rowid= rec.rowid;

      V_COUNT := V_COUNT + 1;

      IF V_COUNT=cnt THEN

          COMMIT;

      V_COUNT := 0;

      END IF;

  END LOOP;

  COMMIT;

end;

/

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