oracle 19c物化视图利用分区裁剪实现查询重写,核心在于物化视图必须基于分区表构建且保持分区结构一致。当查询条件包含分区键的过滤谓词时,优化器会先对基表进行分区裁剪,随后将裁剪后的数据范围与物化视图的分区进行匹配,从而仅扫描相关的物化视图分区来完成查询。

如何确保物化视图与基表的分区结构兼容?
要实现有效的查询重写,物化视图必须显式声明与基表相同的分区方式(如RANGE、LIST或HASH)。如果物化视图未定义分区,或者分区键与基表不一致,优化器将无法建立分区间的映射关系,导致无法利用分区裁剪进行重写。通常使用ON PREBUILD TABLE选项在创建时预建物理表,并指定PARTITION BY子句以确保结构对齐。

CREATE MATERIALIZED VIEW mv_sales PARTITION BY RANGE (sales_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
SUBPARTITION BY LIST (region_id)
(
PARTITION p_before_jan VALUES LESS THAN (DATE '2023-01-01')
SUBPARTITION sp_all VALUES (DEFAULT)
)
AS SELECT s.*, r.region_name FROM sales s JOIN regions r ON s.region_id = r.region_id;上述代码展示了创建一个按时间范围分区并按地区列表子分区的物化视图。这种结构与基表完全对应,使得后续查询可以精确匹配到特定的分区和子分区。
查询谓词如何触发分区裁剪与重写?
当执行带有分区键过滤条件的查询时,Oracle优化器会分析谓词以确定需要访问的分区。如果存在对应的物化视图,优化器会将这些分区请求映射到物化视图上。例如,查询特定月份的数据,优化器只会扫描该月份对应的物化视图分区,而忽略其他无关分区。这种机制显著减少了I/O操作,提升了查询性能。关键在于查询中的谓词必须能被优化器识别为分区键的约束。

SELECT /*+ USE_MV */ SUM(amount) FROM mv_sales WHERE sales_date BETWEEN DATE '2023-05-01' AND DATE '2023-05-31';
运行上述查询,优化器会识别出sales_date的范围,并直接定位到物化视图中对应的p_2023_05分区(假设自动生成了该分区),从而避免全表扫描。若谓词涉及非分区键列,则可能退化为全分区扫描或回退到基表查询。
在特定输入条件下查询重写是否成功?
为了验证分区裁剪与重写的实际效果,我们检查一个具体的查询场景:查询2023年6月华东地区的销售总额。在此条件下,优化器应能同时应用分区裁剪(时间)和子分区裁剪(地区),并直接从物化视图中获取结果。通过查看执行计划,可以确认是否使用了物化视图以及是否发生了分区裁剪。
EXPLAIN PLAN FOR SELECT SUM(amount) FROM mv_sales WHERE sales_date >= DATE '2023-06-01' AND sales_date < DATE '2023-07-01' AND region_id IN (1, 2); -- 假设1,2代表华东地区 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
预期结果中,执行计划应显示MATERIALIZED VIEW ACCESS操作,并且分区访问类型应为RANGE或SINGLE,表明只访问了特定的分区。如果执行计划显示TABLE ACCESS FULL或指向基表,则说明重写失败,需检查物化视图日志、权限或谓词兼容性。


















