TRUNCATING A PARTITION WITH INDEXES [ID 292575.1]

最新推荐文章于 2024-07-07 13:00:43 发布

cuhongyang4157

最新推荐文章于 2024-07-07 13:00:43 发布

阅读量70

点赞数

文章标签：数据库

Applies To

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.1.0.3
Information in this document applies to any platform.

Goal

You need to truncate a partition of a table which has a index.
Will the index also be altered or dropped along with the partition?

Fix

You can use Use the ALTER TABLE ... TRUNCATE PARTITION statement to remove all rows from a
table partition.

But there is no command to truncate the index partition.

However, if there are local indexes
defined for the table, the ALTER TABLE TRUNCATE PARTITION statement truncates
the matching partition in each local index.

For Global indexes
-------------------
If there are global indexes,specify UPDATE GLOBAL INDEXES.

sql>alter table .. truncate partition .. update global indexes ;

Otherwise all partitions of partitioned global indexes
are marked UNUSABLE in DBA_IND_PARTITIONS
and DBA_INDEXES show the index status as N/A.