Mallik wrote: > Hi Friends, > > I need a help from you in implementation of purging functionality. > I am given a hash of table(s), each having millions of records. i > need to delete those records keeping latest 5 days records in the > tables. > > I know the delete query like: delete from $tableName where adddtm < > today ? $numDays ; where $numDays is the variable having number of > days latest data to be retained. if i give the direct query like > above it takes a lot of time to delete records from that set of > tables. > > Do you have any idea to implement this purging that can be done in > time for any number of tables containing millions of records in a > loop. i need to implement this in perl, with DBI-informix interface.
This isn't really a Perl question. The first thing I would ask is what percentage of the table's rows are you deleting? 1 percent? 50 percent? 90 percent? If the percentage is low, you can improve performance by creating an index on the date column and using a properly optimizable WHERE clause. If the percentage is very high, you might offload the rows to keep to a temp table, truncate the big table (which is usually fast), and reload the rows to keep. There isn't going to be a Perl approach that is faster than a simple "DELETE FROM foo WHERE date < ?" to delete a bunch of rows from the table. This is a database question, not a Perl question. Have you pursued this on Informix lists? -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] <http://learn.perl.org/> <http://learn.perl.org/first-response>
