Data warehouse和OLTP系统的对比 2

四.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，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来指出各个表之间的关联列。

如果不指定skip when null子句，每个level中都不允许出现null值。
通过dbms_dimension.describe_dimension可以查看dimension的定义。
通过dbms_dimension.validate_dimension可以检查dimension是否定义正确，在执行之前需要执行ultdim.sql创建一个dimension_exceptions表，如果定义有误，则会在dimension_exceptions中查到相应的记录。在9i里，validate_dimension在dbms_olap包中。

五.Bitmap join index

Bitmap index的主要思想就是，针对每一个可能的值x，建立一个或一组位图映射，每个bit为1代表这个位置的值等于x，为0则不等于x。而每个位置都可以直接映射到某一行的rowid。由于在执行DML操作时，锁定的是整个bitmap，而不是bitmap中的某个位，所以bitmap index对于并发DML的性能很差，而且频繁的DML操作会使得bitmap index的空间效率大打折扣，所以OLTP系统并不合适使用bitmap index。对于基本没有DML操作，有大量ad hoc查询的Data warehouse环境则相当有效。

从oracle9i起，oracle又引进了一种新的索引类型：bitmap join index。和bitmap index建立在单个table上不同，bitmap join index是基于多表连接的，连接条件要求是等于的内连接(equi-inner join)。对于数据仓库而言，较普遍的是Fact table的外键列和相关的Dimension table的主键列的连接操作。

Bitmap join index能够消除查询中的连接操作，因为它实际上已经将连接的结果保存在索引当中了。而且，相对于在表的连接列上建普通bitmap index来说，bitmap join index需要更少的存储空间。同样的基于连接的Metarialized view也可以用来消除连接操作。但bitmap join index比起物化视图来更有效率，因为通过bitmap join index可以直接将基于索引列的查询对应到事实表的rowid。

以oracle的sample schema SH中的sales和customers表做个例子

1.建立基于维度表中一个列的bitmap join index

建立这样的bitmap join index后，下面的查询就可以从index中直接得到结果，而不再需要连接sales和custmoers两张表来获得结果了。相当于根据连接条件，将customers表中的cust_gender列保存到sales表中了。

通过将bitmap join index dump出来可以看到，实际上，索引是按照ustomers.cust_gender分成2个位图，每个位图映射到sales表的ROWID。

所以根据customers.cust_gender来过滤连接结果时，从索引中可以直接得到目标数据在sales中的rowid，无须执行join操作了。

Bitmap join index的一些限制条件

只支持CBO
只能是equi-inner连接，任何外连接都无法使用bitmap join index
多个连接条件只能是AND关系
只能在fact table上执行并行DML。如果在dimension table上执行并行DML，会导致索引变为unusable状态。
不同的事务中，只能并发更新一个表
在From字句中，任何一个表都不能出现两次
在索引组织表(IOT)和临时表上不能建立bitmap join index
索引只能基于dimenion table中的列
维度表用于连接的列只能是主键列或者是有唯一约束的列。

SQL> create bitmap index sales_cust_gender_bjix
2 on sales(customers.cust_gender)
3 from sales,customers
4 where sales.cust_id=customers.cust_id;
from sales,customers
*
ERROR at line 3:
ORA-25954: missing primary key or unique constraint on dimension


如果维度表的主键是组合主键，那么连接条件需要是全部主键列都参与其他对于bitmap index的限制条件同样使用于bitmap join index，比如在分区表上只能是local，不能是global。


六.Unique constraint & unique index


一般情况下，unique constraint都是通过unique index来实现的。但是在数据仓库中，由于数据量巨大，建立一个索引可能需要花费相当大的时间和空间，假如查询中又用不上这个索引的话，那么建立索引的高代价却没有带来什么收益，这是很不划算的。


举个例子，假如有一个sales表，其中sales_id的数据是唯一的，我们在sales_id上建一个unique constraint，语法如下：


alter table sales add constraint sales_uk unique(sales_id);


这样建立的unique constraint是enable validate状态的，oracle会自动在sales_id列上创建一个的名为sales_uk的unique index。通过查询user_indexes或者user_ind_columns视图可以看到这个index：


SQL> select index_name,column_name from user_ind_columns where index_name='SALES_UK';
INDEX_NAME COLUMN_NAME
--------------------- ---------------------
SALES_UK SALES_ID


在数据仓库环境中，这个unique index可能是不合适的：
1.这个索引可能会相当的大。
2.在查询中几乎不会用到sales_id来做为过滤条件
3.多数情况下，sales会是一个分区表，而且分区键不会是sales_id。这样这个unique index必须是global index，在对分区的一些DDL操作中可能会导致global index失效。那么怎么能在创建unique constraint的同时不生成unique index呢？很简单，创建一个状态为disable validate的unique constraint就能满足上述要求。


alter table sales add constraint sales_uk unique(sales_id) disable validate;


再来查询user_ind_columns可以发现没有记录：


SQL> select index_name,column_name from user_ind_columns where index_name='SALES_ID';
no rows selected


但是disable validate状态的索引会导致无法对该列进行DML操作


SQL> delete from sales where rownum=1;
delete from sales where rownum=1
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (NING.SALES_UK) disabled and validated


那么，要修改有disable validate约束的表中的数据，只有以下两种方法：
1.使用DDL操作，比如分区表的exchange partition
2.首先drop constraint，修改数据，再重新创建disable validate的constraint


七.Partition table


分区表(partition table)在数据仓库中的重要性不言而寓，数据仓库的事实表中的数据量一般都比较大，而且很多时候是和时间相关的历史数据，使用范围分区是最合适的，但有时也要结合实际考虑其他的分区方式。


分区有三种基本的方式：range,hash和list。某个分区还可以继续进行子分区，所以，上面三种基本的分区还可以组成两种组合分区：range-hash和range-list。


Range partition


范围分区就是安装分区键的不同范围的数据进入到不同的分区当中，对于按照时间延续性的历史数据，这种分区非常合适。所以这种分区也是


最常见的分区形式。
例如，对于一个销售记录的表，可以按照销售时间来分区，每个月的数据都单独做为一个分区：


CREATE TABLE sales_range
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')));


注意，分区表达式中是一个小于的关系，也就是不包括边界的，等于边界值的数据会进入到下一个分区。如果我们还有些数据在所有的分区定义之外，那么可以定义一个条件为MAXVALUE的分区，例如partition sales_other values less than(Maxvalue)，则不符合前面所有分区条件的数据都会进入这个“默认分区”。


如果你遇到以下情况，考虑使用范围分区是非常合适的：
1.对于一个大表，经常使用范围条件来查询的，可以考虑将该条件作为分区键进行反问分区。
2.你希望对表中的数据滚动更新。比如保持36个月的销售记录，每个月删除36个月前的分区，再建一个新的分区，将新的一个月的记录加进来。
3.当一个表中的数据量非常大的时候，一些管理任务，比如备份恢复什么的，都会花费相当长的时间。而将这些表改造成分区表对于简化这些管理任务相当有效，可以针对单个的分区来进行管理。


Hash partition


Hash分区是，根据oracle内部的一种hash算法，将不同的数据放到不同的分区当中，因此能够将所有的数据比较平均的分到所有的分区中，是各个分区中的数据量比较平衡。
Oracle采用的是一种线性hash算法，分区的数目建议是2的指数个，比如2,4,8,16……


下面是4个分区的例子：


CREATE TABLE sales_hash
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
week_no NUMBER(2))
PARTITION BY HASH(salesman_id)
PARTITIONS 4;


由于hash分区不是按照数据本身的一些逻辑来分区的，所以对于历史数据不合适，主要用来将数据平衡到各个分区当中。而且，hash分区的分区排除只能基于等于条件。


使用hash分区，你可以：
1.对于一些大数据量的表，提供更好的可用性和可管理性。这个算是所有分区表的共性。
2.防止数据在不同分区表中间分布不均。这个是hash分区的特性。
3.对于经常使用等于条件或者in条件的查询，hash分区可以很好的使用分区排除和分区级连接。这个可以算hash分区的一个限制条件。