CODEWITHSUNDEEP
oracle-databasepartitioning
Prasanna Narayanan
Prasanna Narayanan

Reputation: 439

Deleting records from Oracle MAX_PARTITION

We have a table with partitions. It also has an overflow partition (max partition) which sorts of acts as a catch-all for records which do not match the partition criteria. The idea was to create the partitions ahead of time so the records never end up in the max_partition. However for one table, this was missed out, so all the records ended up in that single partition.

Now most of these records are not used anymore so they can be deleted. However our approach is to drop the partitions when its too old. This cannot be done in this case. Is there an easy way to handle the purge?

Maybe its an idea to create the partitions now and move the records to them and then drop the partition now, but however it seems like its going to be very poor in performance. The other option was to create a temp table where a subset of records are moved and deleted from there, but again moving the records individually seems time consuming. This table has around 5 million records.

Which would be the best way forward, performance wise. We could manage a little downtime but not much.

We use Oracle 11g.

The table creation script looks something like this:

CREATE TABLE "TRANSACTIONS" 
   ("year" number(4,0) NOT NULL ENABLE)
  PARTITION BY RANGE ("year") 
 (PARTITION "P_OLD" VALUES LESS THAN (2010),
  PARTITION "P_2011"   VALUES LESS THAN (2011),
 ...

  PARTITION "P_MAX"    VALUES LESS THAN (MAXVALUE));

Upvotes: 0

Views: 658

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

There is no need to drop the partition, you can purge it. 

alter table TRANSACTIONS TRUNCATE PARTITION P_MAX UPDATE INDEXES;

or if you prefer, you can also delete the rows:

delete from TRANSACTIONS PARTITION (P_MAX);

You may use INTERVAL partition to make it simpler (actually I don't understand your question):

CREATE TABLE TRANSACTIONS ( 
   ...
    TRANSACTION_DATE TIMESTAMP(0) NOT NULL
)    
PARTITION BY RANGE (TRANSACTION_DATE) INTERVAL (INTERVAL '12' MONTH) 
    (PARTITION P_OLD VALUES LESS THAN (TIMESTAMP '2000-01-01 00:00:00' ) )
ENABLE ROW MOVEMENT;

Upvotes: 3

Related Questions