OLAP系统的查询性能研究

【作者】 洪佳；

【导师】 刘烨；

【作者基本信息】 天津工业大学 ， 管理科学与工程， 2007， 硕士

【摘要】 数据仓库和联机分析处理(OLAP)技术已经广泛地应用于各行各业中，很好地满足了领导层的决策需要。如何提高数据仓库环境下的查询效率是当前数据仓库研究的一个核心问题。利用索引技术是提高查询性能重要的方法之一。目前，在对利用索引提高查询性能的研究中，没有对影响查询性能的因素进行全面考察，因此提出的索引建立策略有很大的盲目性。本文对影响数据仓库查询性能的因素进行了较全面的分析与研究，将因素归结为两类，一类因素是由数据的组织特征和用户的查询需求特征决定的，另一类因素是索引类型。不同的数据组织特征和用户查询需求以及不同的索引策略决定了查询性能优劣，合理的索引设计必须要建立在对各种查询的分析和预测以及数据组织的特点上。本文通过实验分析和研究，综合考虑在这两类因素影响下获得最佳查询性能的索引策略，通过在Oracle 9i环境下对两类因素组合进行的查询性能实验，探讨了数据组织特征以及用户的查询需求和索引类型选择之间的关系。实验结果表明位图索引很适合建立在事实表外键上以及具有低基数度的维度表非主属性列上，B树索引适合建立在维度表主键上。对于查询中经常出现的维度表非主属性列，在事实表上建立基于其的位图连接索引是个很好的选择。此外，对事实表越大、查询越复杂的数据仓库环境而言，位图索引改善查询性能的作用越显著。根据实验结果提出了一套数据仓库中的索引设计策略，并将此策略应用到行政许可审批OLAP系统的索引设计中。实践证明，合理的索引策略能有效提高数据仓库系统的查询效率，从而使系统的及时响应性得到了明显的改进和提高。本文提出的数据仓库索引设计策略具有较普遍的指导意义，对其它OLAP系统查询性能的改善起到了很好的借鉴作用。更多还原

【Abstract】 Nowadays, data warehouse and OLAP technique have been widely used in various business enterprises; it meets the requirement of decision of leaders well. How to improve the inquiry efficiency in data warehouse environment is one of the core problems of current studies of data warehouse. Making use of the index technology is one of the important methods to improve query performance. At present, in the research of make use of index, factors which affect query performance are not be examined roundly, so the index build tactics have very big blindness.Factors that affect query performance are analyzed roundly in this paper. The factors are divided into two kinds: one kind of factors is decided by data organization characteristic and users’ inquiry needs, the other kind of factors is the type of index. Different data organization characteristic and users’ inquiry needs and different index tactics determines advantage and disadvantage of performance. The rational index design must build on the analysis and forecast of various inquiries and the consideration of data organization characteristic. This paper considers the best index tactics that under the effect of two kinds of factors through experiment analysis and research. Via the query experiment of sorts factors under Oracle 9i environment, the relationship between data organization characteristic and users’ inquiry needs and the choice of index is discussed. The result shows bitmap index is fit to build on the foreign key of fact table and non-primary attribute columns that have low creativity of dimension tables. B tree index is fit to build in the primary key of dimension table. Given the column that often appears in query, it is a good choice to build bitmap join index on it. Besides, the bigger the fact table, the more complicated query, the more notable effect of improves query performance by bitmap index. According to the result of experiment, a set of index design tactics in data warehouse is presented. Furthermore, the index design tactics is applied to the index design of Administration Permit Approval OLAP system. Practice has testified, the proper index has improved systematic inquiry efficiency. It can improve timely responsibility of the system. The index design tactics of data warehouse that be presented in the paper have more common guiding significance. This is a good use for reference of the improvement of query performance of other OLAP systems.更多还原