Data Warehouse Guide阅读笔记Word文档格式.docx
- 文档编号:21655951
- 上传时间:2023-01-31
- 格式:DOCX
- 页数:12
- 大小:40.91KB
Data Warehouse Guide阅读笔记Word文档格式.docx
《Data Warehouse Guide阅读笔记Word文档格式.docx》由会员分享,可在线阅读,更多相关《Data Warehouse Guide阅读笔记Word文档格式.docx(12页珍藏版)》请在冰豆网上搜索。
因为非规范化设计,可以只需要join较少的表就得到更多的数据。
而OLTP系统一般采用规范化的设计,以避免出现update/insert/delete异常,并保证数据的一致性。
4.典型操作
数据仓库中,一条查询可能需要访问上千,甚至几百万行数据,所以查询的性能相当重要。
在oracle中,dimension,materializedview,bitmapindex等技术,都在数据仓库中大量使用以优化查询性能。
OLTP中,一个操作一般只需要访问有限的几条数据。
5.历史数据
数据仓库中需要大量的历史数据,以便从历史数据中分析所需要的信息OLTP系统则一般会将历史数据删除,以保证当前事务处理的性能。
二.数据仓库建模
目前来说,数据仓库的数据存储载体还是关系数据库。
对于数据仓库的建模,自然也要遵循关系数据库的一般设计准则。
从数据仓库诞生以来,主要有两种建模方式。
一种是Inmon推荐的关系模型,遵循规范化理论。
另外一种是kimpall提出的多维模型,这是目前项目实践中的主流建模方式。
多维模型中,最基本用的最多的就是星型模型(starschema)。
Starschema主要的思想在于将我们关心的数据和用于描述数据的属性分隔开来。
实际的数据存放于Facttable中,从不同角度来描述数据的属性放到不同的dimensiontable中。
比如,一个sales数据仓库可以这样设计,每一笔销售记录,应该会包含销售的产品,销售的客户,销售的供货商,销售的时间,销售的数量和获得的收入等。
当我们要分析整个公司的所有销售记录时,毫无疑问,我们最关心的是一共销售了多少?
一共获得了多少收入?
然后更进一步,在某个时间段内销售了多少?
来自哪家供货商的产品的销售额最大?
面向哪种客户的销售额最大?
哪种产品的销售额最大?
等等。
从上面我们关心的这些问题我们可以看到,对于销售的数量和金额这类具体的数字型的数据,通常是我们分析的对象,而对于像时间,产品,客户,供货商,我们希望从这些不同的角度来得到数字型数据的一个统计结果。
所以,我们将数字型的数据存放在facttable中,将时间,产品,客户,供货商存放在不同的dimensiontable中,自然,在facttable和dimensiontable之间存在一个主-外键的关联,各个dimensiontable之间则没有关系。
由此我们可以得到如下的一个starschema:
starschema之所以叫starschema,就是由于上面这个图形的形状来的,facttable处于中间的位置,dimensiontable围成一圈,每个dimensiontable和facttable关联。
Facttable中除了区分每条记录的主键(facttable的主键很有可能是所有dimensiontable的外键组合起来的一个组合主键),连接每个dimensiontable的外键外,就只有我们关心的数字型数据,所以facttable中的每条记录,有个专门的术语称之为度量(measurement),因为我们利用数据仓库做统计分析的时候,这些数据就是统计分析的一个个基本单位,也就是度量值。
显然,starschema是反规范化的。
如果将dimensiontable按照规范化拆开,则starschema演化成了雪花模型(snowflakeschema).规范化减少了数据的冗余,但是由于查询的时候要连接更多的表,性能就会受到影响。
由于数据仓库主要用于查询的特性,除非你有非常特别的原因,一般推荐此采用starschema来进行数据仓库的架构设计。
三.RELYconstraint
数据仓库中的数据,一般是通过ETL定期load进来的。
在做ETL的时候,一般会对load的数据的一致性做检查。
所以,我们有理由认为,数据仓库中的数据都是符合一致性要求的。
既然我们已经可以确定数据仓库中的数据是一致的,那么就可以不在表上建constraint,constraint对DML或者load操作是有性能影响的,能不用当然不用的好。
但是,虽然表中的数据实际上一致了,oracle自己却不知道,优化器也不知道。
在利用物化视图查询重写(queryrewrite)时,constraint和dimension的作用是很大的(一般在数据仓库环境中,query_rewrite_integrity参数设置为trusted),查询重写对数据仓库的性能影响相当大。
为了告诉oracle,数据应该符合某种一致性条件了,而又不想创建的constraint其作用,就可以创建类型为RELY的constraint,也就是一种可以让oracle知道这些数据是符合这些约束的,但这个约束本身却是没有其实际作用的约束。
文档中给出了一个例子:
ALTERTABLEsalesADDCONSTRAINTsales_time_fk
FOREIGNKEY(time_id)REFERENCEStimes(time_id)
RELYDISABLENOVALIDATE;
另外,视图只能创建RELYconstraint,不能创建普通的constraint。
四.Dimension
前面我们提到,除了constraint,另外一个影响物化视图查询重写的重要因素就是dimension。
要理解oracle中的dimension,首先要搞清楚dimension和dimensiontable之间的区别。
dimensiontable是table,和关系数据库中的其他table一样,存放数据,需要实际的存储空间。
而dimension则只是一个逻辑结构,定义了dimensiontable中的一个列或一组列于其他列之间的一个层次关系,dimension只保存定义,可以将其理解为一种特定的constraint。
所以,dimension不是一种必须存在的结构,但是,创建dimension对于数据仓库中一些复杂的查询重写有着相当重要的意义。
而查询重写,则是数据仓库性能优化的一个不二法门。
数据仓库中由于数据量巨大,一些聚合计算等操作往往通过物化视图预先计算存储。
但是,不可能对所有维度的所有可能的聚合操作都建立物化视图,一则空间不允许,二则刷新时间也不允许。
那么,在对某些聚合操作的sql进行查询重写时,就希望能利用已经存在的物化视图,尽管他们的聚合操作条件不完全一致。
而dimension定义的各个level之间的层次关系,对于一些上卷(rollingup)和下钻(drillingdown)操作的查询重写的判断是相当重要的,而dimension中定义的attributes对于使用不同的列来做分组的查询重写起作用。
一个典型的dimension定义如下:
CREATEDIMENSIONproducts_dim
LEVELproductIS(products.prod_id)
LEVELsubcategoryIS(products.prod_subcategory)
LEVELcategoryIS(products.prod_category)
HIERARCHYprod_rollup(
productCHILDOF
subcategoryCHILDOF
category
)
ATTRIBUTEproduct_infoLEVELproductDETERMINES
(products.prod_name,products.prod_desc,
prod_weight_class,prod_unit_of_measure,
prod_pack_size,prod_status,prod_list_price,prod_min_price)
ATTRIBUTEsubcategoryDETERMINES
(prod_subcategory,prod_subcategory_desc)
ATTRIBUTEcategoryDETERMINES
(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,对于雪花模型,dimensiontable可能被规范化为许多的小表,则dimension中的level可能是来自不同表中的列。
这是需要在dimension中指定joinkey来指出各个表之间的关联列。
例如:
CREATEDIMENSIONcustomers_dim
LEVELcustomerIS(customers.cust_id)
LEVELcityIS(customers.cust_city)
LEVELstateIS(customers.cust_state_province)
LEVELcountryIS(countries.country_id)
LEVELsubregionIS(countries.country_subregion)
LEVELregionIS(countries.country_region)
HIERARCHYgeog_rollup(
customerCHILDOF
cityCHILDOF
stateCHILDOF
countryCHILDOF
subregionCHILDOF
region
JOINKEY(customers.country_id)REFERENCEScountry);
如果不指定skipwhennull子句,每个level中都不允许出现null值。
通过dbms_dimension.describe_dimension可以查看dimension的定义。
通过dbms_dimension.validate_dimension可以检查dimension是否定义正确,在执行之前需要执行ultdim.sql创建一个dimension_exceptions表,如果定义有误,则会在dimension_exceptions中查到相应的记录。
在9i里,validate_dimension在dbms_olap包中。
关于dimension,AskTom上有个问题写得比较详尽,值得仔细研究,点此阅读。
五.Bitmapjoinindex
Bitmapindex的主要思想就是,针对每一个可能的值x,建立一个或一组位图映射,每个bit为1代表这个位置的值等于x,为0则不等于x。
而每个位置都可以直接映射到某一行的rowid。
由于在执行DML操作时,锁定的是整个bitmap,而不是bitmap中的某个位,所以bitmapindex对于并发DML的性能很差,而且频繁的DML操作会使得bitmapindex的空间效率大打折扣,所以OLTP系统并不合适使用bitmapindex。
对于基本没有DML操作,有大量adhoc查询的Datawarehouse环境则相当有效。
关于bitmapindex的理解,可以参考itpub上的一篇深入讨论。
从oracle9i起,oracle又引进了一种新的索引类型:
bitmapjoinindex。
和bitmapindex建立在单个table上不同,bitmapjoinindex是基于多表连接的,连接条件要求是等于的内连接(equi-innerjoin)。
对于数据仓库而言,较普遍的是Facttable的外键列和相关的Dimensiontable的主键列的连接操作。
Bitmapjoinindex能够消除查询中的连接操作,因为它实际上已经将连接的结果保存在索引当中了。
而且,相对于在表的连接列上建普通bitmapindex来说,bitmapjoinindex需要更少的存储空间。
同样的基于连接的Metarializedview也可以用来消除连接操作。
但bitmapjoinindex比起物化视图来更有效率,因为通过bitmapjoinindex可以直接将基于索引列的查询对应到事实表的rowid。
以oracle的sampleschemaSH中的sales和customers表做个例子
1.建立基于维度表中一个列的bitmapjoinindex
createbitmapindexsales_cust_gender_bjix
onsales(customers.cust_gender)
fromsales,customers
wheresales.cust_id=customers;
建立这样的bitmapjoinindex后,下面的查询就可以从index中直接得到结果,而不再需要连接sales和custmoers两张表来获得结果了。
相当于根据连接条件,将customers表中的cust_gender列保存到sales表中了。
selectsum(sales.amount_sold)
wheresales.cust_id,customers.cust_id
andcustomers.cust_gender='
M'
;
通过将bitmapjoinindexdump出来可以看到,实际上,索引是按照ustomers.cust_gender分成2个位图,每个位图映射到sales表的ROWID。
所以根据customers.cust_gender来过滤连接结果时,从索引中可以直接得到目标数据在sales中的rowid,无须执行join操作了。
一个可能的执行计划如下:
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=3751Card=1Bytes=9)
10SORT(AGGREGATE)
21PARTITIONRANGE(ALL)
32TABLEACCESS(BYLOCALINDEXROWID)OF'
SALES'
(Cost=3751Card=508136Bytes=4573220)
43BITMAPCONVERSION(TOROWIDS)
54BITMAPINDEX(SINGLEVALUE)OF'
IX_BITMAP'
2.建立基于一个维度表中多个列的bitmapjoinindex
createbitmapindexsales_cust_ms_bjix
onsales(customers.cust_gender,customers.cust_id)
wheresales.cust_id=customers.cust_id;
3.建立多个维度表到一个事实表的bitmapjoinindex
createbitmapindexsales_c_gender_p_cat_bjix
onsales(customers.cust_gender,products.prod_category)
fromsales,customers,products
wheresales.cust_id=customers.cust_id
andsales.prod_id=products.prod_id;
4.建立基于snowflakeschme的bitmapjoinindex
雪花模型的维度表被规范化为多个小表,也就是建index的时候需要额外连接其他几个表
createbitmapindexsales_co_country_name_bjix
onsales(countries.country_name)
fromsales,countries,customers
wheresales.country_id=countries.country_id
andsales.cust_id=customers.cust_id;
Bitmapjoinindex的一些限制条件
∙只支持CBO
∙只能是equi-inner连接,任何外连接都无法使用bitmapjoinindex
∙多个连接条件只能是AND关系
∙只能在facttable上执行并行DML。
如果在dimensiontable上执行并行DML,会导致索引变为unusable状态。
∙不同的事务中,只能并发更新一个表
∙在From字句中,任何一个表都不能出现两次
∙在索引组织表(IOT)和临时表上不能建立bitmapjoinindex
∙索引只能基于dimeniontable中的列
∙维度表用于连接的列只能是主键列或者是有唯一约束的列。
SQL>
createbitmapindexsales_cust_gender_bjix
2onsales(customers.cust_gender)
3fromsales,customers
4wheresales.cust_id=customers.cust_id;
*
ERRORatline3:
ORA-25954:
missingprimarykeyoruniqueconstraintondimension
如果维度表的主键是组合主键,那么连接条件需要是全部主键列都参与其他对于bitmapindex的限制条件同样使用于bitmapjoinindex,比如在分区表上只能是local,不能是global。
六.Uniqueconstraint&
uniqueindex
一般情况下,uniqueconstraint都是通过uniqueindex来实现的。
但是在数据仓库中,由于数据量巨大,建立一个索引可能需要花费相当大的时间和空间,假如查询中又用不上这个索引的话,那么建立索引的高代价却没有带来什么收益,这是很不划算的。
举个例子,假如有一个sales表,其中sales_id的数据是唯一的,我们在sales_id上建一个uniqueconstraint,语法如下:
altertablesalesaddconstraintsales_ukunique(sales_id);
这样建立的uniqueconstraint是enablevalidate状态的,oracle会自动在sales_id列上创建一个的名为sales_uk的uniqueindex。
通过查询user_indexes或者user_ind_columns视图可以看到这个index:
selectindex_name,column_namefromuser_ind_columnswhereindex_name='
SALES_UK'
INDEX_NAMECOLUMN_NAME
------------------------------------------
SALES_UKSALES_ID
在数据仓库环境中,这个uniqueindex可能是不合适的:
1.这个索引可能会相当的大。
2.在查询中几乎不会用到sales_id来做为过滤条件
3.多数情况下,sales会是一个分区表,而且分区键不会是sales_id。
这样这个uniqueindex必须是globalindex,在对分区的一些DDL操作中可能会导致globalindex失效。
那么怎么能在创建uniqueconstraint的同时不生成uniqueindex呢?
很简单,创建一个状态为disablevalidate的uniqueconstraint就能满足上述要求。
altertablesalesaddconstraintsales_ukunique(sales_id)disablevalidate;
再来查询user_ind_columns可以发现没有记录:
SALES_ID'
norowsselected
但是disablevalidate状态的索引会导致无法对该列进行DML操作
deletefromsaleswhererownum=1;
deletefromsaleswhererownum=1
ERRORatline1:
ORA-25128:
Noinsert/update/deleteontablewithconstraint(NING.SALES_UK)disabledandvalidated
那么,要修改有disablevalidate约束的表中的数据,只有以下两种方法:
1.使用DDL操作,比如分区表的exchangepartition
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Data Warehouse Guide阅读笔记 Guide 阅读 笔记