CODEWITHSUNDEEP
oracle-databaseoracle10gpartition
Rodrick
Rodrick

Reputation: 615

Do I need to use partition for cleaning old data on a Oracle 10g table?

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

Upvotes: 0

Views: 108

Answers (2)

Sam
Sam

Reputation: 414

  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.

Upvotes: 2

BobC
BobC

Reputation: 4424

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.

Upvotes: 2

Related Questions