-> The application will delete the oldest (120000 - 50000) records because 120000 > 100000
currently it's doing this by retrieving the id's of the oldest (120000 - 50000) records:
and then delete them with a query like this one:
delete from mytable where id in (id1, id2, id3, ... , idx, idy, idz)
But when there are lots of records in the db (what is the case in my example), I get the following error:
java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000
Apart from this error, it doesn't seem to be the most optimal way of doing this.
I also need to be able to dump those old records in xml format before they are getting deleted from the database
Has someone some suggestions, examples, ... about how to do this at regular intervals in a more efficient way ?
Do you have a date field that tells when the record was inserted/last updated? If so, you can do "delete from table where date <= yourdate." Or, if you have to go by ids, you can do " delete from table where id <= (12000-5000) "
well, I have indeed a timestamp field which I could use for this, but the problem is that there are lots and lots of records inserted on one day and therefore I am not able to delete records based on timestamp only.
Maybe I also didn't use the most difficult criteria of the 3 for the example
So, there are 3 criteria to check:
- # events:
* low: 50000
* high: 100000
* low: 5 hours
* high: 20 hours
- size of db:
* low: 20 mb
* high: 100 mb
=> The high value is the value that indicates when records should be deleted from the database, the low value indicates what should be the result after the cleanup.
So, for example size of db:
when the db is 100mb or more in size, delete records until the size of the db has shrinked to 20mb.
I have an id value in the table, but this is not necessarily 1 to 50000 for the first 50000 records.
also note that I have to dump those records before deleting them (although I would be very glad to be able to delete them in an efficent manner in the first place)