develooper Front page | perl.beginners | Postings from August 2009

Re: delete 20 000 records in oracle from perl

Thread Previous | Thread Next
Chas. Owens
August 17, 2009 06:30
Re: delete 20 000 records in oracle from perl
Message ID:
On Mon, Aug 17, 2009 at 02:00, luke devon<> wrote:
> Hi
> I wanted to delete some unwanted data in one of my oracle
> database.20 000 of records are there. This is actually on production .
> manually to delete  a record , it takes 2 mins of time.
> So now i decided to write a perl script to do this function
> by learning on the web.  Can somebody give me a exact guide to delete
> that amount of data , without giving any impact for performance of the
> database and i would like to ask what is the better way to do such a
> execution ?
> Do we have to implement more threads ?

Multiple threads or processes will just magnify your performance
problem.  The most performant solution to truncate the table, but you
can only do that if there is not data in the table you want to save.
From your SQL it certainly looks like you are deleting everything from
TB1.  If you are certain that everything in the table must go, try

#WARNING: truncate cannot be rolled back.
$dbh->do("truncate table TB1");

If you need to delete some rows from a table, but don't want to eat a
lot of CPU you can always use a SELECT statement to get the primary
keys for the rows you want to delete and then slowly delete a row at a

my $sth = $dbh->prepare("delete from TB1 where TB1_ID = ?");
for my $key (@keys) {
    sleep 1; #don't hog the CPU

Chas. Owens
The most important skill a programmer can have is the ability to read.

Thread Previous | Thread Next Perl Programming lists via nntp and http.
Comments to Ask Bjørn Hansen at | Group listing | About