Re: delete 20 000 records in oracle from perl
Hi Luke, luke devon wrote: 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 ? Perhaps I'm misunderstanding the problem, but if you would like to remove 20,000 records in an Oracle database, I don't believe that Perl will help you with that. It seems that you're already doing everything in a single SQL statement; having Perl do it won't give you much added benefit (i.e., no matter how you do it, you still have 20,000 records to delete). If these records don't overlap, then you could consider a multi-threaded approach...I guess. Another suggestion if this is a production server is to mark records as stale rather than deleting them (i.e., add a boolean flag as another column). And then, when the server is least busy, delete the marked records. Or, rather than a boolean flag, assign some number (say, from 0 to 9) and periodically delete a portion of the rows...sort of like how log files are handled [delete the oldest ones]. I presume your problem is that deleting 20,000 in one go is slowing down the Oracle server? Ray -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: delete 20 000 records in oracle from perl
Hi Ray Thanks for the reply . Yes , when we going to delete those records , CPU is going high.So then its effecting to the performance. as i beginner in perl , how can i implement multi-threaded env ? Can you help me further ? Thanks & Regards Luke From: Raymond Wan To: luke devon Cc: Perl Sent: Monday, August 17, 2009 14:12:27 Subject: Re: delete 20 000 records in oracle from perl Hi Luke, luke devon wrote: > 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 ? Perhaps I'm misunderstanding the problem, but if you would like to remove 20,000 records in an Oracle database, I don't believe that Perl will help you with that. It seems that you're already doing everything in a single SQL statement; having Perl do it won't give you much added benefit (i.e., no matter how you do it, you still have 20,000 records to delete). If these records don't overlap, then you could consider a multi-threaded approach...I guess. Another suggestion if this is a production server is to mark records as stale rather than deleting them (i.e., add a boolean flag as another column). And then, when the server is least busy, delete the marked records. Or, rather than a boolean flag, assign some number (say, from 0 to 9) and periodically delete a portion of the rows...sort of like how log files are handled [delete the oldest ones]. I presume your problem is that deleting 20,000 in one go is slowing down the Oracle server? Ray New Email addresses available on Yahoo! Get the Email name you've always wanted on the new @ymail and @rocketmail. Hurry before someone else does! http://mail.promotions.yahoo.com/newdomains/aa/
Re: delete 20 000 records in oracle from perl
Hi Luke, luke devon wrote: Thanks for the reply . Yes , when we going to delete those records , CPU is going high.So then its effecting to the performance. as i beginner in perl , how can i implement multi-threaded env ? Can you help me further ? Hm, well, I don't see any advantage of a multi-threaded version [perhaps someone else can see something I can't?]. Also, I have to admit that I don't know how to run Perl in a multi-threaded env. [Sorry!] But, before going down that path, how about creating multiple instances of Perl. I would think doing this is much easier and give you the same benefits. Have you considered this? One reason for my skepticism is that your problem is in the SQL statement. What Perl is doing is simply to act as a wrapper around it. A multi-threaded Perl script needs to know how to partition the tables so that they don't step on each other's toes; but it's the SQL that needs to worry about it. If there was a trivial way to separate the rows (i.e., "pkey = 1" for one set of rows and "pkey = 2" for another set), then you could run two instances of Perl to do this. i.e., one does: "delete rec1,rec2 " from tb1 where pkey = 1" and the other does the same but with "pkey = 2"... Ray PS: Even live servers like Amazon have downtime. Is it critical that you can't bring the server down once a day or week for 2 minutes? -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: delete 20 000 records in oracle from perl
Hi Since i have more binary data to be deleted in the requested query , i have to use a external script/program to satisfy the deletion. So thats why i wanted to know , how can we implement multi threading for the situation. Thanks Luke From: Raymond Wan To: luke devon Cc: Perl Sent: Monday, August 17, 2009 14:39:12 Subject: Re: delete 20 000 records in oracle from perl Hi Luke, luke devon wrote: > Thanks for the reply . Yes , when we going to delete those records , CPU is > going high.So then its effecting to the performance. as i beginner in perl , > how can i implement multi-threaded env ? Can you help me further ? Hm, well, I don't see any advantage of a multi-threaded version [perhaps someone else can see something I can't?]. Also, I have to admit that I don't know how to run Perl in a multi-threaded env. [Sorry!] But, before going down that path, how about creating multiple instances of Perl. I would think doing this is much easier and give you the same benefits. Have you considered this? One reason for my skepticism is that your problem is in the SQL statement. What Perl is doing is simply to act as a wrapper around it. A multi-threaded Perl script needs to know how to partition the tables so that they don't step on each other's toes; but it's the SQL that needs to worry about it. If there was a trivial way to separate the rows (i.e., "pkey = 1" for one set of rows and "pkey = 2" for another set), then you could run two instances of Perl to do this. i.e., one does: "delete rec1,rec2 " from tb1 where pkey = 1" and the other does the same but with "pkey = 2"... Ray PS: Even live servers like Amazon have downtime. Is it critical that you can't bring the server down once a day or week for 2 minutes? New Email names for you! Get the Email name you've always wanted on the new @ymail and @rocketmail. Hurry before someone else does! http://mail.promotions.yahoo.com/newdomains/aa/
Re: delete 20 000 records in oracle from perl
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 ?
snip
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
this:
#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
time:
my $sth = $dbh->prepare("delete from TB1 where TB1_ID = ?");
for my $key (@keys) {
$sth->execute($key);
sleep 1; #don't hog the CPU
}
--
Chas. Owens
wonkden.net
The most important skill a programmer can have is the ability to read.
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
