— 创建销售表
CREATE TABLE fgsales (
sale_id NUMBER PRIMARY KEY,
sale_date DATE,
amount NUMBER,
region VARCHAR2(50)
);– 插入测试数据
INSERT INTO fgsales VALUES (1, SYSDATE – 30, 1000, ‘North’);INSERT INTO fgsales VALUES (2, SYSDATE – 20, 2000, ‘South’);INSERT INTO fgsales VALUES (3, SYSDATE – 10, 1500, ‘East’);INSERT INTO fgsales VALUES (4, SYSDATE, 3000, ‘West’);COMMIT;– 创建物化视图
CREATE MATERIALIZED VIEW sales_summary_mv
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT region, SUM(amount) AS total_sales, COUNT(*) AS order_count
FROM fgsales
GROUP BY region;

SQL> CREATE MATERIALIZED VIEW sales_summary_mv
2 REFRESH FAST ON DEMAND
3 ENABLE QUERY REWRITE
4 AS
5 SELECT region, SUM(amount) AS total_sales, COUNT(*) AS order_count
6 FROM fgsales
7 GROUP BY region;Materialized view created.

— 手动刷新物化视图
EXEC DBMS_MVIEW.REFRESH(‘sales_summary_mv’, ‘F’);– 定时刷新物化视图
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => ‘refresh_sales_mv’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘EXEC DBMS_MVIEW.REFRESH(”sales_summary_mv”, ”F”);’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0’,
enabled => TRUE
);END;/

— 为物化视图创建索引
CREATE INDEX idx_sales_mv_region ON sales_summary_mv(region);– 启用并行查询
ALTER MATERIALIZED VIEW sales_summary_mv PARALLEL 4;– 分析物化视图
EXEC DBMS_STATS.GATHER_TABLE_STATS(‘HR’, ‘sales_summary_mv’);