Document Type | Technical Information
Category | Administration
Applicable Product Version | 7FS02PS
Document Number | TADTI111
Overview
This document provides guidance on the necessary actions and management plans when partition table management is not properly performed.
Note
The following content is written based on some cases and should be referenced selectively depending on the operating environment and situation.
Method
Partition Splitting Using SPLIT
Explains an example where data has excessively accumulated in the MAXVALUE partition.
Note
When splitting a partition that is not MAXVALUE, both LOCAL and GLOBAL indexes become UNUSABLE, so an index rebuild is required.
However, for LOCAL indexes, only the indexes corresponding to the split partitions become UNUSABLE, so you can selectively rebuild only those partition indexes.
| TABLE Structure | ||||||||||||||||||||||||
| SQL> CREATE TABLE TIBERO.SALES ( sale_id NUMBER, sale_date DATE ) PARTITION BY RANGE (sale_date) ( PARTITION P_202501 VALUES LESS THAN (DATE '2025-02-01'), PARTITION P_202502 VALUES LESS THAN (DATE '2025-03-01'), PARTITION P_202503 VALUES LESS THAN (DATE '2025-04-01'), PARTITION P_MAX VALUES LESS THAN (MAXVALUE) ); | ||||||||||||||||||||||||
| Rows per Partition, with P_MAX having significantly more rows than other partitions | ||||||||||||||||||||||||
SQL> SELECT TABLE_NAME, PARTITION_NAME, BOUND, NUM_ROWS FROM USER_TAB_PARTITIONS
*The NUM_ROWS column shows values based on statistics collection and may not exactly match the actual row count. | ||||||||||||||||||||||||
| Error Occurs When Attempting to ADD Partition | ||||||||||||||||||||||||
For RANGE partitions, the boundary value of a new partition must be greater than that of the last partition, so if there is a partition with MAXVALUE, you cannot add a new partition. SQL> ALTER TABLE TIBERO.SALES ADD PARTITION P_202504 VALUES LESS THAN (DATE '2025-05-01'); | ||||||||||||||||||||||||
| Partition SPLIT | ||||||||||||||||||||||||
Split P_MAX into P_MAX and P_202504, moving some rows from P_MAX to P_202504. SQL> ALTER TABLE TIBERO.SALES SPLIT PARTITION P_MAX AT (DATE '2025-05-01') INTO (PARTITION P_202504, PARTITION P_MAX); SQL> SELECT TABLE_NAME, PARTITION_NAME, BOUND, NUM_ROWS FROM USER_TAB_PARTITIONS
*The NUM_ROWS column shows values based on statistics collection and may not exactly match the actual row count. |
Automatic Partition Creation Using INTERVAL
- INTERVAL partitioning is an extension of RANGE partitioning, where partitions are internally created for data ranges not initially declared.
- When using the INTERVAL option, if a value exceeds the partition RANGE, a partition is automatically created according to Tibero's internal naming convention. (ex. _table_owner_name_Prandom_number)
| TABLE Creation and INSERT | ||||||||||||
| SQL> CREATE TABLE TIBERO.SALES ( sale_id NUMBER, sale_date DATE ) PARTITION BY RANGE (sale_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION P_BEFORE_2025 VALUES LESS THAN (DATE '2025-01-01') ); SQL> INSERT INTO TIBERO.SALES VALUES (1, DATE '2024-12-31'); SQL> INSERT INTO TIBERO.SALES VALUES (2, DATE '2025-01-01'); SQL> INSERT INTO TIBERO.SALES VALUES (3, DATE '2025-02-01'); SQL> COMMIT; | ||||||||||||
| Partitions Automatically Created According to Input Values with INTERVAL Option | ||||||||||||
SQL> SELECT TABLE_NAME, PARTITION_NAME, BOUND
| ||||||||||||
| Note) Renaming Partition Names | ||||||||||||
SQL> ALTER TABLE TIBERO.SALES RENAME PARTITION _TIBERO_P578900 TO P_202501; SQL> SELECT TABLE_NAME, PARTITION_NAME, BOUND
|