oracle 12cR2 过滤分区维护操作
来源:互联网 发布:ubuntu删除图形界面 编辑:程序博客网 时间:2024/06/06 17:37
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.
Oracle 12cR2允许在移动、拆分和合并分区,移动表或者将普通表转换为分区表时增加过滤条件。只有满足过滤条件的行在结果对象中。
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 ASSELECT level AS id, 'Description for ' || level AS descriptionFROM dualCONNECT BY level <= 100;COMMIT;
Check the contents of the table.
检查表内容
SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_idFROM t1;TOTAL_ROWS MIN_ID MAX_ID---------- ---------- ---------- 100 1 100SQL>
Move the table, filtering out rows with an ID
value greater than 50.
移动表,过滤掉ID大于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_idFROM t1;TOTAL_ROWS MIN_ID MAX_ID---------- ---------- ---------- 50 1 50SQL>
The rows with an ID
value between 51 and 100 have been removed.
可见ID在51至100之间的值已经被移除。
Convert a Non-Partitioned Table to a Partitioned Table
普通表转为分区表
Create and populate a new test table.
DROP TABLE t1 PURGE;CREATE TABLE t1 ASSELECT level AS id, 'Description for ' || level AS descriptionFROM dualCONNECT BY level <= 100;COMMIT;
Check the contents of the table.
SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_idFROM t1;TOTAL_ROWS MIN_ID MAX_ID---------- ---------- ---------- 100 1 100SQL>
Convert the table to a partitioned, filtering out rows with an ID
value greater than 50.
转为分区表,过滤掉ID值大于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 A20COLUMN partition_name FORMAT A20COLUMN high_value FORMAT A20SELECT table_name, partition_name, high_value, num_rowsFROM user_tab_partitionsWHERE table_name = 'T1'ORDER BY 1,2;TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS-------------------- -------------------- -------------------- ----------T1 T1_LE_101 101 0T1 T1_LE_50 51 50SQL>SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_idFROM t1;TOTAL_ROWS MIN_ID MAX_ID---------- ---------- ---------- 50 1 50SQL>
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 t1SELECT level AS id, 'Description for ' || level AS descriptionFROM dualCONNECT BY level <= 100;COMMIT;
Check the contents of the partitions.
EXEC DBMS_STATS.gather_table_stats(USER, 'T1');COLUMN table_name FORMAT A20COLUMN partition_name FORMAT A20COLUMN high_value FORMAT A20SELECT table_name, partition_name, high_value, num_rowsFROM user_tab_partitionsWHERE table_name = 'T1'ORDER BY 1,2;TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS-------------------- -------------------- -------------------- ----------T1 T1_LE_101 101 50T1 T1_LE_50 51 50SQL>SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_idFROM t1;TOTAL_ROWS MIN_ID MAX_ID---------- ---------- ---------- 100 1 100SQL>
Move a partition, filtering out rows with an ID
value greater than 50.
移动分区,过滤掉ID大于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 A20COLUMN partition_name FORMAT A20COLUMN high_value FORMAT A20SELECT table_name, partition_name, high_value, num_rowsFROM user_tab_partitionsWHERE table_name = 'T1'ORDER BY 1,2;TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS-------------------- -------------------- -------------------- ----------T1 T1_LE_101 101 0T1 T1_LE_50 51 50SQL>SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_idFROM t1;TOTAL_ROWS MIN_ID MAX_ID---------- ---------- ---------- 50 1 50SQL>
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 t1SELECT level AS id, 'Description for ' || level AS descriptionFROM dualCONNECT BY level <= 100;COMMIT;
Check the contents of the partition.
EXEC DBMS_STATS.gather_table_stats(USER, 'T1');COLUMN table_name FORMAT A20COLUMN partition_name FORMAT A20COLUMN high_value FORMAT A20SELECT table_name, partition_name, high_value, num_rowsFROM user_tab_partitionsWHERE table_name = 'T1'ORDER BY 1,2;TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS-------------------- -------------------- -------------------- ----------T1 T1_LE_101 101 100SQL>SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_idFROM t1;TOTAL_ROWS MIN_ID MAX_ID---------- ---------- ---------- 100 1 100SQL>
Split the partition, filtering out rows with an ID
value greater than 50.
拆分分区,过滤掉ID值大于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 A20COLUMN partition_name FORMAT A20COLUMN high_value FORMAT A20SELECT table_name, partition_name, high_value, num_rowsFROM user_tab_partitionsWHERE table_name = 'T1'ORDER BY 1,2;TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS-------------------- -------------------- -------------------- ----------T1 T1_LE_101 101 0T1 T1_LE_51 51 50SQL>SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_idFROM t1;TOTAL_ROWS MIN_ID MAX_ID---------- ---------- ---------- 50 1 50SQL>
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 t1SELECT level AS id, 'Description for ' || level AS descriptionFROM dualCONNECT BY level <= 100;COMMIT;
Check the contents of the partitions.
EXEC DBMS_STATS.gather_table_stats(USER, 'T1');COLUMN table_name FORMAT A20COLUMN partition_name FORMAT A20COLUMN high_value FORMAT A20SELECT table_name, partition_name, high_value, num_rowsFROM user_tab_partitionsWHERE table_name = 'T1'ORDER BY 1,2;TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS-------------------- -------------------- -------------------- ----------T1 T1_LE_101 101 50T1 T1_LE_51 51 50SQL>SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_idFROM t1;TOTAL_ROWS MIN_ID MAX_ID---------- ---------- ---------- 100 1 100SQL>
Merge the partitions, filtering out rows with an ID
value greater than 50.
合并分区,过滤掉ID值大于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 A20COLUMN partition_name FORMAT A20COLUMN high_value FORMAT A20SELECT table_name, partition_name, high_value, num_rowsFROM user_tab_partitionsWHERE table_name = 'T1'ORDER BY 1,2;TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS-------------------- -------------------- -------------------- ----------T1 T1_LE_101 101 50SQL>SELECT COUNT(*) AS total_rows, MIN(id) AS min_id, MAX(id) AS max_idFROM t1;TOTAL_ROWS MIN_ID MAX_ID---------- ---------- ---------- 50 1 50SQL>
The rows with an ID
value between 51 and 100 have been removed.
- oracle 12cR2 过滤分区维护操作
- oracle 12cR2 多列列表分区
- Oracle 12cR2自动列表分区
- Install Oracle Database 12cR2 On Oracle Enterprise Linux7.3
- oracle部分维护操作
- Oracle 12cR2发布,金融行业准备大规模上了
- Install Oracle Database 12cR2 On RHEL7.3
- Linux 64位下Oracle 12CR2安装(一)
- Linux 64位下Oracle 12CR2安装(二)
- Linux 64位下Oracle 12CR2安装(一)
- Linux 64位下Oracle 12CR2安装(二)
- Linux平台 Oracle 12cR2 RAC安装Part1:准备工作
- Linux平台 Oracle 12cR2 RAC安装Part1:准备工作
- Linux平台 Oracle 12cR2 RAC安装Part2:GI配置
- 表分区的维护性操作
- Oracle 分区信息操作
- oracle 分区操作常用命令
- Oracle 分区操作
- rop和rop2的题目的wp
- 该如何将FineReader for Mac中的图像转换为Excel
- doctype是什么(面试题)
- 微服务架构中API网关角色
- DateUtils
- oracle 12cR2 过滤分区维护操作
- java调用sql server的存储过程(dbutils)
- 请求处理流程和API组件
- 中文分词评测
- 在Ubuntu下使用Python驱动MongoDB
- hive 分区操作
- [喜讯]分分钟带你入门nodejs开课啦
- 基本变量类型的类型转换
- oracle带日期操作语句