Renaming partitions in ORACLE

Question

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?

Solution 1

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

Solution 2

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