[转帖]Data warehouse和OLTP系统的对比 2_MQ, Tuxedo及OLTP讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MQ, Tuxedo及OLTP讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 4985 | 回复: 0   主题: [转帖]Data warehouse和OLTP系统的对比 2        下一篇 
ilikeorcl
注册用户
等级:中尉
经验:431
发帖:32
精华:1
注册:2012-12-17
状态:离线
发送短消息息给ilikeorcl 加好友    发送短消息息给ilikeorcl 发消息
发表于: IP:您无权察看 2012-12-21 11:34:23 | [全部帖] [楼主帖] 楼主

 Data warehouse和OLTP系统的对比 2

四.Dimension

前面我们提到,除了constraint,另外一个影响物化视图查询重写的重要因素就是dimension

要理解oracle中的dimension,首先要搞清楚dimensiondimension table之间的区别。dimension tabletable,和关系数据库中的其他table一样,存放数据,需要实际的存储空间。而dimension则只是一个逻辑结构,定义了dimension table中的一个列或一组列于其他列之间的一个层次关系,dimension只保存定义,可以将其理解为一种特定的constraint。所以,dimension不是一种必须存在的结构,但是,创建dimension对于数据仓库中一些复杂的查询重写有着相当重要的意义。而查询重写,则是数据仓库性能优化的一个不二法门。

数据仓库中由于数据量巨大,一些聚合计算等操作往往通过物化视图预先计算存储。但是,不可能对所有维度的所有可能的聚合操作都建立物化视图,一则空间不允许,二则刷新时间也不允许。那么,在对某些聚合操作的sql进行查询重写时,就希望能利用已经存在的物化视图,尽管他们的聚合操作条件不完全一致。而dimension定义的各个level之间的层次关系,对于一些上卷(rolling up)和下钻(drilling down)操作的查询重写的判断是相当重要的,而dimension中定义的attributes对于使用不同的列来做分组的查询重写起作用.

dimension中三个重要的属性:levelhierarchyattribute。其中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_dimensiondbms_olap包中。

五.Bitmap join index

Bitmap index的主要思想就是,针对每一个可能的值x,建立一个或一组位图映射,每个bit1代表这个位置的值等于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

oraclesample schema SH中的salescustomers表做个例子

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中直接得到结果,而不再需要连接salescustmoers两张表来获得结果了。相当于根据连接条件,将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 constraintenable validate状态的,oracle会自动在sales_id列上创建一个的名为sales_ukunique 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 validateunique 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 validateconstraint

    七.Partition table

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

    分区有三种基本的方式:range,hashlist。某个分区还可以继续进行子分区,所以,上面三种基本的分区还可以组成两种组合分区:range-hashrange-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分区的一个限制条件。




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论