Based On The Key Technologies Of The Aggregate Function Of The Materialized View

It is very important to quickly answer queries in database application system. Besides SQL query optimization, another efficient way is to use materialized views. The definitions and pre-query results of materialized views are both stored in database. When users give their queries against base tables, the database will transparently rewrite these queries by using materialized views, which can not only avoid accessing the huge raw records directly and performing time-consuming computing, but also can save the response time and improve query performance finally.In this paper, the significance of materialized views technology for the database application, with the development history and the present situation of materialized views for the famous commercial databases abroad, is described first. Then, we mainly discuss the selection and query rewriting technology of materialized views. For a given SQL query statement, the first thing database system needs to do is to decide the materialized views that could be used. Generally, materialized views selection is constituted by candidate views filter, cost estimation and optimal configure search. So we will particularly focus on the implement of these three steps, including the detailed rules description and algorithms, which is based on the entire frame of materialized views selection we design first.After the materialized views selection, the problems that how the system uses the selected materialized views to rewrite the the SQL query and selects the optimal one from numerous rewriting strategies for the final performance optimization have been investigated. So in the following part of this paper, the rewriting algorithms for aggregation queries based on conjunctive views and aggregation views have been discussed, and then the optimal rewriting plan selection method based on the binary search tree is proposed.With regard to the detailed design, the existing framework of OSCAR database is extended by adding the materialized views selection module and query rewriting index manager, and is modified by maintaining the metadata of materialized views in data dictionary. As an assistant module, the query rewriting index manager is in charge of constructing and maintaining a multi-pass tree which can index all the available materialized views for the convenience of the realization of query rewriting selection method. Finally, with Shenzhou OSCAR database as the platform, we implement the above key technologies and provide experiments for performance optimization test. The relevant results demonstrate the validity and high effectiveness of the proposed techniques, which can reduce the work load of DBA and optimize the system performance automatically.