There are several things that you could attempt to do. Each method would depend on your particular circumstances, such as size of record, number of indexes, fragmentation in the database, etc.
1. Try dropping the indexes from the database prior to deleting the group of records. Then recreate the indexes after the records are deleted. This may be faster if your intent is to keep fewer records than you are deleting. 2. Can the schema be redone? Perhaps you could organize the tables so that each table holds a specific range of dates with a controlling table pointing the application to the correct table based on the date of data to be retrieved. This probably isn't a likely solution. But, I thought I'd mention this as I've had to do this with some applications that I've developed. 3. Probably the best solution is to create another copy of the table without indexes. Use a select into to move data that you want to keep from the main table to the copy table. Then rebuild your indexes, drop the original table and rename the copy table to the original tables name. Be very careful with this approach as some foreign keys in other tables may be affected by doing this. 4. A trunc operation will be much faster than deleting records. But, trunc will remove ALL records. Trunc is useful in combination with copying records (solution 3) above. Just yesterday I had to drop 36 million records from a table. Sort of makes 500K seem small. In my haste I didn't stop to build a copy table to hold the 56 records that I actually wanted to keep. Instead I used a delete statement and it took over 90 minutes. Doh! I'd also recommend that you experiment with any of these solutions after backing up your database. And, it may be best to work out the solution on a development/test database if that's possible. Good luck! Ron -----Original Message----- From: Rozengurtel, Daniel [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 18, 2001 8:49 AM To: '[EMAIL PROTECTED]' Subject: Delete Hello all I am trying to delete from a large table (about 500,000 recs). This procedure has to take place twice a month, on a specific day. So for that purpose I have this sql: $sql = "DELETE FROM ft_t_ispc WHERE LAST_CHG_TMS < TO_DATE('$final_date','MM/DD/YYYY')"; my $rc = $dbh->do("$sql"); However, it seems to run forever. Am I doing something wron? Should I check my indexes on thst tsble? Is there anything like deleteing data in chunks? Since what I have is per-transaction commit (AutoCommit=>1) Thanx all _____________________________________________________________________ IMPORTANT NOTICES: This message is intended only for the addressee. Please notify the sender by e-mail if you are not the intended recipient. If you are not the intended recipient, you may not copy, disclose, or distribute this message or its contents to any other person and any such actions may be unlawful. Banc of America Securities LLC("BAS") does not accept time sensitive, action-oriented messages or transaction orders, including orders to purchase or sell securities, via e-mail. BAS reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the BAS e-mail system.
