`

用materialized view + dimension 来提高mondrian 的性能2

阅读更多
接着上一篇的定义我们定义如下两个dimension :
CREATE DIMENSION PRODUCT_DIM
LEVEL "product_id" IS "product"."product_id"
LEVEL "brand_name" IS "product"."brand_name"
LEVEL "product_class_id" IS "product_class"."product_class_id"
LEVEL "product_category" IS "product_class"."product_category"
LEVEL "product_department" IS "product_class"."product_department"
LEVEL "product_family" IS "product_class"."product_family"
HIERARCHY PRODUCT_ROLLUP (
"product_id" CHILD OF
"brand_name" CHILD OF
"product_class_id" CHILD OF
"product_category" CHILD OF
"product_department" CHILD OF
"product_family"
JOIN KEY ("product"."product_class_id") REFERENCES "product_class_id"
)
ATTRIBUTE "product_id" DETERMINES ("product_name")
ATTRIBUTE "product_class_id" DETERMINES ("product_subcategory");


CREATE DIMENSION TIME_DIM
LEVEL time IS "time_by_day"."time_id"
LEVEL month IS "time_by_day"."month_of_year"
LEVEL quarter IS "time_by_day"."quarter"
LEVEL year IS "time_by_day"."the_year"
HIERARCHY TIME_ROLLUP (
time CHILD OF
month CHILD OF
quarter CHILD OF
year
)
ATTRIBUTE time DETERMINES ("time_by_day"."the_date");

然后我们建立materialized view , 注意QUERY_REWRITE_INTEGRITY 和 QUERY_REWRITE_ENABLED 应该已经正确的设置了.
CREATE MATERIALIZED VIEW PRODUCT_SUM
BUILD IMMEDIATE
REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT "time_by_day"."time_id" ,
"product"."product_id",
"product_class"."product_class_id" ,
SUM("sales_fact_1997"."store_sales") ,
SUM("sales_fact_1997"."store_cost")
FROM "time_by_day" "time_by_day",
"product" "product" ,
"product_class" "product_class" ,
"sales_fact_1997" "sales_fact_1997"
WHERE "sales_fact_1997"."time_id" = "time_by_day"."time_id"
AND "sales_fact_1997"."product_id" = "product"."product_id"
AND "product"."product_class_id" = "product_class"."product_class_id"
GROUP BY "time_by_day"."time_id",
"product"."product_id" ,
"product_class"."product_class_id";

现在我们 set autotrace on .

执行
SELECT "time_by_day"."the_date" ,
"product_class"."product_family" ,
SUM("sales_fact_1997"."store_sales") ,
SUM("sales_fact_1997"."store_cost")
FROM "time_by_day" "time_by_day",
"product" "product" ,
"product_class" "product_class" ,
"sales_fact_1997" "sales_fact_1997"
WHERE "sales_fact_1997"."time_id" = "time_by_day"."time_id"
AND "sales_fact_1997"."product_id" = "product"."product_id"
AND "product"."product_class_id" = "product_class"."product_class_id"
GROUP BY "time_by_day"."the_date",
"product_class"."product_family";

从图一中我们看到,当我们把product 聚合到了最高的level product_family,oracle 的 执行计划是从product_sum 中来做聚合的.这就是因为我们建立的dimension 告诉了oracle product有这种层次的关系.product的dimension 即告诉了product_id 能够决定product_name,也告诉了product_id能够聚合product_family , 同样的我们把Time 聚合到最高的level
SELECT "time_by_day"."the_year" ,
"product"."product_name" ,
SUM("sales_fact_1997"."store_sales") ,
SUM("sales_fact_1997"."store_cost")
FROM "time_by_day" "time_by_day",
"product" "product" ,
"product_class" "product_class" ,
"sales_fact_1997" "sales_fact_1997"
WHERE "sales_fact_1997"."time_id" = "time_by_day"."time_id"
AND "sales_fact_1997"."product_id" = "product"."product_id"
AND "product"."product_class_id" = "product_class"."product_class_id"
GROUP BY "time_by_day"."the_year",
"product"."product_name";
从图二中的执行计划同样可以看出我们只选取Time 来做聚合的时候,oracle 仍然是从product_sum 表中来做聚合, 用time_id 来决定the_date ,time_id同样可以聚合year.

最后一个是同时聚合product 和 time
SELECT "time_by_day"."the_year" ,
"product_class"."product_family" ,
SUM("sales_fact_1997"."store_sales") ,
SUM("sales_fact_1997"."store_cost")
FROM "time_by_day" "time_by_day",
"product" "product" ,
"product_class" "product_class" ,
"sales_fact_1997" "sales_fact_1997"
WHERE "sales_fact_1997"."time_id" = "time_by_day"."time_id"
AND "sales_fact_1997"."product_id" = "product"."product_id"
AND "product"."product_class_id" = "product_class"."product_class_id"
GROUP BY "time_by_day"."the_year",
"product_class"."product_family";
同样的,oracle 还是从product_sum 中取数据 .


因为materialized view 的使用,我们可以把我们要分析的Cube 作成一个或几个非常大的materialized view , 建立正确的dimension 之后,当你查询的时候,由于数据已经提前计算过了,所以查询的速度比较快,在加上dimension可以告诉oracle 数据之间的层级关系,减少了我们建立不必要的materialized view , 所以使数据能够得到更加充分的利用.
关于如何建立dimension 倒是比较简单,只要你弄懂数据之间的层级关系就可以了.
对于如何建立materialized view 倒是比较麻烦,
我举个简单的例子吧:
在mondrian 的 foodmart 的例子中,我们可以任意的选取 指标 , product , customers , education leve , gender ,marital sttus ,promotin media ,promotions , store , store size in SQFT , store type , time , yearly income 这十三个要分析的数据来建立cube ,用户有可能使用product 来做分析的维度,也有可能把product 来做Measure 或者不选,所以我们不可能建立所有情况考虑到的Cube .
ps : 如果你非要搞一个出来的话,我可以给你点提示
总的方案有2 的 13 次方:8096 种方案.也就是你要建立8096 个materialized view 就可以解决所有情况.
C 13 3 : 数学里面的概率问题, 十三个里面选3个出来,不论顺序的. C 13 3 = 13*12*12/(1*2*3)
代表的意思是从13个里面选3个出来做fact table ,其余十个做dimension .不论你选不选这些dimension 都一样,
总的方案 = c 13 1 + c 13 2 + c 13 3 + ...... + c 13 13 = 2 的十三次方 = 8096.

如何建立materialized view 还是主要是看你如何建立你的分析的维度.如果你的fact table 本身很多.而维度也很多的情况下,不可能每个fact table 都建立一个关于所有dimension 的materialized view ,对于我们的product dimension数据还算是比较少的,如果达到像大型超市那么多的产品,可能还需要在brand_name 或者 subcategory 来建立materialized view ,所以还是建议根据用户的查询sql 来分析用户到底经常查询那些数据.

下一篇继续介绍 Mondrian 如何使用materialized view 来提高性能.

图一 : product 集合到最高层 的 执行计划.



图二 : Time 聚合到最高层 的执行计划


图三 : 同时将Time 和 product 聚合到最高层 的执行计划


图四 : drill down product 的样子


图五 : 十三种数据,到底怎样建materialized view 呢 ?


分享到:
评论

相关推荐

    materialized_view基础知识

    物化视图(Materialized View)是数据库管理系统中一种特殊的数据对象,它预先计算并存储了一个查询的结果,以便后续的查询能快速访问这些结果。物化视图的主要用途包括提高查询性能、实现数据的局部复制以及支持...

    PostgreSQL物化视图(materialized view)过程解析

    这篇文章主要介绍了PostgreSQL物化视图(materialized view)过程解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 1、创建视图 CREATE MATERIALIZED VIEW ...

    视图上触发器 & Materialized View 物化视图

    物化视图主要用于提高查询性能,特别是对于那些复杂查询或者经常需要汇总数据的情况。它们可以作为数据仓库的一部分,或者在分布式数据库系统中用于数据复制。 在提供的文件中,"项目中物化视图.sql"可能包含创建...

    oracle materialized view

    物化视图是远程数据的本地副本,用于提高查询性能,尤其是在数据仓库环境中,通过预计算汇总数据,显著提升了数据分析的速度。 物化视图可以查询单个表、视图,甚至其他物化视图。在数据复制场景下,物化视图作为...

    MariaDB-Materialized-View

    而材质化视图则会预先计算出结果并存储为物理表,因此,对于复杂的联接操作或聚合查询,使用材质化视图可以显著提高查询性能。尤其当源表数据频繁更新,但需要快速获取预处理过的数据时,材质化视图尤为有用。 1. *...

    Oracle物化视图创建和使用

    创建物化视图可以使用 CREATE MATERIALIZED VIEW 语句,例如: ```sql CREATE MATERIALIZED VIEW MV_VIEW AS SELECT * FROM ... ``` 删除物化视图可以使用 DROP MATERIALIZED VIEW 语句,例如: ```sql DROP ...

    ORACLE多表查询优化

    开发者可以使用 Materialized View 来提高数据库性能。 10. 优化数据库参数 数据库参数的设置对数据库性能产生重要影响。开发者可以根据不同的应用场景设置合适的数据库参数,例如设置合适的缓存大小、调整并发度...

    创建物化视图ORA-12014错误解决方法

    在Oracle数据库中,物化视图(Materialized View)是一种用于优化查询性能的重要工具。它预先计算并存储了复杂的查询结果,从而在实际查询时能够快速地返回数据,大大提升了查询效率。然而,在创建物化视图的过程中...

    tune materialized view

    dbms_advisor.tune_mview使用的例子

    一个漂亮Materialized和灵活的ReactSelect组件

    3. **分页与虚拟化**:对于包含大量选项的下拉列表,React Select支持分页和虚拟化,以提高性能并减少内存消耗。 4. **实时搜索**:用户可以通过在输入框中输入文字,实时过滤出匹配的选项,提升用户体验。 5. **...

    数据库中的物化视图的使用保姆级

    相比普通视图,物化视图可以提供更好的性能,因为普通视图是虚拟表,任何对视图的查询实际上都是转换为对 SQL 语句的查询,性能并没有实际上提高。 在 Vastbase G100 中,物化视图可以分为全量物化视图和增量物化...

    oracle view

    CREATE MATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/48 WITH PRIMARY KEY AS SELECT * FROM emp@remote_db; ``` 这里使用了`REFRESH FAST`选项,意味着需要为源表创建物化视图...

    测试sql性能的实例

    5. 使用数据库内置功能:如SQL Server的索引视图、物化视图,Oracle的materialized view等,可以预先计算并存储结果。 总结,SQL性能测试是数据库管理的重要环节,通过深入分析SQL语句的执行情况,我们可以找出性能...

    PostgresQL-Materialized-View-Demo:PostgresQL中物化视图的演示脚本

    Postgres物化视图演示 设置 在您的机器上安装了postgresql 运行脚本 createdb your_database psql -f setup_scripts/00_setup.sql psql -d your_database 如果需要更多种子数据, bundle exec ruby dev.rb以为...

    1-5+Doris物化视图、索引的典型应用案例.pdf

    Doris 物化视图和索引的典型应用案例 Doris 是一个开源的分布式分析型数据库,旨在帮助用户快速处理海量数据,提供了物化视图和索引等...通过使用物化视图和索引,用户可以快速查询数据,提高查询性能和数据的一致性。

    Oracle中管理物化视图变得更加容易

    在Oracle数据库中,物化视图(Materialized View, MV)是一种重要的性能优化工具,尤其在数据仓库和决策支持系统中。物化视图预先计算并存储了一个查询的结果,允许快速访问而不是每次需要时重新执行复杂的查询。在...

    Oracle性能优化技术内幕

    2. **应用程序优化**:这部分关注的是如何通过修改SQL语句、存储过程和触发器来提高性能。优化主要包括避免全表扫描,使用索引,减少JOIN操作,正确使用绑定变量,以及避免在WHERE子句中使用函数。同时,合理使用...

Global site tag (gtag.js) - Google Analytics