MySQL Forums
Forum List  »  Partitioning

Using partitioning with ARCHIVE engine for data archiving
Posted by: Veerabahu Subramanian
Date: December 20, 2010 10:12AM

Experts,
In our application we are planning to archive large amount of data, i.e moving from operational table to other table with ARCHIVE engine there by reducing disk space. For the below two reasons we have choosen partitions too,

1. Actually there is no need for an ever growing data. Need to delete at some point of time (say after an year). Archive engine doesn't allows deletion. However if we are able to move the unwanted data to a partition then we could drop that partition. We are able to achive this with one partition like
CREATE TABLE test (
id int DEFAULT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
PARTITION BY RANGE(Operation) (
PARTITION p1 VALUES LESS THAN MAXVALUE
);
Alter table test reorganize partition p1 into (
PARTITION p2 VALUES LESS THAN (25),
PARTITION p1 VALUES LESS THAN MAXVALUE
);
then Alter table test drop partition p2.
By this way I could delete the values that are less than 25.
2. However the above approach doesn't effectively make use of parition, i.e. pruning the number of records to scan. Say
select * from test where id<25 is going scan all the rows. To achieve this we need to create more than one partition like
CREATE TABLE test (
id int DEFAULT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
PARTITION BY RANGE(Operation) (
PARTITION p1 VALUES LESS THAN 10
PARTITION p1 VALUES LESS THAN 20
..
..
PARTITION p1 VALUES LESS THAN MAXVALUE
);
But when we partition like this we won't be able to delete certain range of values like delete all values between 15 to 20.

Can you kindly clarify how we could achieve both the above i.e. compressed storage (ARCHIVE) and partition pruning.
One possible solution that I could think of is ,
Partition as shown in step 2, use Archive engine. Whenever data needs to be deleted change the engine type from Archive to some other. Once deleted change it back to ARCHIVE. Kindly comment on this too. How this approach will behave with huge data

Thanks,
Veerabahu

Options: ReplyQuote


Subject
Views
Written By
Posted
Using partitioning with ARCHIVE engine for data archiving
8691
December 20, 2010 10:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.