SQL 在 ORACLE 中重命名分区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1979163/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Renaming partitions in ORACLE
提问by Venkataramesh Kommoju
if we use the ALTER TABLE RENAME PARTITION statement to rename the existing partitions in an Oracle table, do we need to recreate the local partitioned indexes with newly changed partition names?
如果我们使用 ALTER TABLE RENAME PARTITION 语句重命名 Oracle 表中的现有分区，我们是否需要使用新更改的分区名称重新创建本地分区索引？
回答by Thomas Aregger
No, renaming partitions does not affect locally partitioned indexes. You can easily test that:
不，重命名分区不会影响本地分区索引。您可以轻松测试：
--create table
CREATE TABLE t (
c1 DATE,
c2 NUMBER(3))
partition by range (c1) (
partition t_nov values less than (
to_date('01-12-2009 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
),
partition t_dec values less than (
to_date('01-01-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
)
)
/
--create index
create index idx_t on t (c1) local (partition t_nov, partition t_dec);
--insert some rows
insert into t values (sysdate, 1);
insert into t values (sysdate, 1);
insert into t values (sysdate, 1);
insert into t values (sysdate, 1);
insert into t values (sysdate, 1);
insert into t values (sysdate, 1);
insert into t values (sysdate, 1);
insert into t values (sysdate, 1);
insert into t values (sysdate, 1);
insert into t values (sysdate, 1);
--gather statistics
exec dbms_stats.gather_table_stats('SYSTEM', 'T');
--set autotrace on, to determine that index is used
set autotrace on
--select indexed column
select c1 from t where c1 < sysdate+1;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 88 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 11 | 88 | 1 (0)| 00:00:01 | 1 | KEY |
|* 2 | INDEX RANGE SCAN | IDX_T | 11 | 88 | 1 (0)| 00:00:01 | 1 | KEY |
--------------------------------------------------------------------------------------------------
--rename partition
alter table t rename partition t_dec to t_december;
Table altered.
select c1 from t where c1 < sysdate+1;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 88 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 11 | 88 | 1 (0)| 00:00:01 | 1 | KEY |
|* 2 | INDEX RANGE SCAN | IDX_T | 11 | 88 | 1 (0)| 00:00:01 | 1 | KEY |
--------------------------------------------------------------------------------------------------
The index is still used, after renaming the partition
重命名分区后索引仍在使用
回答by Bob Jarvis - Reinstate Monica
Renaming partitions does not affect locally partitioned indexes, so you will not need to rebuild your indexes.
重命名分区不会影响本地分区索引，因此您无需重建索引。
