Exchange partition with update index

最新推荐文章于 2021-04-05 22:34:50 发布

xiadingling

最新推荐文章于 2021-04-05 22:34:50 发布

阅读量625

点赞数 1

分类专栏： oracle

本文链接：https://blog.csdn.net/xiadingling/article/details/87810102

版权

91 篇文章 0 订阅

订阅专栏

Exchange none composite-partitioned table

ALTER TABLE TEST_P EXCHANGE PARTITION P1 WITH TABLE TEST_TEMP UPDATE INDEXES;

Exchange subparition(composite-partitioned table)

ALTER TABLE TEST_P2 EXCHANGE SUBPARTITION P1 WITH TABLE TEST_TEMP UPDATE INDEXES;

在使用 EXCHANGE　PARTITION　with update index可以自动更新索引，只有Local索引才会失效，全局索引会被自动更新。

但是也有性能问题要考虑。

Oracle建议全局索引手动重建并使用 NOLOGGING 效率会好些。

在实际操作中，需要提前准备的工作：

1.Source 分区表

2. 用于交换的普通表，表结构月Source分区表保持一致

3.如果是交换到不同用户下的不同的表空间，需要有quota 权限在相关的表空间上

ALTER USER Source_user_name QUOTA UNLIMITED ON Target_TS_name;
ALTER USER Target_user_name QUOTA UNLIMITED ON Source_TS_name;

Alter table privilege

4.target 分区表

Considerations when Updating Indexes Automatically

The following implications are worth noting when you specify UPDATE INDEXES:

The partition DDL statement takes longer to execute, because indexes that were previously marked UNUSABLE are updated. However, you must compare this increase with the time it takes to execute DDL without updating indexes, and then rebuild all indexes. A rule of thumb is that it is faster to update indexes if the size of the partition is less that 5% of the size of the table.
The DROP, TRUNCATE, and EXCHANGE operations are no longer fast operations. Again, you must compare the time it takes to do the DDL and then rebuild all indexes.
When you update a table with a global index:
- The index is updated in place. The updates to the index are logged, and redo and undo records are generated. In contrast, if you rebuild an entire global index, you can do so in NOLOGGING mode.
- Rebuilding the entire index manually creates a more efficient index, because it is more compact with better space utilization.
The UPDATE INDEXES clause is not supported for index-organized tables. However, the UPDATE GLOBAL INDEXES clause may be used with DROP PARTITION, TRUNCATE PARTITION, and EXCHANGEPARTITION operations to keep the global indexes on index-organized tables usable. For the remaining operations in the above list, global indexes on index-organized tables remain usable. In addition, local index partitions on index-organized tables remain usable after a MOVE PARTITION operation.

The following operations support the UPDATE INDEXES clause:

关注

专栏目录