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
create bitmap index sales_cust_gender_bjix
on sales(customers.cust_gender)
from sales,customers
where sales.cust_id=customers;
建立这样的bitmap join index后,下面的查询就可以从index中直接得到结果,而不再需要连接sales和custmoers两张表来获得结果了。相当于根据连接条件,将customers表中的cust_gender列保存到sales表中了。
select sum(sales.amount_sold)
from sales,customers
where sales.cust_id,customers.cust_id
and customers.cust_gender='M';
通过将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分区的一个限制条件。