Full Scanを速くしちゃう　その８

＜Full Scanを速くしちゃう　その８＞
ペンネーム：グリーンペペ

前回はMViewを使用することでFull Scanをかなり速くできることを確認した。
しかしながら、MViewを使用する場合、定期的にMViewを最新データに更新しな
いと[refreshしないと]データが陳腐化してしまう。

◆環境
HP-UX hp11i B.11.11
Oracle9i Enterprise Edition Release 10.1.0.2.0 – 64bit Production

◆検証

--Full Scan実行
SQL> select /* test1 */ count(*) from tpc2.stock;

  COUNT(*)
----------
    500000

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS
   1    0   MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_STOCK' (MAT_VIEW REWRITE)

Query Rewite機能によりFull ScanがMViewへのアクセスに書き替えられている。

--元表にレコードをinsert
SQL> insert into tpc2.stock(s_i_id) values(1);

--再度Full Scan実行
SQL> select /* test2 */ count(*) from tpc2.stock;

  COUNT(*)
----------
    500001

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'STOCK' (TABLE)

MViewの内容が陳腐化してしまったために、Query Rewriteが行われずstock表
に対してFull Scanされていることがわかる。
陳腐化してしまった、内容でも構わないのであれば以下の初期化パラメタを変
更し、強制的にMViewにアクセスするようにできる。

/* Query Rewrite時に強制的にオプティマイザがMViewを使用した実行計画を
選択するように設定 */
SQL> alter system set query_rewrite_enabled=force;

/* Query Rewrite時に元表とMViewに齟齬があってもMViewにアクセスするよう
に設定 */
SQL> alter system set query_rewrite_integrity=stale_tolerated;

/* Full Scan実行 */
SQL> select /* test3 */ count(*) from tpc2.stock;

  COUNT(*)
----------
    500000

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS
   1    0   MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_STOCK' (MAT_VIEW REWRITE)

元表とMViewにずっと齟齬があってもO.K.なサイトはお目にかかったことがない。
1時間とか1日に1回MViewを更新する必要がある。

--MViewを最新に更新[refresh]
SQL> exec dbms_mview.refresh('TPC2.MV_STOCK');

PL/SQL procedure successfully completed.

--Full Scan実行
SQL> select /* test4 */ count(*) from tpc2.stock;

  COUNT(*)
----------
    500001

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS
   1    0   MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_STOCK' (MAT_VIEW REWRITE)

MViewを更新することにより、MViewの内容と元表の内容の同期が取れた。
常にMViewと元表の同期を取る必要があり、かつMViewにアクセスすることでFull
Scanのコストを下げたい場合は、元表更新時にMViewも同時に更新されるよう
に変更する。

SQL> alter materialized view tpc2.mv_stock refresh on commit;

Materialized view altered.

SQL> insert into tpc2.stock(s_i_id) values(2);
SQL> commit;
SQL> select /* test5 */ count(*) from tpc2.stock;

  COUNT(*)
----------
    500002

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS
   1    0   MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_STOCK' (MAT_VIEW REWRITE)

refreshを行わなくてもMViewと元表の同期が取れていることが確認できた。
しかしながら、この場合commit時にMViewへの更新も発生するために更新処理
の負荷が高くなることを念頭に置いて活用して頂きたい。

今週までで～Full Scanを速くしちゃう～シリーズは一旦終了します。
グリーンペペはrefreshしてまた何れ、新たなFull Scanを速くするテクニック
をお伝えします。それではアディオス!!

夏休みを満喫したい!!茅ヶ崎にて

Full Scanを速くしちゃう その８