Materialised views performance in a data warehouse environment

Abstract

A reason for the existence of a Data Warehouse is its utility for decision support. Their queries over a Data Warehouse are complex, expensive, and their response time can be more than available. An aid to query optimisation is view materialisation. A case study based on TPC-D’s structures, modality and sixteen of its queries are used to give a quantitative measure of the performance of cost based optimisation versus materialised view based optimisation. Fourteen queries’ elapsed time show an improvement (an average of 27 times) when comparing a query’s performance on the materialised views to that on base tables. A similar improvement is found in the amount of logical reads generated. The one time cost of materialising reaches a break even (for many of the queries) by the fifth use.