12c Filtering Maintenance Operations

Partition maintenance operations support the addition of data filtering, enabling the combination of partition and data maintenance.

A filtered partition maintenance operation only preserves the data satisfying the data filtering as part of the partition maintenance. The capability of data filtering applies to MOVE PARTITION, MERGE PARTITION, and SPLIT PARTITION .

Example 4-27 shows the use of the ALTER TABLE statement to move a partition while removing all orders that are not open (closed orders).

The filtering predicate must be on the partitioned table. All partition maintenance operations that can be performed online (MOVE and SPLIT) can also be performed as filtered partition maintenance operations. With ONLINE specified, DML operations on the partitions being maintained are allowed.

模拟实验

insert into dbmt.YHTEST2 select * from dbmt.YHTEST





SQL> select count(*) from dbmt.YHTEST2 partition (PART_202006)  where   wbs_level='2';

  COUNT(*)
----------
     11110

SQL> select count(*) from dbmt.YHTEST2 partition (PART_202006);

  COUNT(*)
----------
     15025

SQL> 
SQL> 
SQL> ALTER TABLE dbmt.yhtest2
  2    MOVE PARTITION PART_202006  COMPRESS ONLINE
  3    INCLUDING ROWS WHERE wbs_level='2';



SQL> select count(*) from dbmt.YHTEST2 partition (PART_202006);

  COUNT(*)
----------
     11110

SQL> select count(*) from dbmt.YHTEST2 partition (PART_202006)  where   wbs_level='2';

  COUNT(*)
----------
     11110