oracle支持分区移动,Oracle 12c 新特性 --- 移动分区支持数据过滤

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.

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

实验

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

https://www.cndba.cn/cndba/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

操作文档

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

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

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