LoginSignup
4
2

More than 5 years have passed since last update.

@mkyz08

パーティションを移動するとインデックスが無効化されるお話（Oracle）

Last updated at Posted at 2018-12-23

はじめに

Oracleでパーティションを移動（MOVE)させるとインデックスが無効になります。
これはパーティションの移動によりROWIDが変更になるからで、パーティションだけでなく、テーブルやサブパーティションも同様です。
今更書くほどのことでもない当たり前なことですが、この記事では自分の復習のため実際にその動きを確かめています。

なお、12cR1では、オンラインでパーティションを移動することもでき、この場合はインデックスが無効にはなりません。

パーティションの移動を試してみる

わけあって11gが手元になかったので、Oracle12cR2で試しています。
12cR1から使えるオンラインでのパーティション移動は使用せずに試してみます。

テスト用のTEST_INTERVALテーブルというインターバルパーティションのテーブルを作成。
id,timeカラムに対してIDX_TEST_INTERVAL1というインデックスを作成。

SQL> create table TEST_INTERVAL(id number not null, time timestamp, msg1 varchar2(100), msg2 varchar2(100))
  partition by range(time) interval(NUMTODSINTERVAL(1, 'day'))
    (partition P1 values less than (to_date('2018/01/01', 'YYYY/MM/DD')));

SQL> insert into TEST_INTERVAL values(1, to_date('2018-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'test1', 'test2');
SQL> commit;

SQL> create unique index IDX_TEST_INTERVAL1 on TEST_INTERVAL(id, time) local;

-- 統計情報も最新化
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'HR',OPTIONS => 'GATHER AUTO');
※ただし、インデックスは作成時に統計情報が取得されています。

さっそくパーティションを移動します。
以下の例ではmoveしているだけですが、よく利用するオプションとしては別の表領域への移動があります。
例えば、古い日付のアクセスしなくなったパーティションを別ストレージ（別表領域）に移す場合に使用することがあります。

-- パーティション名を確認する。
SQL> select table_name, partition_name, high_value from user_tab_partitions;
-- パーティションを移動する。
SQL> alter table TEST_INTERVAL move partition SYS_P934;

表が変更されました。

インデックスを確認すると、パーティションについているインデックスが無効（ステータスが UNUSABLE）になっていることが確認できます。

set linesize 300
column index_name format a20
column partition_name format a15
column high_value format a32
column status format a15
SQL> select index_name, partition_name, high_value, status from USER_IND_PARTITIONS where index_name = 'IDX_TEST_INTERVAL1';

INDEX_NAME           PARTITION_NAME  HIGH_VALUE                       STATUS
-------------------- --------------- -------------------------------- ---------------
IDX_TEST_INTERVAL1   P1              TIMESTAMP' 2018-01-01 00:00:00'  USABLE
IDX_TEST_INTERVAL1   SYS_P934        TIMESTAMP' 2018-12-24 00:00:00'  UNUSABLE

このとき統計情報も失効しているので確認してみます。


-- オプティマイザ統計確認
SET PAGESIZE 100;
column OWNER format a10
column TABLE_NAME format a15
column LAST_ANALYZED format a20
column SUBPARTITION_NAME format a15
column NUM_ROWS format B99999999
column STALE_STATS format a15

SQL> SELECT OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS
  FROM DBA_TAB_STATISTICS
 WHERE OWNER = 'HR' AND TABLE_NAME = 'TEST_INTERVAL'
 ORDER BY OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME;

OWNER      TABLE_NAME      PARTITION_NAME  SUBPARTITION_NA LAST_ANALYZED         NUM_ROWS STALE_STATS
---------- --------------- --------------- --------------- -------------------- --------- ---------------
HR         TEST_INTERVAL   P1                              18-12-24                       NO
HR         TEST_INTERVAL   SYS_P934                        18-12-24                     1 YES
HR         TEST_INTERVAL                                   18-12-24                     1 YES



SQL> SELECT OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS
  FROM DBA_IND_STATISTICS
 WHERE OWNER = 'HR' AND TABLE_NAME = 'TEST_INTERVAL'
 ORDER BY OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME;

OWNER      TABLE_NAME      INDEX_NAME           PARTITION_NAME  SUBPARTITION_NA LAST_ANALYZED         NUM_ROWS STALE_STATS
---------- --------------- -------------------- --------------- --------------- -------------------- --------- ---------------
HR         TEST_INTERVAL   IDX_TEST_INTERVAL1   P1                              18-12-24                       NO
HR         TEST_INTERVAL   IDX_TEST_INTERVAL1   SYS_P934                        18-12-24                     1 YES
HR         TEST_INTERVAL   IDX_TEST_INTERVAL1                                   18-12-24                     1 YES

パーティション、ローカルインデックスともにSTALE_STATS='YES'となっており、統計情報が失効していることが分かります。

次にインデックスが無効になっている状態で、DMLの実行結果を確かめてみます。

SQL> insert into TEST_INTERVAL values(2, to_date('2018-12-23 01:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'test1', 'test2');
*
行1でエラーが発生しました。:
ORA-01502: 索引'HR.IDX_TEST_INTERVAL1'またはそのパーティションが使用不可の状態です。

SQL> update TEST_INTERVAL set msg1 = 'test123' where id = 1;

1行が更新されました。

SQL> update TEST_INTERVAL set time = to_date('2018-12-23 01:00:00', 'YYYY-MM-DD HH24:MI:SS') where id = 1;
update TEST_INTERVAL set time = to_date('2018-12-23 01:00:00', 'YYYY-MM-DD HH24:MI:SS') where id = 1
*
行1でエラーが発生しました。:
ORA-01502: 索引'HR.IDX_TEST_INTERVAL1'またはそのパーティションが使用不可の状態です。


SQL> delete from TEST_INTERVAL where id = 1;
delete from TEST_INTERVAL
*
行1でエラーが発生しました。:
ORA-01502: 索引'HR.IDX_TEST_INTERVAL1'またはそのパーティションが使用不可の状態です。

SQL> select * from TEST_INTERVAL where id = 1;

INSERT、DELETE、UPDATE（インデックス含む）は失敗、SELECTとインデックスのあるカラムを変更しないUPDATEは成功しました。
なお、SELECTの実行計画を確認したところ、「TABLE ACCESS FULL」となりインデックスは使用されていない（当然ですが）

実行計画
----------------------------------------------------------
Plan hash value: 1840575326

-----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |     1 |    28 |     4   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|               |     1 |    28 |     4   (0)| 00:00:01 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | TEST_INTERVAL |     1 |    28 |     4   (0)| 00:00:01 |     1 |1048575|
-----------------------------------------------------------------------------------------------------

インデックスを有効にするためにインデックスをリビルドします。また、統計情報も失効しているので再収集する必要があります。

SQL> alter index IDX_TEST_INTERVAL1 rebuild partition SYS_P934;

索引が変更されました。

インデックスを確認するとUSABLEになり有効になっていることが分かります。

SQL> select index_name, partition_name, high_value, status from USER_IND_PARTITIONS where index_name = 'IDX_TEST_INTERVAL1';

INDEX_NAME           PARTITION_NAME  HIGH_VALUE                       STATUS
-------------------- --------------- -------------------------------- ---------------
IDX_TEST_INTERVAL1   P1              TIMESTAMP' 2018-01-01 00:00:00'  USABLE
IDX_TEST_INTERVAL1   SYS_P934        TIMESTAMP' 2018-12-24 00:00:00'  USABLE

統計情報も確認します。CREATE INDEXでは自動で統計情報が取得されるのですが、ALTER INDEXでは統計情報は取得されない（STALE_STATS='YES'）？ようです。

SQL> SELECT OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS
  FROM DBA_IND_STATISTICS
 WHERE OWNER = 'HR' AND TABLE_NAME = 'TEST_INTERVAL'
 ORDER BY OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME;

OWNER      TABLE_NAME      INDEX_NAME           PARTITION_NAME  SUBPARTITION_NA LAST_ANALYZED         NUM_ROWS STALE_STATS
---------- --------------- -------------------- --------------- --------------- -------------------- --------- ---------------
HR         TEST_INTERVAL   IDX_TEST_INTERVAL1   P1                              18-12-24                       NO
HR         TEST_INTERVAL   IDX_TEST_INTERVAL1   SYS_P934                        18-12-24                     1 YES
HR         TEST_INTERVAL   IDX_TEST_INTERVAL1                                   18-12-24                     1 YES

統計情報を取得（DBMS_STATS.GATHER_SCHEMA_STATS）して、STALE_STATS='NO'となっていることを確認できました。

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'HR',OPTIONS => 'GATHER AUTO');

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, NUM_ROWS, STALE_STATS
  FROM DBA_IND_STATISTICS
 WHERE OWNER = 'HR' AND TABLE_NAME = 'TEST_INTERVAL'
 ORDER BY OWNER, TABLE_NAME, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME; 

OWNER      TABLE_NAME      INDEX_NAME           PARTITION_NAME  SUBPARTITION_NA LAST_ANALYZED         NUM_ROWS STALE_STATS
---------- --------------- -------------------- --------------- --------------- -------------------- --------- ---------------
HR         TEST_INTERVAL   IDX_TEST_INTERVAL1   P1                              18-12-24                       NO
HR         TEST_INTERVAL   IDX_TEST_INTERVAL1   SYS_P934                        18-12-24                     1 NO
HR         TEST_INTERVAL   IDX_TEST_INTERVAL1                                   18-12-24                     1 NO

パーティションの移動時は、オンラインで実行することもできます。その場合はDMLが実行できインデックスの再構築も不要となります。

SQL> alter table TEST_INTERVAL move partition SYS_P934 online;

表が変更されました。

インデックスを確認してみると、STATUS=USABLEで無効になっていないことが確認できます。統計情報も該当パーティションは無効化していません。

SQL> select index_name, partition_name, high_value, status from USER_IND_PARTITIONS where index_name = 'IDX_TEST_INTERVAL1';

INDEX_NAME           PARTITION_NAME  HIGH_VALUE                       STATUS
-------------------- --------------- -------------------------------- ---------------
IDX_TEST_INTERVAL1   P1              TIMESTAMP' 2018-01-01 00:00:00'  USABLE
IDX_TEST_INTERVAL1   SYS_P885        TIMESTAMP' 2018-12-24 00:00:00'  USABLE

ONLINE句は次の制限事項があると書かれています。

Oracle Database 12cリリース2 SQL Language Reference 12.3 ALTER TABLE

  • ONLINE句は、SYSが所有する表に対しては指定できません。
  • ONLINE句は、索引構成表には指定できません。
  • ONLINE句は、オブジェクト・タイプを格納するヒープ構成表や、ビットマップ結合索引またはドメイン索引が定義されているヒープ構成表には指定できません。
  • パラレルDMLおよびダイレクト・パスINSERT操作は、表に対する排他的ロックを必要とします。したがって、これらの操作は、競合するロックにより、実行中のオンライン・パーティションMOVEと同時にはサポートされません。

なお、ALTER TABLE文にUPDATE INDEXESを指定すると、インデックスが更新されます。
ただし、以下の記事にあるように12cであればONLINE句を使用しましょう。

Oracle Database エンジニアを不幸にしないための「べからず集」
　　「Alter table move partitionでupdate indexes句を使うべからず」

まとめ

  • 11gではパーティション移動するとインデックスが無効化されるのでリビルドすること。統計情報も再収集する。
  • 12cR1以降ならパーティション移動はオンラインで実行する。UPDATE INDEXESは使わない。オンラインで実行する必要がなければ、後でインデックスをリビルドする。

参考

Oracle Database 12cリリース2 SQL Language Reference 10.15 ALTER INDEX
https://docs.oracle.com/cd/E82638_01/sqlrf/ALTER-INDEX.html#GUID-D8F648E7-8C07-4C89-BB71-862512536558

Oracle Database 12cリリース2 SQL Language Reference 12.3 ALTER TABLE
https://docs.oracle.com/cd/E82638_01/sqlrf/ALTER-TABLE.html#GUID-552E7373-BF93-477D-9DA3-B2C9386F2877

ALTER INDEX REBUILD による索引の再構築 by SHIFT the Oracle
https://www.shift-the-oracle.com/alter-index/alter-index-rebuild.html

津島博士のパフォーマンス講座　第43回　パーティションについて（３）
https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-43-2405698-ja.html

Oracle Database エンジニアを不幸にしないための「べからず集」
https://www.oracle.com/technetwork/jp/ondemand/database/db-new/db-tech-night-3508291-ja.html#anc_16

4
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
2