一.Data warehouse和OLTP系统的对比
和OLTP系统不同,数据仓库的主要目的是集中统一的存放业务的历史数据,以便用于查询和分析。
数据仓库理论的鼻祖William Inmon给数据仓库的特性归纳为一下四点:
- 面向主题(Subject Oriented)
- 集成(Inegrated)
- 不可修改(Nonvolatile)
- 时间相关(Time Variant)
数据仓库和OLTP是基于不同的需求而得出的解决方案。下面是数据仓库和OLTP系统的一些主要方面的对比:
1.负载
数据仓库主要设计为适合即席查询(Ad hoc query),我们无法预先知道数据仓库的负荷,所以,数据仓库需要设计为能够适应各种可能的查询
而OLTP系统一般执行的操作是可以预先确定的,可以按照估计出的系统负荷来设计OLTP系统.
2.数据修改
数据仓库一般是通过ETL,周期性的将新的数据批量装载。数据进入数据仓库后,一般是不可修改的,所以数据仓库当中一般会建较多的索引用于优化查询。而OLTP系统,终端用户可以修改其对应的某些数据,所有OLTP系统一般即时体现着当前最新的数据。
3.架构设计
数据仓库一般采用非规范化或者部分非规范化的设计,以优化查询性能,典型的设计如星型模型和雪花模型。因为非规范化设计,可以只需要join较少的表就得到更多的数据。而OLTP系统一般采用规范化的设计,以避免出现update/insert/delete异常,并保证数据的一致性。
4.典型操作
数据仓库中,一条查询可能需要访问上千,甚至几百万行数据,所以查询的性能相当重要。在oracle中,dimension,materialized view,bitmap index等技术,都在数据仓库中大量使用以优化查询性能。OLTP中,一个操作一般只需要访问有限的几条数据。
5.历史数据
数据仓库中需要大量的历史数据,以便从历史数据中分析所需要的信息OLTP系统则一般会将历史数据删除,以保证当前事务处理的性能。
二.数据仓库建模
目前来说,数据仓库的数据存储载体还是关系数据库。对于数据仓库的建模,自然也要遵循关系数据库的一般设计准则。从数据仓库诞生以来,主要有两种建模方式。一种是Inmon推荐的关系模型,遵循规范化理论。另外一种是kimpall提出的多维模型,这是目前项目实践中的主流建模方式。多维模型中,最基本用的最多的就是星型模型(star schema)。
Star schema主要的思想在于将我们关心的数据和用于描述数据的属性分隔开来。实际的数据存放于Fact table中,从不同角度来描述数据的属性放到不同的dimension table中。比如,一个sales数据仓库可以这样设计,每一笔销售记录,应该会包含销售的产品,销售的客户,销售的供货商,销售的时间,销售的数量和获得的收入等。当我们要分析整个公司的所有销售记录时,毫无疑问,我们最关心的是一共销售了多少?
一共获得了多少收入?然后更进一步,在某个时间段内销售了多少?来自哪家供货商的产品的销售额最大?面向哪种客户的销售额最大?哪种产品的销售额最大?等等。
从上面我们关心的这些问题我们可以看到,对于销售的数量和金额这类具体的数字型的数据,通常是我们分析的对象,而对于像时间,产品,客户,供货商,我们希望从这些不同的角度来得到数字型数据的一个统计结果。所以,我们将数字型的数据存放在fact table中,将时间,产品,客户,供货商存放在不同的dimension table中,自然,在fact table和dimension table之间存在一个主-外键的关联,各个dimension table之间则没有关系。由此我们可以得到如下的一个star schema:
star schema之所以叫star schema,就是由于上面这个图形的形状来的,fact table处于中间的位置,dimension table围成一圈,每个dimension table和fact table关联。Fact table中除了区分每条记录的主键(fact table的主键很有可能是所有dimension table的外键组合起来的一个组合主键),连接每个dimension table的外键外,就只有我们关心的数字型数据,所以fact table中的每条记录,有个专门的术语称之为度量(measurement),因为我们利用数据仓库做统计分析的时候,这些数据就是统计分析的一个个基本单位,也就是度量值。
显然,star schema是反规范化的。如果将dimension table按照规范化拆开,则star schema演化成了雪花模型(snowflake schema).规范化减少了数据的冗余,但是由于查询的时候要连接更多的表,性能就会受到影响。由于数据仓库主要用于查询的特性,除非你有非常特别的原因,一般推荐此采用star schema来进行数据仓库的架构设计。
三.RELY constraint
数据仓库中的数据,一般是通过ETL定期load进来的。在做ETL的时候,一般会对load的数据的一致性做检查。所以,我们有理由认为,数据仓库中的数据都是符合一致性要求的。
既然我们已经可以确定数据仓库中的数据是一致的,那么就可以不在表上建constraint,constraint对DML或者load操作是有性能影响的,能不用当然不用的好。但是,虽然表中的数据实际上一致了,oracle自己却不知道,优化器也不知道。在利用物化视图查询重写(query rewrite)时,constraint和dimension的作用是很大的(一般在数据仓库环境中,query_rewrite_integrity参数设置为trusted),查询重写对数据仓库的性能影响相当大。
为了告诉oracle,数据应该符合某种一致性条件了,而又不想创建的constraint其作用,就可以创建类型为RELY的constraint,也就是一种可以让oracle知道这些数据是符合这些约束的,但这个约束本身却是没有其实际作用的约束。
文档中给出了一个例子:
FOREIGN KEY ( time_id ) REFERENCES times ( time_id )
RELY DISABLE NOVALIDATE ;
另外,视图只能创建RELY constraint,不能创建普通的constraint。
四.Dimension
前面我们提到,除了constraint,另外一个影响物化视图查询重写的重要因素就是dimension。
要理解oracle中的dimension,首先要搞清楚dimension和dimension table之间的区别。dimension table是table,和关系数据库中的其他table一样,存放数据,需要实际的存储空间。而dimension则只是一个逻辑结构,定义了dimension table中的一个列或一组列于其他列之间的一个层次关系,dimension只保存定义,可以将其理解为一种特定的constraint。所以,dimension不是一种必须存在的结构,但是,创建dimension对于数据仓库中一些复杂的查询重写有着相当重要的意义。而查询重写,则是数据仓库性能优化的一个不二法门。
数据仓库中由于数据量巨大,一些聚合计算等操作往往通过物化视图预先计算存储。但是,不可能对所有维度的所有可能的聚合操作都建立物化视图,一则空间不允许,二则刷新时间也不允许。那么,在对某些聚合操作的sql进行查询重写时,就希望能利用已经存在的物化视图,尽管他们的聚合操作条件不完全一致。而dimension定义的各个level之间的层次关系,对于一些上卷(rolling up)和下钻(drilling down)操作的查询重写的判断是相当重要的,而dimension中定义的attributes对于使用不同的列来做分组的查询重写起作用。
一个典型的dimension定义如下:
LEVEL product IS ( products . prod_id )
LEVEL subcategory IS ( products . prod_subcategory )
LEVEL category IS ( products . prod_category )
HIERARCHY prod_rollup (
product CHILD OF
subcategory CHILD OF
category
)
ATTRIBUTE product_info LEVEL product DETERMINES
( products . prod_name , products . prod_desc ,
prod_weight_class , prod_unit_of_measure ,
prod_pack_size , prod_status , prod_list_price , prod_min_price )
ATTRIBUTE subcategory DETERMINES
( prod_subcategory , prod_subcategory_desc )
ATTRIBUTE category DETERMINES
( prod_category , prod_category_desc ) ;
dimension中三个重要的属性:level,hierarchy,attribute。其中level定义了一个或一组列为一个整体,而hierarchy则定义了各个level之间的层次关系,父level和子level之间是一种1:N的关系,而且,在dimension中可以指定多个hierarchy层次关系。attribute则定义了level和其他列的一个1:1的关系,但这种1:1的关系不一定是可逆的,比如上面的列子,根据product_info,也就是prod_id,可以确定prod_name,但不一定要求prod_name就能确定prod_id。
而且,各个level之间的列不一定要来自同一个table,对于雪花模型,dimension table可能被规范化为许多的小表,则dimension中的level可能是来自不同表中的列。这是需要在dimension中指定join key来指出各个表之间的关联列。例如:
LEVEL customer IS ( customers . cust_id )
LEVEL city IS ( customers . cust_city )
LEVEL state IS ( customers . cust_state_province )
LEVEL country IS ( countries . country_id )
LEVEL subregion IS ( countries . country_subregion )
LEVEL region IS ( countries . country_region )
HIERARCHY geog_rollup (
customer CHILD