Re: Very long query

From: Thanassis M Mylonas <thanassis.mylonas_at_eletson.com>
Date: Thu, 10 Dec 1998 12:10:45 GMT
Message-ID: <366fb957.10220275@news.ntua.gr>

Zach,

To avoid this problem, the procedure described below commits after every N number of rows specified by the user so that rollback segments are not taxed.

--This procedure deletes rows from the table specified in tname for the condition in where_clause. It commits after every n records specified by the parameter commit_size .
-- eg : execute delete_table('emp','where job=''clerk''',2000);

• if where_clause and commit_size is not specified the procedure deletes all the records for the table specified commiting after every 1000 rows.

CREATE OR REPLACE PROCEDURE delete_table

                                         (tname in varchar2,
                                          where_clause in varchar2
default null,
                                          commit_size in number

default 1000) is

  sel_id      INTEGER;
  del_id      INTEGER;
  exec_sel    INTEGER;
  exec_del    INTEGER;

  cur_stmt VARCHAR2(2000);
  del_rowid ROWID;
BEGIN
  --Prepare cursor to select rowids of the records to be deleted.   cur_stmt := 'SELECT rowid FROM '||tname||' '||where_clause;   sel_id := DBMS_SQL.OPEN_CURSOR;

  DBMS_SQL.PARSE(sel_id,cur_stmt,dbms_sql.v7);
  DBMS_SQL.DEFINE_COLUMN_ROWID(sel_id,1,del_rowid);
  exec_sel := DBMS_SQL.EXECUTE(sel_id);

  --Prepare cursor to delete records
  del_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(del_id,'delete '||tname||

               ' where rowid = :del_rowid',dbms_sql.v7);  LOOP
   IF DBMS_SQL.FETCH_ROWS(sel_id) >0 THEN

      --Get the rowid to be deleted 
      DBMS_SQL.COLUMN_VALUE(sel_id,1,del_rowid);
      DBMS_SQL.BIND_VARIABLE(del_id,'del_rowid',del_rowid);
      --Execute the delete for the rowid got
      exec_del := DBMS_SQL.EXECUTE(del_id);
      -- Commit for every n records where n is specified in
commit_size
      if mod(dbms_sql.last_row_count,commit_size) = 0 then
      COMMIT;
      end if;

   ELSE exit;
   END IF;
 END LOOP;

• Commit again to catch the last set of records and close the cursors COMMIT; DBMS_SQL.CLOSE_CURSOR(sel_id); DBMS_SQL.CLOSE_CURSOR(del_id); END; /

Thanassis Mylonas

On Thu, 10 Dec 1998 07:55:37 +0100, Rolf Hauck <Rolf.Hauck_at_datenrevision.de> wrote:

>Did you check for locks on your table?
>Why don't you commit inside the loop?
>
>Rolf
>
>Zachary Agatstein schrieb:
>> 
>...
>> Why if I can successfully delete 1000 records, I cannot consecutively delete
>> all of them in 1000-record chunks?
>> 
>> Thanks
>> 
>> Zach Agatstein

Received on Thu Dec 10 1998 - 06:10:45 CST