How to Split Partition of Heavily Used Table

大型表分区优化

最新推荐文章于 2025-10-10 13:05:53 发布

转载最新推荐文章于 2025-10-10 13:05:53 发布 · 659 阅读

文章标签：

#table #constraints #insert #dictionary #validation #exchange

Partition分区专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一种在不停止插入操作的情况下，对大型数据库表中过大的分区进行分割的方法。通过创建临时表并交换分区，最终将大分区拆分为多个小分区，以提高数据库性能。

How to Split Partition of Heavily Used Table:
=============================================

Scenario:
- Large production database.
- Database contains table called BIG, that is partitioned by
   sequence number.
- BIG has an oversized partition called Colorado that has grown
   to a size of 30+ million rows.
- It is determined that this oversized partition should be
   split into 50 smaller partitions.
- A job exists that constantly inserts into the table called BIG

If the partition is split using the syntax ALTER TABLE BIG SPLIT PARTITION...., you receive
an error is returned; ORA-54 "resource busy and acquire with NOWAIT specified".

To split the partition the insert job will have to be stopped. Depending on the
size of the table, it may take up to a day to split or add a new partition.

The downtime associated with the split is too great as no DML can be
performed against the table during that time.

Are there any other options?

Suggestions:

1. Modify the INSERT job in order to eliminate the partition in question.
This should allow separate actions against this partition.

2. If the first option is not possible, try the following steps that give a
less time consuming scenario than using the SPLIT PARTITION syntax.

   A. Create table NEW ....- nonpartitioned, with the storage large enough
   to store all of the data from the oversized partition in table BAD.

   B. Alter table BIG exchange partition Colorado with table NEW without
      validation;

   -->the BAD partition Colorado which was previously a segment
      now becomes a table and table NEW becomes a partition.

   No data is actually moved, only the data dictionary gets updated.

   C. Alter table BIG truncate partition Colorado;

   -->this should be faster than delete. Disable referential integrity
   constraints if needed, before truncating.


   D. Now the 50 smaller tables from table NEW can be created - each table
   will then be exchanged for a partition - NEW1....NEW50


   E. Alter table BIG split partiton BAD.... - as many times as needed
   (50? ---> GOOD1,..., GOOD50)

   --> There is no data to move (BAD was truncated), so it should be quick.

   F. Insert into BIG partition (GOOD1) select * from NEW1;

   --> for each of new partitions;

Explanation:
============

The most time consuming step will be #E above, where the process is
moving the actual data into smaller tables that will be exchanged
with partitions later.

The benefits of using the alternative steps outlined in suggestion 2
are one, the process requires less time and two, the BIG table is still
accessible to users and processes during the time that the data is being
divided into smaller sections.