sql - 在 ORACLE 中重命名分区

Question

如果我们使用 ALTER TABLE RENAME PARTITION 语句重命名 Oracle 表中的现有分区，我们是否需要使用新更改的分区名称重新创建本地分区索引？

Answer 1

不，重命名分区不会影响本地分区索引。您可以轻松测试:

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

重命名分区后索引仍在使用