0

I have a very simple table, on Oracle 10g, that keeps the log messages from a process. This table generates about 1 GB per day. I would like to keep just 1 week of data.

What is the best approach to accomplish this? Create a procedure that continuously delete the old data? Do I need to use partitions?

I can delete/truncate/drop the entire table if necessary.

This is the table:

CREATE TABLE "TABLE_LOGS" 
   (    
    "TABLE_ID" VARCHAR2(200 BYTE), 
    "TABLE_NUMBER" NUMBER(19,0), 
    "DESCRIPTION" VARCHAR2(2000 BYTE), 
    "DATE" TIMESTAMP (6), 
    "TYPE" VARCHAR2(100 BYTE), 
    "LEVEL" NUMBER(*,0)
   )
Rodrick
  • 595
  • 10
  • 27

2 Answers2

2

It's not mandatory, but partitioning can be an effective strategy. The term Information Lifecycle Management (ILM) is often used. If you create the table range partitioned by day, then you can simply truncate/drop the oldest partition, so that you only have 7 (or 8) active partitions. The advantages of this are

  • Truncate/drop partition will be faster than delete
  • You will have better space management.
BobC
  • 4,208
  • 1
  • 12
  • 15
2
  1. Create an index on you date column (if not already there).
  2. Have a nightly job run the following SQL:

delete table_logs where date > sysdate - 7;

commit;

This should take less than 1 minute, if you DB is well tuned.

Sam
  • 404
  • 4
  • 7
  • Shouldn't be < sysdate -7? Thanks, very easy approach. – Rodrick Mar 01 '17 at 12:03
  • If i use this approach, what happens with the disk space? Will the same disk space, of the deleted records, be used by the new data? I mean, I think the table will be using the same space. I am correct? Will all the disk space be used suddenly? – Rodrick Mar 01 '17 at 14:02
  • The space "freed" by a delete statement will eventually be reclaimed. Perhaps :) It really depends. If a data block is full, to the point that it has been taken off the freelist, it may take several rows to be deleted from that block before it's empty enough to be put back on the freelist. – BobC Mar 04 '17 at 00:44