Search code examples
sqloracle-databasepartitioningalter-table

Re-partition table from scratch, in Oracle

How do you re-partition an existing table in Oracle DB?

For example, in MySQL you can simply use ALTER TABLE to redefine the partitions from scratch:

/* MySQL code, not Oracle DB */
ALTER TABLE t1
    PARTITION BY HASH(id)
    PARTITIONS 8;

I've been googling for a while, and I can't find anything in Oracle that provides this functionality.

But it has to be possible somehow, because it's essential functionality for ongoing partition management.

In my case, I'm building a historical snapshot table, partitioned by snapshot date range.
Each day, I need to add a partition for the new day, but also merge old snapshot partitions since we are only keeping monthly snapshots from previous quarters/years.
Redefining the entire partition configuration from scratch makes this very straight-forward, and I would presume such a feature would be available in any top-tier database product.

Solution

  • You have two separate tasks here:

    1. Alter partitioning of an existing table (nonpartitioned -> hash partitioned, hash partitioned -> range partitioned, etc.), which is what your alter table statement is doing. Not sure how it's implemented under the hood of MySQL, but in Oracle, for better or worse, it is not possible to do using a single alter table. Instead, you have two options: a) boring, create a new partitioned table, copy the data there, and swap the tables, and b) use DBMS_REDEFINITION package, which is essentially Oracle's attempt at wrapping (a) in a single call.
    2. Ongoing partition management, which is adding, moving, renaming, splitting and merging partitions of a table that is already partitioned in some way. This is fully supported with alter table. See the ALTER TABLE reference (scroll down for merge example).

    Update:

    I stand corrected, they added the corresponding ALTER TABLE functionality in 12.2, and it's present in the latest versions (18c, 19c) as well. That being said, in many cases it won't be a good idea to use that feature.

    Why? Because while changing the table partitioning can be done online (not blocking any DML), but even so it's not a metadata-only operation - Oracle has to physically move the data around. And the ALTER TABLE itself is atomic - which means, you cannot restart or pause the operation, it either completes as a whole, or rolls back as a whole.

    Now, consider this. The fact table in the system I'm maintaining contains around 4 billion rows PER DAY. The total size of the fact table is around ~40TB at the moment. If I decide to change partitioning with a single ALTER TABLE command, moving this amount of data would take indefinite amount of time (I'm talking days or more), block any DDL operations during that time, and probably take a ton of temp space. And in case of some failure the only option I have is to start over.

    After a certain point the option just becomes unviable.