Oracle 分区维护操作后的不可用索引

团队，

正在通读这份文件。
http://docs.oracle.com/database/122/SQLRF/CREATE-INDEX.htm#GUID-1F89BBC0-825F-4215-AF71-7588E31D8BFE__BABCHJDH
<报价>
当创建索引或索引的某些分区或子分区不可用时，不会为不可用对象分配任何段。不可用的索引或索引分区不消耗数据库中的空间。


报价如何仅适用于本地分区的索引，而不适用于全局分区的索引和非分区的索引。

demo@ORA12C> create table t
  2    partition by range( created_dt )
  3    ( partition p2013 values less than
  4       ( to_date('01-jan-2014','dd-mon-yyyy') ) ,
  5     partition p2014 values less than
  6       ( to_date('01-jan-2015','dd-mon-yyyy') ) ,
  7     partition pmax values less than (maxvalue)         )
  8    as
  9    select a.* ,
 10       to_date('01-Jan-2013','dd-mon-yyyy') +
 11       mod(rownum,1825) created_dt
 12    from all_objects a ;

Table created.

demo@ORA12C>   create index t_nonpart_idx on t(object_id);

Index created.

demo@ORA12C> create index t_globalpart_idx on t(object_id,owner)
  2    global partition by hash(object_id) (
  3       partition p1,
  4       partition p2 ) ;

Index created.

demo@ORA12C> create index t_local_idx on t(object_name) local;

Index created.

demo@ORA12C> column index_name format a20
demo@ORA12C> column partition_name format a10
demo@ORA12C> column segment_name format a20
demo@ORA12C>
demo@ORA12C> select index_name,status
  2   from user_indexes
  3   where index_name = 'T_NONPART_IDX';

INDEX_NAME           STATUS
-------------------- --------
T_NONPART_IDX        VALID

demo@ORA12C>
demo@ORA12C> select index_name,partition_name,status
  2  from user_ind_partitions
  3  where index_name in ('T_GLOBALPART_IDX',
  4    'T_LOCAL_IDX');

INDEX_NAME           PARTITION_ STATUS
-------------------- ---------- --------
T_GLOBALPART_IDX     P1         USABLE
T_GLOBALPART_IDX     P2         USABLE
T_LOCAL_IDX          P2013      USABLE
T_LOCAL_IDX          P2014      USABLE
T_LOCAL_IDX          PMAX       USABLE

demo@ORA12C>
demo@ORA12C> select segment_name,partition_name,extents
  2    from user_segments
  3    where segment_name in ( 'T_NONPART_IDX' ,
  4      'T_GLOBALPART_IDX' , 'T_LOCAL_IDX' )
  5    order by segment_name,partition_name nulls first;

SEGMENT_NAME         PARTITION_    EXTENTS
-------------------- ---------- ----------
T_GLOBALPART_IDX     P1                 23
T_GLOBALPART_IDX     P2                 23
T_LOCAL_IDX          P2013              20
T_LOCAL_IDX          P2014              20
T_LOCAL_IDX          PMAX               23
T_NONPART_IDX                           17

6 rows selected.

demo@ORA12C> alter table t
  2  split partition pmax at
  3  ( to_date('01-jan-2016','dd-mon-yyyy') )
  4  into ( partition p2016, partition pmax);

Table altered.

demo@ORA12C> select index_name,status
  2   from user_indexes
  3   where index_name = 'T_NONPART_IDX';

INDEX_NAME           STATUS
-------------------- --------
T_NONPART_IDX        UNUSABLE

demo@ORA12C>
demo@ORA12C> select index_name,partition_name,status
  2  from user_ind_partitions
  3  where index_name in ('T_GLOBALPART_IDX',
  4    'T_LOCAL_IDX');

INDEX_NAME           PARTITION_ STATUS
-------------------- ---------- --------
T_GLOBALPART_IDX     P1         UNUSABLE
T_GLOBALPART_IDX     P2         UNUSABLE
T_LOCAL_IDX          P2013      USABLE
T_LOCAL_IDX          P2014      USABLE
T_LOCAL_IDX          P2016      UNUSABLE
T_LOCAL_IDX          PMAX       UNUSABLE

6 rows selected.

demo@ORA12C> select segment_name,partition_name,extents
  2    from user_segments
  3    where segment_name in ( 'T_NONPART_IDX' ,
  4      'T_GLOBALPART_IDX' , 'T_LOCAL_IDX' )
  5    order by segment_name,partition_name nulls first;

SEGMENT_NAME         PARTITION_    EXTENTS
-------------------- ---------- ----------
T_GLOBALPART_IDX     P1                 23
T_GLOBALPART_IDX     P2                 23
T_LOCAL_IDX          P2013              20
T_LOCAL_IDX          P2014              20
T_NONPART_IDX                           17

demo@ORA12C>
复制

您能否帮助我理解为什么在全局分区索引和非分区索引的情况下不会释放这些未使用的段？

这里的关键点是当你create它们需要不可用的索引。

脚本创建可用的索引。拆分分区不会重新创建全局索引。所以他们保持他们的范围。其中许多将与不受拆分影响的行有关。

如果在创建索引时添加不可用，则会看到数据库不会创建任何扩展:

create table t
  partition by range( created_dt )
  ( partition p2013 values less than
     ( to_date('01-jan-2014','dd-mon-yyyy') ) ,
   partition p2014 values less than
     ( to_date('01-jan-2015','dd-mon-yyyy') ) ,
   partition pmax values less than (maxvalue)         )
  as
  select a.* ,
     to_date('01-Jan-2013','dd-mon-yyyy') +
     mod(rownum,1825) created_dt
  from all_objects a ;

create index t_nonpart_idx on t(object_id) unusable;

create index t_globalpart_idx on t(object_id,owner)
  global partition by hash(object_id) (
     partition p1,
     partition p2 ) unusable;

create index t_local_idx on t(object_name) local unusable;

select segment_name,partition_name,extents
from user_segments
where segment_name in ( 'T_NONPART_IDX' , 'T_GLOBALPART_IDX' , 'T_LOCAL_IDX' )
order by segment_name,partition_name nulls first;

no rows selected

alter table t
split partition pmax at
( to_date('01-jan-2016','dd-mon-yyyy') )
into ( partition p2016, partition pmax);

select segment_name,partition_name,extents
from user_segments
where segment_name in ( 'T_NONPART_IDX' , 'T_GLOBALPART_IDX' , 'T_LOCAL_IDX' )
order by segment_name,partition_name nulls first;

no rows selected
复制

索引是UNUSABLE,不是UNUSED。

PS-LiveSQL链接应该指向your script。Not its homepage!