NOLOGGINGでATOMIC REFRESHしたいならPARTITIONのEXCHANGEでどうだろう？ (Oracle)

###はじめに

例えば、こんな状況。

定期的に完全リフレッシュをするマテリアライズド・ビューがある。REDOログを生成したくないので、NOLOGGINGにしてあるが、ATOMIC REFRESH有効だと意に反して大きなログが吐き出される。クエリを中断させないリフレッシュでREDOログを生成しない方法はないものか？


まぁ、思考実験みたいなものかな。

- ソースデータが大きく変わるのでMVは完全リフレッシュとしよう。
- まぁMVデータはいつでも再生成可能だし、REDOログっていらないよね？
- じゃあ NOLOGGING にしてみよう。パフォーマンスも向上するし。
- とはいえNOLOGGING が有効になるのはダイレクトパスインサートのみ。
- つまりは CREATE TABLE/INDEX または INSERT /*+ APPEND */ のこと。
- たしかにREDOは作られなくなる。ATOMIC REFRESH無効であれば、、、
- でもATOMIC REFRESH無効だと、実行中のクエリがエラーになるじゃないか。
- ATOMIC REFRESHは有効にしよう。でもそうするとREDOが生成されてしまう。
- TRUNCATE & INSERT APPENDじゃなくてDELETE & INSERTだもんな。
- インデックスメンテナンスを含めると結構なREDOログだな、これ。
- う～ん、クエリを中断させないリフレッシュでREDOログも抑えたいのだが、、、。

さてどうしたものか。

これもうマテリアライズド・ビューやめて、ダイレクトパスインサートでテーブルとインデックスを作った後、パーティションテーブルのパーティションと交換すればいんじゃね？って考えた。

で、やってみた。

>以下12.2でテスト。10gでも特に問題ないはず。

###アーカイブログ設定の確認

まずは、アーカイブログが有効でかつ強制ロギングが無効になってることを確認する。アーカイブログ無効だと、テーブルのロギング設定に関わらず、ダイレクトパスインサート時に自動的にREDOログ生成をスキップするから、テストとしてはふさわしくないだけ。無効でも特に問題はない。強制データベースロギングは今回の目的としては論外。

```sql:アーカイブの設定確認
SELECT log_mode,
       force_logging
FROM   v$database; 

LOG_MODE     FORCE_LOGGING
------------ ---------------------------------------
ARCHIVELOG   NO
```

###REDO生成量確認スクリプト

生成REDOサイズの確認にはv$mystatを使う。定義変数に保存したセッションのREDO生成値からの差分を出す。ついでにlogin.sqlに定義変数の初期設定を追加しておく。やっとかないとスクリプトの最初の実行で値を聞いてくるから鬱陶しい。

```sql:login.sql
define _REDO_SAVED=0
```

```sql:myredo.sql
col session_redo new_value _REDO_SAVED

SELECT name,
       value - NVL('&_REDO_SAVED', 0) redo_bytes,
       value                          session_redo
FROM   v$statname n,
       v$mystat s
WHERE  s.statistic# = n.statistic#
       AND name = 'redo size'; 
```

###テストデータ作成

いつものテストデータ。ダミー文字列を含む。

```sql:テストデータ作成
CREATE TABLE test_src
  (
     id NUMBER,
     s  VARCHAR2(500)
  );

INSERT INTO test_src
SELECT LEVEL                 id,
       RPAD(LEVEL, 500, 'X') s
FROM   dual
CONNECT BY LEVEL <= 100000; 

COMMIT; 

SELECT COUNT(*) FROM test_src; 

        COUNT(*)
----------------
         100,000
```

###マテリアライズド・ビュー

事前準備ができたので、まずはNOLOGGINGのマテリアライズド・ビューを作ってリフレッシュ時のREDOログ生成量を確認してみる。ついでにインデックスも作っておく。

```sql:マテリアライズド・ビューの作成
CREATE materialized VIEW test_nolog_mv
nologging
AS SELECT *
   FROM   test_src;

CREATE INDEX test_nolog_mv_n1
  ON test_nolog_mv(id, s)
nologging; 
```

アトミック有効でリフレッシュするとNOLOGGINGが効かずそこそこのREDOが生成される。さらにインデックス有りだとREDO生成量が跳ね上がる。インデックスメンテナンスって思ってた以上に高価なんだね。

```sql:アトミックリフレッシュ
exec dbms_mview.refresh('TEST_NOLOG_MV', 'C', atomic_refresh=>TRUE) 

インデックス無し
------------------------- ----------------
redo size                      136,334,416

インデックス有り
Elapsed: 00:00:21.74
------------------------- ----------------
redo size                      599,895,696

```

アトミックを無効にすると、TRUNCATEしてからダイレクトパスインサートするなのでほとんどREDOが作られない。リフレッシュに必要な時間も相当短くなってる。ただパフォーマンスについては単純にTRUNCATEの恩恵だな。

```sql:TRANCATEリフレッシュ
exec dbms_mview.refresh('TEST_NOLOG_MV', 'C', atomic_refresh=>FALSE) 

インデックス無し
NAME                            REDO_BYTES
------------------------- ----------------
redo size                          305,260

インデックス有り
Elapsed: 00:00:04.57
------------------------- ----------------
redo size                          748,236
```

じゃあ、もうアトミック無効でいいじゃん、ってなりそうだけど、そうでもない。リフレッシュでテーブルをTRUNCATEするから、実行中のクエリがエラーになってしまう。これは避けたい。

```sql:TRUCATEリフレッシュでクエリエラー
SELECT Count(*)
FROM   test_nolog_mv mv1,
       test_nolog_mv mv2
WHERE  mv2.id <= 100
       AND mv1.s LIKE '%' || mv2.id || '%'; 

----------
別セッションでTRUNCATEリフレッシュ

exec dbms_mview.refresh('TEST_NOLOG_MV', 'C', atomic_refresh=>FALSE) 
----------

ERROR at line 1:
ORA-08103: object no longer exists
```

エラーメッセージが紛らわしいのだけど、テーブルをDROPしてるというわけではなくDDLの結果アクセス不能ってことらしい。

###パーティションテーブル

そんなわけで、マテリアライズド・ビューを諦めて、パーティションテーブルで代替してみる。テーブルにパーティションを一つだけつくり、そこにすべてのデータが入るようにする。インデックスは、ローカルにしておく。

```sql:パーティションテーブルの作成
CREATE TABLE test_nolog_par
  (
     id NUMBER,
     s  VARCHAR2(500)
  ) partition BY RANGE(id) (PARTITION pt VALUES less than (100000000) nologging)
;

CREATE INDEX test_nolog_par_n1
  ON test_nolog_par(id, s)
local nologging;
```

リフレシュの代わりに、このパーティションを新規テーブルと交換するというわけだ。まぁ、パーティションEXCHANGEは、バルクデータの投入なんかで使われてるけどその応用。

ここで更新データの入った新しいテーブルとインデックスを作る。どちらもNOLOGGINGでのダイレクトパスインサートなのでREDOの生成は極めて小さい。

```sql:交換用テーブルの作成
CREATE TABLE test_nolog
nologging AS
  SELECT * FROM test_src; 

------------------------- ----------------
redo size                          189,316

CREATE INDEX test_nolog_n1
  ON test_nolog(id, s)
nologging; 

------------------------- ----------------
redo size                          313,720
```

そして、インデックスごとテーブルとパーティションを交換。これにて完了。REDOはほぼ無し。

```sql:パーティションの交換
SELECT COUNT(*) FROM test_nolog_par; 
----------------
               0

ALTER TABLE test_nolog_par exchange PARTITION pt WITH TABLE test_nolog INCLUDING INDEXES;

------------------------- ----------------
redo size                           13,680


SELECT COUNT(*) FROM test_nolog_par; 
----------------
         100,000
```

交換したら実行中のクエリはどうなるんだ？って疑問だけれども、引き続き元のデータを参照し続ける。交換前にパーティションテーブルを参照中だったクエリは、交換後は元データをもつ方のテーブルを参照する。なのでエラーにはならない。逆に言うと交換後に元データを持つ方のテーブルをドロップすれば、即エラーとなる。つまり、不要になったテーブルはある程度余裕を持ってから削除しましょう、ってこと。

```sql:意図的にクエリエラーを起す

SELECT COUNT(*)
FROM   test_nolog_par p1,
       test_nolog_par p2
WHERE  p2.id <= 100
       AND p1.s LIKE '%' || p2.id || '%'; 

--------
別セッションでパーティションを交換したあと元テーブルをドロップ

ALTER TABLE test_nolog_par exchange PARTITION pt WITH TABLE test_nolog INCLUDING INDEXES;
DROP TABLE test_nolog;
--------

ERROR at line 1:
ORA-08103: object no longer exists
```

あとは、データ更新時に同じ様に、テーブルとインデックスの作成、パーティション交換、テーブルドロップを繰り返せばよいわけだ。テーブルドロップのタイミングは環境次第。

もしかして、これ単純にテーブルの名前変更でも同じことでできるんじゃね？ってふとよぎるかもれしれないけれども、そうするとテーブルがなくなる一瞬が存在するので、クエリ側でエラーになる可能性がやっぱりある。高速でクエリを繰り返している場合特にね。

```sql:テーブルリネームでトライ
CREATE TABLE test_tbl_new AS
  SELECT *
  FROM   test_src;

DECLARE
    n NUMBER;
BEGIN
    WHILE TRUE LOOP
        SELECT COUNT(*)
        INTO   n
        FROM   test_nolog;
    END LOOP;
END; 
/

----------
別セッションで、以下をスクリプトとして実行。

ALTER TABLE test_tbl RENAME TO test_tbl_old;
ALTER TABLE test_tbl_new RENAME TO test_tbl; 
----------

ORA-00942: table or view does not exist
```

対して、パーティションテーブルだとオブジェクト自体はなくならないので、エラーにならない。~~たとえ、クエリの途中でパーティションをDROPしても問題なし。~~ 追記：流石にDROPはエラーになるようです。訂正。

### リカバリ後

もののついでなので、リカバリも試してみた。リカバリするとNOLOGGINGオブジェクトは思いっきりエラーになる。なるけれどもTRUNCATEすればよいみたいなので問題なし。

```sql:バックアップからリカバリ
RMAN> backup archivelog all;
RMAN> alter tablespace users offline;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> alter tablespace users online;

SELECT COUNT(*) FROM test_nolog_par; 

ORA-01578: ORACLE data block corrupted (file # 7, block # 134307)
ORA-01110: data file 7: '/oradata/TESTDB/datafile/o1_mf_users_fm1x46px_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

TRUNCATE TABLE test_nolog_par; 

SELECT COUNT(*) FROM test_nolog_par; 

        COUNT(*)
----------------
               0
```

###おわりに


そういうわけで、ダイレクトパスインサートをつかってREDOログを生成しない状態で、クエリを中断を起こさずにテーブルを完全リフレッシュする方法でした。実際に使えるかどうかはまた別の話ということで (^^)。

以上。