Oracle 12c新特性维护表分区Global Index不失效

最新推荐文章于 2024-08-24 12:16:43 发布

你好我是李白

最新推荐文章于 2024-08-24 12:16:43 发布

阅读量1.2k

点赞数

分类专栏： Oracle 文章标签： update indexes 12c新特性删除表分去global index不失效

本文链接：https://blog.csdn.net/jwocnimabi/article/details/103885249

版权

本文介绍了Oracle 12c的一个新特性，即在删除表分区时，通过'update indexes'子句可以防止全局索引失效。文中详细阐述了新特性的官方文档说明，并通过测试步骤展示了如何不使用和使用此新特性，以及四种手工维护全局索引的方法。

摘要由CSDN通过智能技术生成

1.新特性官方文档说明

这个特性为：Oracle 12c Release 1 New Feature

详情可参考官方文档：Database VLDB and Partitioning Guide

新特性限制

Asynchronous Global Index Maintenance for Dropping and Truncating Partitions

The partition maintenance operations DROP PARTITION and TRUNCATE PARTITION are optimized by making the index maintenance for metadata only.

Asynchronous global index maintenance for DROP and TRUNCATE is performed by default; however, the UPDATE INDEXES clause is still required for backward compatibility.

The following list summarizes the limitations of asynchronous global index maintenance:

* Only performed on heap tables
* No support for tables with object types
* No support for tables with domain indexes
* Not performed for the user SYS

新特性四种手动维护办法

* Maintenance operations on indexes can be performed with the automatic scheduler job SYS.PMO_DEFERRED_GIDX_MAINT_JOB to clean up all global indexes. This job is scheduled to run at 2:00 A.M. on a daily basis by default. You can run this job at any time using DBMS_SCHEDULER.RUN_JOB if you want to proactively clean up the indexes. You can also modify the job to run with a different schedule based on your specific requirements. However, Oracle recommends that you do not drop the job.

You can also force cleanup of an index needing maintenance using one of the following options:

* DBMS_PART.CLEANUP_GIDX - This PL/SQL procedure gathers the list of global indexes in the system that may require cleanup and runs the operations necessary to restore the indexes to a clean state.

* ALTER INDEX REBUILD [PARTITION] – This SQL statement rebuilds the entire index or index partition as is done in releases previous to Oracle Database 12c Release 1 (12.1). The resulting index (partition) does not contain any stale entries.

* ALTER INDEX [PARTITION] COALESCE CLEANUP – This SQL statement cleans up any orphaned entries in index blocks.

2.测试

（1）创建测试表以及插入数据

# 创建测试表
TEST@czhpdb1 > create table test_part (id number,name varchar2(30))
  2  partition by range(id)
  3  (
  4  partition p1 values less than (1000),
  5  partition p2 values less than (2000),
  6  partition p3 values less than (3000),
  7  partition p4 values less than (4000),
  8  partition p5 values less than (maxvalue)
  9  );

# 插入测试数据
TEST@czhpdb1 > declare
id number;
begin
for i in 1..6000 loop
    id:=i;
    insert into test_part values(id,'nihaoma');
end loop;
commit;
end;
/

TEST@czhpdb1 > select count(*) fr