Oracle12c INDEXがUNUSABLEになるとEXTENT,SEGMENTは削除される

概要

ALTER TABLEなどでINDEXがUNUSABLEとなった場合に、EXTENT、SEGMENTは削除されるという事象の検証（というかメモ）です。 OracleMasterの教科書に載っているような話らしいですね。資格は持っていないので知らないです。

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CentOS Linux release 7.3.1611 (Core) 

検証

テーブル、インデックスの用意

検証用のテーブルとインデックス、データを用意します。

^ テーブル作成

SQL> create table TAB_UNUSABLE ( pid number(1) );

Table created.

Elapsed: 00:00:00.05
SQL>

^ インデックス作成

SQL> create index IDX1_TAB_UNUSABLE ON TAB_UNUSABLE ( pid );

Index created.

Elapsed: 00:00:00.07
SQL>

^ データ登録

SQL> insert into TAB_UNUSABLE values (1);

1 row created.

Elapsed: 00:00:00.06
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>

データ0件ですとエクステントも確保されませんのでね。

事前確認

まずは、テーブル、インデックスを作成しただけの状態で、関連ディクショナリを確認します。

^ ディクショナリ確認

SQL> ed
Wrote file afiedt.buf

  1  select segment_name
  2       , segment_type
  3       , bytes
  4       , blocks
  5    from user_extents
  6*  where segment_name = 'IDX1_TAB_UNUSABLE'
SQL> /

SEGMENT_NAME         SEGMENT_TYPE          BYTES     BLOCKS
-------------------- ---------------- ---------- ----------
IDX1_TAB_UNUSABLE    INDEX                 65536          8

1 row selected.

Elapsed: 00:00:00.03
SQL>

１レコード分のBLOCKが確保されています。

SQL> ed
Wrote file afiedt.buf

  1  select segment_name
  2       , segment_type
  3       , bytes
  4       , blocks
  5       , extents
  6    from user_segments
  7*  where segment_name = 'IDX1_TAB_UNUSABLE'
SQL> /

SEGMENT_NAME         SEGMENT_TYPE          BYTES     BLOCKS    EXTENTS
-------------------- ---------------- ---------- ---------- ----------
IDX1_TAB_UNUSABLE    INDEX                 65536          8          1

1 row selected.

Elapsed: 00:00:00.08
SQL>

当然ですが、segment表も同じくです。

SQL> ed
Wrote file afiedt.buf

  1  select object_name
  2       , object_id
  3       , object_type
  4       , created
  5       , last_ddl_time
  6       , status
  7    from user_objects
  8*  where object_name = 'IDX1_TAB_UNUSABLE'
SQL> /

OBJECT_NAME                      OBJECT_ID OBJECT_TYPE      CREATED             LAST_DDL_TIME       STATUS
------------------------------ ----------- ---------------- ------------------- ------------------- ----------
IDX1_TAB_UNUSABLE                    73429 INDEX            2017/10/05 18:42:45 2017/10/05 18:42:45 VALID

1 row selected.

Elapsed: 00:00:00.04
SQL>

SQL> ed
Wrote file afiedt.buf

  1  select index_name
  2       , index_type
  3       , compression
  4       , status
  5       , num_rows
  6       , last_analyzed
  7       , visibility
  8    from user_indexes
  9*  where index_name = 'IDX1_TAB_UNUSABLE'
SQL> /

INDEX_NAME           INDEX_TYPE   COMPRESSION          STATUS       NUM_ROWS LAST_ANALYZED       VISIBILITY
-------------------- ------------ -------------------- ---------- ---------- ------------------- ----------
IDX1_TAB_UNUSABLE    NORMAL       DISABLED             VALID               0 2017/10/05 18:42:45 VISIBLE

1 row selected.

Elapsed: 00:00:00.00
SQL>

ここは説明は不要ですね。

ALTER INDEX xxx UNUSABLE

次に、インデックスをUNUSABLEに変更してみます。

SQL> alter index IDX1_TAB_UNUSABLE unusable;

Index altered.

Elapsed: 00:00:00.06
SQL>

^ ディクショナリ確認

SQL> ed
Wrote file afiedt.buf

  1  select object_name
  2       , object_id
  3       , object_type
  4       , created
  5       , last_ddl_time
  6       , status
  7    from user_objects
  8*  where object_name = 'IDX1_TAB_UNUSABLE'
SQL> /

OBJECT_NAME                      OBJECT_ID OBJECT_TYPE      CREATED             LAST_DDL_TIME       STATUS
------------------------------ ----------- ---------------- ------------------- ------------------- ----------
IDX1_TAB_UNUSABLE                    73429 INDEX            2017/10/05 18:42:45 2017/10/05 18:47:22 VALID

1 row selected.

Elapsed: 00:00:00.00
SQL>

SQL> ed
Wrote file afiedt.buf

  1  select index_name
  2       , index_type
  3       , compression
  4       , status
  5       , num_rows
  6       , last_analyzed
  7       , visibility
  8    from user_indexes
  9*  where index_name = 'IDX1_TAB_UNUSABLE'
SQL> /

INDEX_NAME           INDEX_TYPE   COMPRESSION          STATUS       NUM_ROWS LAST_ANALYZED       VISIBILITY
-------------------- ------------ -------------------- ---------- ---------- ------------------- ----------
IDX1_TAB_UNUSABLE    NORMAL       DISABLED             UNUSABLE            0 2017/10/05 18:42:45 VISIBLE

1 row selected.

Elapsed: 00:00:00.04
SQL>

user_indexes.statusはUNUSABLEとなりました。

SQL> ed
Wrote file afiedt.buf

  1  select segment_name
  2       , segment_type
  3       , bytes
  4       , blocks
  5    from user_extents
  6*  where segment_name = 'IDX1_TAB_UNUSABLE'
SQL> /

no rows selected

Elapsed: 00:00:00.01
SQL>

SQL> ed
Wrote file afiedt.buf

  1  select segment_name
  2       , segment_type
  3       , bytes
  4       , blocks
  5       , extents
  6    from user_segments
  7*  where segment_name = 'IDX1_TAB_UNUSABLE'
SQL> /

no rows selected

Elapsed: 00:00:00.01
SQL>

すると、user_extents,user_segmentsからは削除されました。

ALTER INDEX xxx REBUILD

UNUSABLEとなった、インデックスをREBUILDします。

SQL> alter index IDX1_TAB_UNUSABLE rebuild;

Index altered.

Elapsed: 00:00:00.08
SQL>

^ ディクショナリ確認

SQL> ed
Wrote file afiedt.buf

  1  select object_name
  2       , object_id
  3       , object_type
  4       , created
  5       , last_ddl_time
  6       , status
  7    from user_objects
  8*  where object_name = 'IDX1_TAB_UNUSABLE'
SQL> /

OBJECT_NAME                      OBJECT_ID OBJECT_TYPE      CREATED             LAST_DDL_TIME       STATUS
------------------------------ ----------- ---------------- ------------------- ------------------- ----------
IDX1_TAB_UNUSABLE                    73429 INDEX            2017/10/05 18:42:45 2017/10/05 18:49:46 VALID

1 row selected.

Elapsed: 00:00:00.03
SQL>

SQL> ed
Wrote file afiedt.buf

  1  select index_name
  2       , index_type
  3       , compression
  4       , status
  5       , num_rows
  6       , last_analyzed
  7       , visibility
  8    from user_indexes
  9*  where index_name = 'IDX1_TAB_UNUSABLE'
SQL> /

INDEX_NAME           INDEX_TYPE   COMPRESSION          STATUS       NUM_ROWS LAST_ANALYZED       VISIBILITY
-------------------- ------------ -------------------- ---------- ---------- ------------------- ----------
IDX1_TAB_UNUSABLE    NORMAL       DISABLED             VALID               1 2017/10/05 18:49:46 VISIBLE

1 row selected.

Elapsed: 00:00:00.01
SQL>

インデックスのSTATUSはVALIDに戻りました。

SQL> ed
Wrote file afiedt.buf

  1  select segment_name
  2       , segment_type
  3       , bytes
  4       , blocks
  5    from user_extents
  6*  where segment_name = 'IDX1_TAB_UNUSABLE'
SQL> /

SEGMENT_NAME         SEGMENT_TYPE          BYTES     BLOCKS
-------------------- ---------------- ---------- ----------
IDX1_TAB_UNUSABLE    INDEX                 65536          8

1 row selected.

Elapsed: 00:00:00.02
SQL>

SQL> ed
Wrote file afiedt.buf

  1  select segment_name
  2       , segment_type
  3       , bytes
  4       , blocks
  5       , extents
  6    from user_segments
  7*  where segment_name = 'IDX1_TAB_UNUSABLE'
SQL> /

SEGMENT_NAME         SEGMENT_TYPE          BYTES     BLOCKS    EXTENTS
-------------------- ---------------- ---------- ---------- ----------
IDX1_TAB_UNUSABLE    INDEX                 65536          8          1

1 row selected.

Elapsed: 00:00:00.02
SQL>

extent,segmentも復活しています。

ALTER TABLE xxx MOVE

テーブル圧縮を実行してみます。

SQL> alter table TAB_UNUSABLE move compress for oltp;

Table altered.

Elapsed: 00:00:00.09
SQL>

^ ディクショナリ確認

SQL> ed
Wrote file afiedt.buf

  1  select object_name
  2       , object_id
  3       , object_type
  4       , created
  5       , last_ddl_time
  6       , status
  7    from user_objects
  8*  where object_name = 'IDX1_TAB_UNUSABLE'
SQL> /

OBJECT_NAME                      OBJECT_ID OBJECT_TYPE      CREATED             LAST_DDL_TIME       STATUS
------------------------------ ----------- ---------------- ------------------- ------------------- ----------
IDX1_TAB_UNUSABLE                    73429 INDEX            2017/10/05 18:42:45 2017/10/05 18:49:46 VALID

1 row selected.

Elapsed: 00:00:00.01
SQL>

SQL> ed
Wrote file afiedt.buf

  1  select index_name
  2       , index_type
  3       , compression
  4       , status
  5       , num_rows
  6       , last_analyzed
  7       , visibility
  8    from user_indexes
  9*  where index_name = 'IDX1_TAB_UNUSABLE'
SQL> /

INDEX_NAME           INDEX_TYPE   COMPRESSION          STATUS       NUM_ROWS LAST_ANALYZED       VISIBILITY
-------------------- ------------ -------------------- ---------- ---------- ------------------- ----------
IDX1_TAB_UNUSABLE    NORMAL       DISABLED             UNUSABLE            1 2017/10/05 18:49:46 VISIBLE

1 row selected.

Elapsed: 00:00:00.00
SQL>

SQL> ed
Wrote file afiedt.buf

  1  select segment_name
  2       , segment_type
  3       , bytes
  4       , blocks
  5    from user_extents
  6*  where segment_name = 'IDX1_TAB_UNUSABLE'
SQL> /

no rows selected

Elapsed: 00:00:00.23
SQL>

SQL> ed
Wrote file afiedt.buf

  1  select segment_name
  2       , segment_type
  3       , bytes
  4       , blocks
  5       , extents
  6    from user_segments
  7*  where segment_name = 'IDX1_TAB_UNUSABLE'
SQL> /

no rows selected

Elapsed: 00:00:00.02
SQL>

テーブルをMOVEすると、インデックスはUNUSABLEになるので、当然と言えば当然なのですが、
やはりextent,segmentからは削除されました。