Renaming partitions in ORACLE

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?

---

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

---

Renaming partitions does not affect locally partitioned indexes, so you will not need to rebuild your indexes.