Oracle物化视图 #

物化视图 vs 普通视图
├── 普通视图
│   ├── 只存储查询定义
│   ├── 每次查询时执行
│   └── 不占用存储空间
└── 物化视图
    ├── 存储实际数据
    ├── 可定期刷新
    └── 占用存储空间

物化视图应用场景
├── 数据仓库
│   └── 预计算聚合数据
├── 分布式数据库
│   └── 数据复制
├── 移动计算
│   └── 数据同步
└── 性能优化
    └── 加速复杂查询

-- 创建物化视图基本语法
CREATE MATERIALIZED VIEW mv_name
    [BUILD IMMEDIATE|DEFERRED]
    [REFRESH [FAST|COMPLETE|FORCE] [ON DEMAND|ON COMMIT]]
    [ENABLE|DISABLE QUERY REWRITE]
AS
SELECT statement;

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_emp_salary
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT 
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

-- 查询物化视图
SELECT * FROM mv_emp_salary;

-- 完全刷新
CREATE MATERIALIZED VIEW mv_dept_stats
REFRESH COMPLETE ON DEMAND
AS
SELECT department_id, COUNT(*) AS cnt
FROM employees
GROUP BY department_id;

-- 快速刷新（需要物化视图日志）
CREATE MATERIALIZED VIEW LOG ON employees
WITH ROWID, SEQUENCE (department_id, salary)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_emp_fast
REFRESH FAST ON COMMIT
AS
SELECT department_id, SUM(salary) AS total_sal
FROM employees
GROUP BY department_id;

-- 定时刷新
CREATE MATERIALIZED VIEW mv_daily_stats
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1
AS
SELECT * FROM sales WHERE sale_date >= TRUNC(SYSDATE) - 30;

-- COMPLETE：完全刷新，重新执行查询
-- FAST：增量刷新，只刷新变化的数据
-- FORCE：自动选择刷新方式
-- NEVER：从不刷新

-- 手动刷新
EXEC DBMS_MVIEW.REFRESH('mv_emp_salary', 'C');  -- 完全刷新
EXEC DBMS_MVIEW.REFRESH('mv_emp_salary', 'F');  -- 快速刷新

-- 刷新所有物化视图
EXEC DBMS_MVIEW.REFRESH_ALL_MVIEWS;

-- ON DEMAND：按需刷新（默认）
CREATE MATERIALIZED VIEW mv_on_demand
REFRESH ON DEMAND
AS SELECT * FROM employees;

-- ON COMMIT：提交时刷新
CREATE MATERIALIZED VIEW mv_on_commit
REFRESH FAST ON COMMIT
AS SELECT department_id, COUNT(*) AS cnt
FROM employees GROUP BY department_id;

-- 定时刷新
CREATE MATERIALIZED VIEW mv_scheduled
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1/24  -- 每小时刷新
AS SELECT * FROM employees;

-- 创建支持查询重写的物化视图
CREATE MATERIALIZED VIEW mv_emp_dept
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT 
    d.department_name,
    COUNT(*) AS employee_count,
    AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

-- 查询重写示例
-- 原始查询
SELECT d.department_name, AVG(e.salary)
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

-- Oracle自动使用物化视图

-- 查看执行计划
EXPLAIN PLAN FOR
SELECT d.department_name, AVG(e.salary)
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 查看物化视图信息
SELECT mview_name, refresh_method, refresh_mode, last_refresh_date
FROM user_mviews;

-- 查看物化视图详细信息
SELECT * FROM user_mviews WHERE mview_name = 'MV_EMP_SALARY';

-- 查看物化视图日志
SELECT master, log_table FROM user_mview_logs;

-- 修改刷新策略
ALTER MATERIALIZED VIEW mv_emp_salary
REFRESH COMPLETE ON DEMAND;

-- 启用查询重写
ALTER MATERIALIZED VIEW mv_emp_salary
ENABLE QUERY REWRITE;

-- 禁用查询重写
ALTER MATERIALIZED VIEW mv_emp_salary
DISABLE QUERY REWRITE;

-- 删除物化视图
DROP MATERIALIZED VIEW mv_emp_salary;

-- 保留物化视图表
DROP MATERIALIZED VIEW mv_emp_salary PRESERVE TABLE;