8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Filtered Partition Maintenance Operations in Oracle Database 12c Release 2 (12.2)
Oracle Database 12c Release 2 (12.2) allows you to add a filter condition when you move, split or merge a partition, move a table or convert a non-partitioned table to a partitioned table. Only those rows matching the filter condition are included in the resulting object.
The filter condition is only allowed on heap tables, can only reference columns from the table and can be used in conjunction with online operations with some additional considerations.
This article provides some simple examples using filter conditions with maintenance operations. The examples in this article are mostly based on partition management operations, but this functionality applies equally to subpartition maintenance operations.
- Move a Table
- Convert a Non-Partitioned Table to a Partitioned Table
- Move a Partition
- Split a Partition
- Merge Partitions
Related articles.
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)
- Online Move of Partitions and Sub-Partitions in Oracle 12c Release 1
- Online Conversion of a Non-Partitioned Table to a Partitioned Table in Oracle Database 12c Release 2 (12.2)
- Online Move of a Table in Oracle Database 12c Release 2 (12.2)
Move a Table
Create and populate a new test table.
DROP TABLE t1 PURGE; CREATE TABLE t1 AS SELECT level AS id, 'Description for ' || level AS description FROM dual CONNECT BY level <= 100; COMMIT;
Check the contents of the table.
SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_id FROM t1; TOTAL_ROWS MIN_ID MAX_ID ---------- ---------- ---------- 100 1 100 SQL>
Move the table, filtering out rows with an ID
value greater than 50.
ALTER TABLE t1 MOVE ONLINE INCLUDING ROWS WHERE id <= 50;
Check the contents of the table.
SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_id FROM t1; TOTAL_ROWS MIN_ID MAX_ID ---------- ---------- ---------- 50 1 50 SQL>
The rows with an ID
value between 51 and 100 have been removed.
Convert a Non-Partitioned Table to a Partitioned Table
Create and populate a new test table.
DROP TABLE t1 PURGE; CREATE TABLE t1 AS SELECT level AS id, 'Description for ' || level AS description FROM dual CONNECT BY level <= 100; COMMIT;
Check the contents of the table.
SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_id FROM t1; TOTAL_ROWS MIN_ID MAX_ID ---------- ---------- ---------- 100 1 100 SQL>
Convert the table to a partitioned, filtering out rows with an ID
value greater than 50.
ALTER TABLE t1 MODIFY PARTITION BY RANGE (id) ( PARTITION t1_le_50 VALUES LESS THAN (51), PARTITION t1_le_101 VALUES LESS THAN (101) ) INCLUDING ROWS WHERE id <= 50;
Check the contents of the partitions.
EXEC DBMS_STATS.gather_table_stats(USER, 'T1'); COLUMN table_name FORMAT A20 COLUMN partition_name FORMAT A20 COLUMN high_value FORMAT A20 SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions WHERE table_name = 'T1' ORDER BY 1,2; TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS -------------------- -------------------- -------------------- ---------- T1 T1_LE_101 101 0 T1 T1_LE_50 51 50 SQL> SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_id FROM t1; TOTAL_ROWS MIN_ID MAX_ID ---------- ---------- ---------- 50 1 50 SQL>
The rows with an ID
value between 51 and 100 have been removed.
Move a Partition
Create and populate a new test partitioned table.
DROP TABLE t1 PURGE; CREATE TABLE t1 ( id NUMBER, description VARCHAR2(50) ) PARTITION BY RANGE (id) ( PARTITION t1_le_50 VALUES LESS THAN (51), PARTITION t1_le_101 VALUES LESS THAN (101) ); INSERT INTO t1 SELECT level AS id, 'Description for ' || level AS description FROM dual CONNECT BY level <= 100; COMMIT;
Check the contents of the partitions.
EXEC DBMS_STATS.gather_table_stats(USER, 'T1'); COLUMN table_name FORMAT A20 COLUMN partition_name FORMAT A20 COLUMN high_value FORMAT A20 SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions WHERE table_name = 'T1' ORDER BY 1,2; TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS -------------------- -------------------- -------------------- ---------- T1 T1_LE_101 101 50 T1 T1_LE_50 51 50 SQL> SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_id FROM t1; TOTAL_ROWS MIN_ID MAX_ID ---------- ---------- ---------- 100 1 100 SQL>
Move a partition, filtering out rows with an ID
value greater than 50.
ALTER TABLE t1 MOVE PARTITION t1_le_101 ONLINE TABLESPACE users INCLUDING ROWS WHERE id <= 50;
Check the contents of the partitions.
EXEC DBMS_STATS.gather_table_stats(USER, 'T1'); COLUMN table_name FORMAT A20 COLUMN partition_name FORMAT A20 COLUMN high_value FORMAT A20 SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions WHERE table_name = 'T1' ORDER BY 1,2; TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS -------------------- -------------------- -------------------- ---------- T1 T1_LE_101 101 0 T1 T1_LE_50 51 50 SQL> SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_id FROM t1; TOTAL_ROWS MIN_ID MAX_ID ---------- ---------- ---------- 50 1 50 SQL>
The rows with an ID
value between 51 and 100 have been removed.
Split a Partition
Create and populate a new test partitioned table.
DROP TABLE t1 PURGE; CREATE TABLE t1 ( id NUMBER, description VARCHAR2(50) ) PARTITION BY RANGE (id) ( PARTITION t1_le_101 VALUES LESS THAN (101) ); INSERT INTO t1 SELECT level AS id, 'Description for ' || level AS description FROM dual CONNECT BY level <= 100; COMMIT;
Check the contents of the partition.
EXEC DBMS_STATS.gather_table_stats(USER, 'T1'); COLUMN table_name FORMAT A20 COLUMN partition_name FORMAT A20 COLUMN high_value FORMAT A20 SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions WHERE table_name = 'T1' ORDER BY 1,2; TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS -------------------- -------------------- -------------------- ---------- T1 T1_LE_101 101 100 SQL> SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_id FROM t1; TOTAL_ROWS MIN_ID MAX_ID ---------- ---------- ---------- 100 1 100 SQL>
Split the partition, filtering out rows with an ID
value greater than 50.
ALTER TABLE t1 SPLIT PARTITION t1_le_101 AT (51) INTO (PARTITION t1_le_51, PARTITION t1_le_101) INCLUDING ROWS WHERE id <= 50 ONLINE;
Check the contents of the partitions.
EXEC DBMS_STATS.gather_table_stats(USER, 'T1'); COLUMN table_name FORMAT A20 COLUMN partition_name FORMAT A20 COLUMN high_value FORMAT A20 SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions WHERE table_name = 'T1' ORDER BY 1,2; TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS -------------------- -------------------- -------------------- ---------- T1 T1_LE_101 101 0 T1 T1_LE_51 51 50 SQL> SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_id FROM t1; TOTAL_ROWS MIN_ID MAX_ID ---------- ---------- ---------- 50 1 50 SQL>
The rows with an ID
value between 51 and 100 have been removed.
Merge Partitions
Create and populate a new test partitioned table.
DROP TABLE t1 PURGE; CREATE TABLE t1 ( id NUMBER, description VARCHAR2(50) ) PARTITION BY RANGE (id) ( PARTITION t1_le_51 VALUES LESS THAN (51), PARTITION t1_le_101 VALUES LESS THAN (101) ); INSERT INTO t1 SELECT level AS id, 'Description for ' || level AS description FROM dual CONNECT BY level <= 100; COMMIT;
Check the contents of the partitions.
EXEC DBMS_STATS.gather_table_stats(USER, 'T1'); COLUMN table_name FORMAT A20 COLUMN partition_name FORMAT A20 COLUMN high_value FORMAT A20 SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions WHERE table_name = 'T1' ORDER BY 1,2; TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS -------------------- -------------------- -------------------- ---------- T1 T1_LE_101 101 50 T1 T1_LE_51 51 50 SQL> SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_id FROM t1; TOTAL_ROWS MIN_ID MAX_ID ---------- ---------- ---------- 100 1 100 SQL>
Merge the partitions, filtering out rows with an ID
value greater than 50.
ALTER TABLE t1 MERGE PARTITIONS t1_le_51, t1_le_101 INTO PARTITION t1_le_101 INCLUDING ROWS WHERE id <= 50;
Check the contents of the partitions.
EXEC DBMS_STATS.gather_table_stats(USER, 'T1'); COLUMN table_name FORMAT A20 COLUMN partition_name FORMAT A20 COLUMN high_value FORMAT A20 SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions WHERE table_name = 'T1' ORDER BY 1,2; TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS -------------------- -------------------- -------------------- ---------- T1 T1_LE_101 101 50 SQL> SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_id FROM t1; TOTAL_ROWS MIN_ID MAX_ID ---------- ---------- ---------- 50 1 50 SQL>
The rows with an ID
value between 51 and 100 have been removed.
For more information see:
- Filtering Maintenance Operations
- ALTER TABLE : filter_condition
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)
- Online Move of Partitions and Sub-Partitions in Oracle 12c Release 1
- Online Conversion of a Non-Partitioned Table to a Partitioned Table in Oracle Database 12c Release 2 (12.2)
- Online Move of a Table in Oracle Database 12c Release 2 (12.2)
Hope this helps. Regards Tim...