Olap Query Performance Study

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.