oracle 数据仓库

一.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知道这些数据是符合这些约束的，但这个约束本身却是没有其实际作用的约束。

文档中给出了一个例子：

ALTER TABLE sales ADD CONSTRAINT sales_time_fk
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定义如下：

CREATE DIMENSION products_dim
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来指出各个表之间的关联列。例如：

CREATE DIMENSION customers_dim
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