ORACLE 12.2新特性-在线将非分区表转换成分区表

最新推荐文章于 2024-08-31 21:57:21 发布

转载最新推荐文章于 2024-08-31 21:57:21 发布 · 644 阅读

CC 4.0 BY-SA版权

原文链接：https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/evolve-nopartition-table.html#GUID-5FDB7D59-DD05-40E4-8AB4-AF82EA0D0FE5

Oracle 专栏收录该内容

73 篇文章

订阅专栏

本文介绍如何使用ALTER TABLE语句中的MODIFY子句，将非分区表转换为分区表，同时利用ONLINE关键字实现转换过程中的并发DML操作。文章详细解释了语法，并给出一个具体示例，展示如何指定分区条件、更新索引状态及存储属性。

通过在ALTER table SQL语句中添加MODIFY子句，可以将非分区表转换为分区表。

此外，还可以指定关键字ONLINE，在转换过程中启用并发DML操作。

语法如下：

ALTER TABLE table_name MODIFY table_partitioning_clauses
  [ filter_condition ]
  [ ONLINE ]
  [ UPDATE INDEXES [ ( index { local_partitioned_index | global_partitioned_index | GLOBAL }
                     [, index { local_partitioned_index | global_partitioned_index | GLOBAL } ]... )
                   ]
  ]

下面是ALTER TABLE语句的一个示例，该语句使用ONLINE关键字将联机转换为分区表。

示例---Using the MODIFY clause of ALTER TABLE to convert online to a partitioned table

ALTER TABLE employees_convert MODIFY
  PARTITION BY RANGE (employee_id) INTERVAL (100)
  ( PARTITION P1 VALUES LESS THAN (100),
    PARTITION P2 VALUES LESS THAN (500)
   ) ONLINE
  UPDATE INDEXES
 ( IDX1_SALARY LOCAL,
   IDX2_EMP_ID GLOBAL PARTITION BY RANGE (employee_id)
  ( PARTITION IP1 VALUES LESS THAN (MAXVALUE))
 );

使用UPDATE INDEX子句时的注意事项：

When using the UPDATE INDEXES clause, note the following.

This clause can be used to change the partitioning state of indexes and storage properties of the indexes being converted.
The specification of the UPDATE INDEXES clause is optional.

Indexes are maintained both for the online and offline conversion to a partitioned table.
This clause cannot change the columns on which the original list of indexes are defined.
This clause cannot change the uniqueness property of the index or any other index property.
If you do not specify the tablespace for any of the indexes, then the following tablespace defaults apply.
- Local indexes after the conversion collocate with the table partition.
- Global indexes after the conversion reside in the same tablespace of the original global index on the non-partitioned table.
If you do not specify the INDEXES clause or the INDEXES clause does not specify all the indexes on the original non-partitioned table, then the following default behavior applies for all unspecified indexes.
- Global partitioned indexes remain the same and retain the original partitioning shape.
- Non-prefixed indexes become global nonpartitioned indexes.
- Prefixed indexes are converted to local partitioned indexes.
  
  Prefixed means that the partition key columns are included in the index definition, but the index definition is not limited to including the partitioning keys only.
- Bitmap indexes become local partitioned indexes, regardless whether they are prefixed or not.
  
  Bitmap indexes must always be local partitioned indexes.
The conversion operation cannot be performed if there are domain indexes.