Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: how to cleanup a database ?

    I have a java application that checks the database for several criteria at specified intervals and if one of those criteria is met, it cleans up the database.

    example:
    * criteria = size
    * lower limit = 50000
    * upper limit = 100000
    * #records in db: 120000

    -> 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 ?

    thanx in advance

  2. #2
    Join Date
    Nov 2004
    Posts
    6
    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) "

  3. #3
    Join Date
    Nov 2004
    Posts
    3
    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
    - timestamp:
    * 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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •