— 检查查询重写是否启用
SELECT * FROM v$parameter WHERE name = ‘query_rewrite_enabled’;– 验证查询重写是否生效
EXPLAIN PLAN FOR
SELECT region, TO_CHAR(sale_date, ‘YYYY-MM’) AS month, SUM(amount) AS total_amount
FROM fgsales
GROUP BY region, TO_CHAR(sale_date, ‘YYYY-MM’);SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

— 创建销售表
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
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
6 FROM fgsales
7 GROUP BY region;Materialized view created.

— 启用查询重写
ALTER SESSION SET query_rewrite_enabled = TRUE;– 执行查询，观察是否使用物化视图
EXPLAIN PLAN FOR
SELECT region, SUM(amount) AS total_sales
FROM fgsales
GROUP BY region;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);– 强制使用物化视图
SELECT /*+ REWRITE(sales_summary_mv) */ region, SUM(amount) AS total_sales
FROM fgsales
GROUP BY region;

SQL> EXPLAIN PLAN FOR
2 SELECT region, SUM(amount) AS total_sales
3 FROM fgsales
4 GROUP BY region;Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);Plan hash value: 3986184993

————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————
| 0 | SELECT STATEMENT | | 4 | 100 | 2 (0)| 00:00:01 |
| 1 | MAT_VIEW ACCESS BY INDEX ROWID| SALES_SUMMARY_MV | 4 | 100 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | SYS_C0012345 | 4 | | 1 (0)| 00:00:01 |
————————————————————————————

Note
—–
– dynamic sampling used for this statement (level=2)
– query rewrite using materialized view SALES_SUMMARY_MV

— 检查物化视图状态
SELECT mview_name, status, last_refresh_date
FROM user_mviews
WHERE mview_name = ‘SALES_SUMMARY_MV’;– 检查查询重写是否被禁用
SELECT * FROM v$parameter WHERE name = ‘query_rewrite_enabled’;– 检查物化视图是否满足查询重写条件
EXEC DBMS_MVIEW.EXPLAIN_REWRITE(
‘SELECT region, SUM(amount) AS total_sales FROM fgsales GROUP BY region’,
‘sales_summary_mv’
);SELECT * FROM rewrite_table;