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

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

Full Scanを速くするテクニックとしてMaterialized View(以後MView)を取り
上げる。MViewはDBLink先リモートDBの複製として使用する場合があるが、ロ
ーカルDB上の表などからによる集計や結合、条件といった出力結果を格納する
ことができる。早速使ってみよう。

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

◆検証
–MViewを作成する

SQL> create materialized view tpc2.mv_stock as select count(*) from tpc2.stock;

ORA-01031: insufficient privileges

MViewを作成するにはcreate any materialized view権限が必要だ。

–create any materialized view権限をtpc2ユーザに付与し、MViewを作成する。

SQL> conn system/********
SQL> grant create any materialized view to tpc2;
SQL> conn tpc2/********
SQL> create materialized view tpc2.mv_stock as select count(*) from tpc2.stock;

Materialized view created.

MViewが作成された。

–性能比較 Full Scan VS MView

SQL> select count(*) from tpc2.stock;
  COUNT(*)
----------
    500000

Elapsed: 00:00:12.20

Statistics
----------------------------------------------------------
::略::
      45460  consistent gets
      44992  physical reads
::略::
SQL> select * from tpc2.mv_stock;

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

Elapsed: 00:00:00.00

Statistics
----------------------------------------------------------
::略::
          3  consistent gets
          0  physical reads
::略::

当然ながら、MViewに対してアクセスした方がアクセスする行数が少ないので
I/Oもレスポンス時間も優れている。
しかしながら、MViewにアクセスするようにSQL文を書き換える必要がある。ま
た今回の検証のようにcountを取得するのにMViewへアクセスする際にはcount
関数は使用しておらず感覚的に解りにくい。
全くSQL文を変更することなく、MViewを使用するQuery Rewrite機能を使用す
ればオプティマイザが自動的にMViewへアクセスするSQL文へ変更してくれる。
但し、Query Rewrite機能を使用するには幾つかの設定が必要だ。

条件1. コストベースでSQL文を実行すること
条件2. 初期化パラメータ”query_rewrite_enabled” が “true”に設定されてい
ること
条件3. MViewがquery rewrite可能になっていること

–上記条件の確認

SQL> sho parameter optimizer_mode
NAME                                 VALUE
------------------------------------ ------------------------------
optimizer_mode                       ALL_ROWS

SQL> sho parameter query_rewrite_enabled
NAME                                 VALUE
------------------------------------ ------------------------------
query_rewrite_enabled                TRUE


--MViewをquery rewrite可能に変更
SQL> alter materialized view mv_stock enable query rewrite;


--MViewがQuery Rewrite機能により使用されるか確認
SQL> select count(*) from tpc2.stock;

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

Elapsed: 00:00:00.02

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

SQL文そのものはstock表への問い合わせであるが、実行計画を見るとMViewへ
アクセスしていることが判る。
また、レスポンスもかなり速くなっていることが実証できた。
来週に続く。

夏休みがやってきた茅ヶ崎より

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