Geek Answers Handbook

Renaming partitions in ORACLE

if we use the ALTER TABLE RENAME PARTITION statement to rename existing partitions in an Oracle table, do we need to recreate the local partitioned indexes with the new name names?

+4
source share
2 answers

No, renaming partitions does not affect locally partitioned indexes. You can easily check this:

--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 | --------------------------------------------------------------------------------------------------

Index is still in use, after renaming a section

+7
source

Renaming partitions does not affect locally partitioned indexes, so you won’t need to restore indexes.

+2
source

More articles:


All Articles