CODEWITHSUNDEEP
databaseoracle-databasepartitioningdatabase-partitioning
Calimero
Calimero

Reputation: 299

Is partitioning the correct feature for periodical deletions in large tables?

If I have two tables in a (Oracle) database that keeps an history of records through time :

CREATE TABLE Records(
    RecordPackId NUMBER,
    RecordId NUMBER,
    Data VARCHAR2(4000),
    PRIMARY KEY (RecordPackid, RecordId)
)

CREATE TABLE RecordPacks(
    RecordPackId NUMBER,
    DateTime DATE,
    PRIMARY KEY (RecordPackId)
)

Over time, Records table grew to 11GB and 300M rows while RecordPacks grew to 11k rows.

I would like to set up a periodic process that would fetch RecordPacks indices meeting some criterion (for example sake, where date > 1 month ago and date isn't the last day of a month) and delete the corresponding record from the record table.

I was thinking that partitioning could be the solution to avoid rather long delete operations. If there is a partition of Records per distinct RecordPackId value, it could drop the partition rather quickly compared to delete from where.

Moreover, Oracle documentation says that tables should be partitioned when they weight more than 2GB and contain historic data where new data would be added to newest partition.

However reading the documentation on partition, it seems partition by range, list or hash all require to know the precise number of partitions beforehand. The documentation goes further with partitioned index but I realize that as I drop branches of the index tree, the tree would have to be rebuilt to rebalance it and the operation would probably take forever, am i wrong ?

Is it feasible, and is it a good idea to have a partition by distinct value or am I absolutely mad ? Is there a database feature that would be more sound ?

What about future inserts ? Should I create a partition everytime I insert or can the database create a new partition automatically ?

Upvotes: 0

Views: 35

Answers (1)

Paul W
Paul W

Reputation: 11603

For high volume deletes / archiving like this, you absolutely want to use partitioning and avoid deletes.

Your concern about the index is alleviated by the ability to define those indexes as "LOCAL" - that will create a separate index segment for every table partition and dropping one partition will drop the associated index segment without impacting the rest of the index - the index will remain usable. Just be sure to add the keyword "LOCAL" to your indexes.

You do not need to know the precise number of partitions beforehand. You are probably seeing the initial partition list in examples of creating a partitioned table. You can add partitions to an existing partitioned table at any time. However, the best is to use "interval partitioning" to have Oracle automatically create a new daily partition (or monthly, or whatever meets your needs) whenever incoming data needs a partition that doesn't yet exist.

Lastly, while you could create a foreign key between your two tables and use partition by reference, it's better for performance to denormalize your date column so its in the child table also, and partition by it normally. That would also simplify your code, as you wouldn't have to reference the parent table at all to determine what to drop.

Upvotes: 2

Related Questions