Simplifying the deletion of data from multiple tables dynamically using pl/sql

I need to write an oracle pl/sql procedure to delete data from multiple tables. The selection criteria for data to be deleted is different for each table.

To keep it simple, assuming i have 3 tables, there will be one selection query for each table. i.e.

select id from table_a where product_type='A';
select name from table_b where category='student';
select phone_name from table_c where name='nokia';

A couple of rules

- I can only delete 1000 records at a time for each table. 
- I should issue a commit after every 1000 records
- Each row is processed first before deleting (in all tables)

The easiest solution is to have something like this

commit_limit:=1000;

counter:=0;
recordsDeleted:=0;
For i in (select rowid,id from table_a where product_type='A') loop

    Delete from table_a where rowid=i.rowid;
    recordsDeleted:=SQL%rowcount;
    counter:=counter++;

    if(counter>=commit_limit) then
        commit;
        counter:=0;
        >log to file that commit has been issued. 
    end if;
End loop;

counter:=0;
recordsDeleted:=0;
For i in (select rowid,name from table_b where category='student') loop

    Delete from table_b where rowid=i.rowid;
    recordsDeleted:=SQL%rowcount;
    counter:=counter++;

    if(counter>=commit_limit) then
        commit;
        counter:=0;
        >log to file that commit has been issued. 
    end if;
End loop;

counter:=0;
recordsDeleted:=0;
For i in (select rowid,phone_name from table_c where name='nokia') loop

    Delete from table_c where rowid=i.rowid;
    recordsDeleted:=SQL%rowcount;
    counter:=counter++;

    if(counter>=commit_limit) then
        commit;
        counter:=0;
        >log to file that commit has been issued and log value of 'recordsDeleted'. 
    end if;
End loop;

> log to file total records deleted. 

As you can see, there is a lot of repetition. I would like to use a procedure where i can tell it the table name and the query to use as the selection criteria.

I think if there was no selection criteria and there was no processing for each row before the deletion it would be a simple case of building a dynamic sql statement and use execute immediate to perform the deletion. I want to have a procedure/function like this one below and call it for each table. (i.e. 3 times for the above examples)

function delete_by_colid (table_name in varchar(35), column_name in varchar(150), select_criteria in varchar(200)) return number
Is
begin
    counter:=0;
    recordsDeleted:=0;

    For i in (<<<select_criteria>>>) loop

        Delete from <<<table_name>>> where <<<column_name>>>=i.rowid;
        recordsDeleted:=SQL%rowcount;
        counter:=counter++;

        if(counter>=commit_limit) then
            commit;
            counter:=0;
            >log to file that commit has been issued and log value of 'recordsDeleted'. 
        end if;
    End loop;
    return recordsDeleted;
End;

The problem i have is am not sure of the following

  • How do i include the select_criteria in the for loop? Would an explicit cursor work instead? How would i define it dynamically?
  • How do i decide which sizes are suitable for table_name and especially the select_criteria when they are passed in to the function?
  • If i use execute immediate to execute a dynamically built sql statement. How do i get the value of sql%rowcount?

Thanks in advance

Edit

The process that i am working on will be part of a batch job that will run overnight. The batch job will be processing millions of rows which means it could take several hours to complete.

There are other more important deamon processes that will be running at the same time. This means the process that deletes the rows needs to be prevented from locking out tables/rows as a result of the deletion. To do this, we intend to send the process to sleep for a few seconds after every commit to allow the other processes to continue. This means i cant just delete using a single 'DELETE' statement.

Answers


Your pseudo-code would perform really badly. It is also likely to hurl ORA-1555 errors.

A better approach would be:

delete from table_a
where product_type='A'
/
delete from table_b 
where category='student' 
/
delete from table_c 
where name='nokia'
/

I fail to see why you think that needs simplifying.


"The batch job will be processing millions of rows which means it could take several hours to complete."

Yes but running on a modern server with well-tuned access paths it could take minutes. Benchmarking is all.

" This means the process that deletes the rows needs to be prevented from locking out tables/rows as a result of the deletion."

DML in Oracle doesn't lock tables, only affected rows. If you have other processes which need to use the rows in question you probably shouldn't be deleting them in the first place.

"To do this, we intend to send the process to sleep for a few seconds after every commit to allow the other processes to continue."

The best way to minimize the resources consumed by your deletion processing is to execute it in an efficient fashion. This means running a pure SQL statement rather than an elaborate PL/SQL loop. Set-based processing is considerably faster and lighter than RBAR processes. That is a fact.

But if you really want to have a job which nibbles away at the table rather than finishing the task in a single bite, do this:

delete from table_a
where product_type = 'A'
and rownum <= 1000;
log.write('records deleted from table_a = '||SQL%rowcount);
commit;

Have this run by a polling OS daemon or cron or something. Don't use DBMS_LOCK.SLEEP() to suspend it: that's juss tying up a CPU for no good reason.

My advice remains that you should benchmark this. You need to prove that the simplest approach will actually cause an unacceptable drain on system resources before you embark on an over-engineering exercise.


Oracle allows you to lock and wait for resources manually. You don't have to add 1000 rows logic, that's a huge performance issue.

You can simply explicitly lock a table and delay that operation by performing a "wait" on that table.

You can also do share row exclusive, allowing other users to view the whole table but prohibits them from locking or updating.

Oracle 10g docs http://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_9015.htm

Oracle 11g docs http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9015.htm


Need Your Help

SQL to generate fantasy league schedule

sql sql-server tsql

I'm trying to figure out the best way to tackle this and I'm having some trouble...What I want to do is write a SQL script to generate a head-to-head schedule for a league that could have a variabl...

Why doesn't mr.developer install my package dependencies?

python dependencies buildout egg setup.py

I'm using mr.developer with buildout for a project, but it doesn't install the eggs listed in my development packages' install_requires. What am I doing wrong?