基于Oracle SALES_DATA销售表创建物化视图实现目标输出的问题
基于销售表创建年度销售汇总物化视图
基础说明
- 本次使用的原始销售表为
SALES_DATA,核心字段说明如下:Product:产品名称,字符串类型,长度255Date1:销售年份,字符串类型,存储4位数字年份Sale:销售额,当前定义为字符串类型,实际存储数值型金额ID:记录唯一标识,数值类型
- 目标输出为按产品、年份维度汇总销售额的结构化结果,可支撑快速查询各产品的年度销售情况,交叉表展示效果为行维度对应产品、列维度对应年份、值为对应周期的销售额总和
- 创建物化视图可避免每次查询都遍历原始全量表,大幅提升多维度汇总查询的响应速度
参考实现语句
前置调整(推荐执行)
当前表的Sale字段定义为字符串类型,直接做数值汇总会触发隐式类型转换,影响性能,建议先修正字段类型:
ALTER TABLE SALES_DATA MODIFY Sale NUMBER(18,2);
物化视图创建语句
如果已执行上述字段修正,可直接运行以下语句创建:
CREATE MATERIALIZED VIEW MV_PRODUCT_YEARLY_SALE -- 配置刷新规则:*按需全量刷新*,仅手动触发时同步最新数据 REFRESH COMPLETE ON DEMAND -- 关闭查询重写,如需开启可调整为ENABLE QUERY REWRITE DISABLE QUERY REWRITE AS SELECT Product, Date1 AS sale_year, SUM(Sale) AS total_sale FROM SALES_DATA GROUP BY Product, Date1;
如果暂不修改原表字段类型,创建语句需手动做类型转换:
CREATE MATERIALIZED VIEW MV_PRODUCT_YEARLY_SALE REFRESH COMPLETE ON DEMAND DISABLE QUERY REWRITE AS SELECT Product, Date1 AS sale_year, SUM(TO_NUMBER(Sale)) AS total_sale FROM SALES_DATA GROUP BY Product, Date1;
配置指引
- 刷新规则调整:如果数据按天更新,可将物化视图配置为定时自动刷新,例如每天凌晨2点执行刷新,对应的创建配置调整为:
REFRESH COMPLETE START WITH SYSDATE NEXT TRUNC(SYSDATE) + 1 + 2/24 - 手动刷新执行命令:
EXEC DBMS_MVIEW.REFRESH('MV_PRODUCT_YEARLY_SALE') - 如需实现产品为行、各年份为列的交叉表效果,可基于上述物化视图使用Oracle
PIVOT语法查询,无需将交叉转换逻辑存入物化视图占用额外存储
内容的提问来源于stack exchange,提问作者Cordelia
相关产品推荐
相关产品推荐

