【Oracle】マテリアライズドビューの作成・管理・リフレッシュ完全ガイド｜FAST/COMPLETE・クエリリライト・DBMS_MVIEWまで解説

CREATE MATERIALIZED VIEW mv_dept_sales AS
SELECT
    dept_id,
    COUNT(*)       AS order_count,
    SUM(amount)    AS total_sales
FROM   orders
GROUP BY dept_id;

CREATE MATERIALIZED VIEW マテリアライズドビュー名
    [BUILD {IMMEDIATE | DEFERRED}]          -- 作成時のデータ投入タイミング
    [REFRESH [FAST | COMPLETE | FORCE | NEVER]
             [ON {DEMAND | COMMIT}]
             [START WITH 日時]
             [NEXT 日時]]                   -- リフレッシュ設定
    [{ENABLE | DISABLE} QUERY REWRITE]      -- クエリリライトの有効/無効
AS
SELECT文;

CREATE MATERIALIZED VIEW mv_monthly_sales
    BUILD IMMEDIATE
    REFRESH COMPLETE ON DEMAND
AS
SELECT
    TO_CHAR(order_date, 'YYYYMM') AS ym,
    product_id,
    SUM(quantity)                  AS total_qty,
    SUM(amount)                    AS total_amount
FROM   orders
GROUP BY TO_CHAR(order_date, 'YYYYMM'), product_id;

-- ベーステーブルにMVログを作成
CREATE MATERIALIZED VIEW LOG ON orders
    WITH PRIMARY KEY, ROWID, SEQUENCE
    INCLUDING NEW VALUES;

-- MVログが作成されたことを確認
SELECT log_table, primary_key, rowids, sequence
FROM   user_mview_logs
WHERE  master = 'ORDERS';

CREATE MATERIALIZED VIEW mv_orders_fast
    BUILD IMMEDIATE
    REFRESH FAST ON DEMAND
AS
SELECT
    order_id,
    customer_id,
    product_id,
    quantity,
    amount,
    order_date
FROM   orders;

CREATE MATERIALIZED VIEW mv_order_summary
    BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
AS
SELECT
    customer_id,
    COUNT(*)    AS order_cnt,
    SUM(amount) AS total_amt
FROM   orders
GROUP BY customer_id;

CREATE MATERIALIZED VIEW mv_daily_report
    BUILD IMMEDIATE
    REFRESH COMPLETE
    START WITH TRUNC(SYSDATE + 1) + 2/24   -- 翌日の 02:00
    NEXT  TRUNC(SYSDATE + 1) + 2/24        -- 以降も毎日 02:00
AS
SELECT
    dept_id,
    TO_CHAR(order_date, 'YYYYMMDD') AS order_day,
    SUM(amount) AS daily_sales
FROM   orders
GROUP BY dept_id, TO_CHAR(order_date, 'YYYYMMDD');

-- 単一のMVをCOMPLETEリフレッシュ
EXEC DBMS_MVIEW.REFRESH('MV_DEPT_SALES', 'C');   -- 'C'=COMPLETE

-- FASTリフレッシュ
EXEC DBMS_MVIEW.REFRESH('MV_DEPT_SALES', 'F');   -- 'F'=FAST

-- FORCE（FAST が使えなければ COMPLETE）
EXEC DBMS_MVIEW.REFRESH('MV_DEPT_SALES', '?');   -- '?'=FORCE

-- スキーマ内の全MVをリフレッシュ（アウトオブプレース）
EXEC DBMS_MVIEW.REFRESH_ALL_MVIEWS(:errors);

-- 特定のMVリストをまとめてリフレッシュ
EXEC DBMS_MVIEW.REFRESH(
    LIST    => 'MV_DEPT_SALES,MV_MONTHLY_SALES',
    METHOD  => 'C',    -- COMPLETE
    ATOMIC_REFRESH => FALSE   -- 並列リフレッシュを許可
);

-- QUERY REWRITE を有効化（ENABLE QUERY REWRITE句を指定）
CREATE MATERIALIZED VIEW mv_dept_monthly
    BUILD IMMEDIATE
    REFRESH COMPLETE ON DEMAND
    ENABLE QUERY REWRITE
AS
SELECT
    dept_id,
    TO_CHAR(order_date, 'YYYYMM') AS ym,
    SUM(amount) AS total_amount,
    COUNT(*)    AS order_cnt
FROM   orders
GROUP BY dept_id, TO_CHAR(order_date, 'YYYYMM');

-- クエリリライトを有効にする（デフォルト有効だが確認・明示設定）
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

-- クエリリライトの整合性チェックレベル
-- TRUSTED: ディメンションの制約を信頼して積極的にリライト
-- ENFORCED: 制約で保証された場合のみリライト（デフォルト）
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

-- ベーステーブルへのSELECT（オプティマイザがMVを使うかもしれない）
SELECT dept_id, TO_CHAR(order_date, 'YYYYMM'), SUM(amount)
FROM   orders
GROUP BY dept_id, TO_CHAR(order_date, 'YYYYMM');

SELECT
    mview_name,
    refresh_mode,         -- DEMAND / COMMIT
    refresh_method,       -- COMPLETE / FAST / FORCE
    last_refresh_date,    -- 最終リフレッシュ日時
    staleness,            -- FRESH / STALE / UNKNOWN
    query_rewrite_enabled -- Y / N
FROM   user_mviews
ORDER BY mview_name;

SELECT
    owner,
    mview_name,
    refresh_mode,
    refresh_method,
    last_refresh_date,
    staleness,
    query                 -- 定義クエリ（最大4000文字）
FROM   dba_mviews
WHERE  owner = 'MYSCHEMA'
ORDER BY mview_name;

SELECT
    log_table,            -- ログテーブル名（MLOG$_テーブル名）
    master,               -- ベーステーブル名
    log_creator,          -- 作成者
    primary_key,          -- プライマリキーを記録するか
    rowids,               -- ROWIDを記録するか
    sequence              -- SEQUENCEを記録するか
FROM   user_mview_logs;

-- 結果格納テーブルを作成（初回のみ）
-- @?/rdbms/admin/utlxmv.sql でも作成可能
CREATE TABLE mv_capabilities_table (
    statement_id    VARCHAR2(30),
    mvowner         VARCHAR2(30),
    mvname          VARCHAR2(30),
    capability_name VARCHAR2(30),
    possible        CHAR(1),
    related_text    VARCHAR2(2000),
    related_num     NUMBER,
    msgno           NUMBER,
    msgtxt          VARCHAR2(2000),
    seq             NUMBER
);

-- 確認対象のMVを分析
EXEC DBMS_MVIEW.EXPLAIN_MVIEW('SELECT dept_id, SUM(amount) FROM orders GROUP BY dept_id', 'MV_TEST');

-- 結果確認（FASTリフレッシュが可能かどうか）
SELECT capability_name, possible, msgtxt
FROM   mv_capabilities_table
WHERE  capability_name LIKE '%REFRESH%'
ORDER BY seq;

-- 部署IDと年月の組み合わせで検索されることが多い場合
CREATE INDEX idx_mv_dept_monthly_01
    ON mv_dept_monthly (dept_id, ym);

-- インデックス情報の確認
SELECT index_name, column_name, column_position
FROM   user_ind_columns
WHERE  table_name = 'MV_DEPT_MONTHLY'
ORDER BY index_name, column_position;

-- リフレッシュモードを変更（COMPLETEに変更）
ALTER MATERIALIZED VIEW mv_dept_sales
    REFRESH COMPLETE;

-- スケジュールを変更（毎日03:00に変更）
ALTER MATERIALIZED VIEW mv_daily_report
    REFRESH NEXT TRUNC(SYSDATE + 1) + 3/24;

-- クエリリライトを無効化
ALTER MATERIALIZED VIEW mv_dept_monthly
    DISABLE QUERY REWRITE;

-- MVを削除（MVのデータも一緒に削除される）
DROP MATERIALIZED VIEW mv_dept_sales;

-- MVログを削除（必要なければ削除）
DROP MATERIALIZED VIEW LOG ON orders;