概念 http://www.cndba.cn/leo1990/article/2183http://www.cndba.cn/leo1990/article/2183http://www.cndba.cn/leo1990/article/2183

Partition maintenance operations can now be combined with data filtering. For example, a partition can be compressed and moved to a different tablespace, but only the data satisfying the specific filter criteria is actually moved.
分区维护操作现在可以与数据过滤相结合。例如，可以将分区压缩并移动到不同的表空间，但只有满足特定筛选条件的数据才实际移动。

Partition maintenance operations with data filtering combine two of the most common data maintenance operations. This combination not only makes the partition maintenance operation more flexible and powerful, but also makes it more performant and less resource intensive compared to the two separate data management operations.

分区维护操作与数据过滤结合了两个最常见的数据维护操作。这种组合不仅使分区维护操作更加灵活和强大，而且与两个独立的数据管理操作相比，它还使分区维护更加高效，资源更少。

实验 http://www.cndba.cn/leo1990/article/2183

1) 创建分区并插入数据
[leo@www.cndba.cn1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Aug 27 15:24:20 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn test/test@pdbcndba
Connected.
SQL> CREATE TABLE test_tbl
(
 id1 NUMBER,
 id2 NUMBER,
 id3 NUMBER
)
PARTITION BY RANGE(id1)
(
  PARTITION p1 VALUES LESS THAN(10),
  PARTITION p2 VALUES LESS THAN(MAXVALUE)
);

Table created.


SQL> INSERT INTO test_tbl VALUES(1, 1, 1);

1 row created.

SQL> INSERT INTO test_tbl VALUES(2, 2, 2);

1 row created.

SQL> INSERT INTO test_tbl VALUES(3, 3, 3);

1 row created.

SQL> commit;

Commit complete.

SQL> col table_name for a20
SQL> col partition_name for a20
SQL> SELECT table_name,partition_name
FROM   user_tab_partitions
WHERE  table_name = 'TEST_TBL'; 

TABLE_NAME	     PARTITION_NAME
-------------------- --------------------
TEST_TBL	     P1
TEST_TBL	     P2

2） 分区p1 有3条数据
SQL> SELECT * FROM test_tbl PARTITION(p1);

       ID1	  ID2	     ID3
---------- ---------- ----------
	 1	    1	       1
	 2	    2	       2
	 3	    3	       3

3) 将ID<3 的数据 移到分区p1 。
SQL> ALTER TABLE test_tbl
MOVE PARTITION p1
INCLUDING ROWS WHERE id1 < 3; 

Table altered.
4) 可以看到p1分区保存id<3的数据
SQL> SELECT * FROM test_tbl PARTITION(p1);

       ID1	  ID2	     ID3
---------- ---------- ----------
	 1	    1	       1
	 2	    2	       2

SQL> SELECT table_name,partition_name
FROM   user_tab_partitions
WHERE  table_name = 'TEST_TBL';  2    3  

TABLE_NAME	     PARTITION_NAME
-------------------- --------------------
TEST_TBL	     P1
TEST_TBL	     P2

http://www.cndba.cn/leo1990/article/2183

操作文档http://www.cndba.cn/leo1990/article/2183

http://docs.oracle.com/database/122/VLDBG/maintenance-partition-can-be-performed.htm#VLDBG-GUID-E5ACAFF8-AB4D-4165-9833-05BB46F149DAhttp://www.cndba.cn/leo1990/article/2183http://www.cndba.cn/leo1990/article/2183http://www.cndba.cn/leo1990/article/2183

版权声明：本文为博主原创文章，未经博主允许不得转载。