The Study And Application Of Query Optimization Technology Based On Automatic Summary Table

Recent years have witnessed the rapid development in the area of the decision-support queries in commercial databases.These queries generally perform over huge amounts of data (many Terabytes), performing multiple joins and complex aggregation. In addition, they are becoming increasingly interactive, requiring response times in the order of seconds. Traditional optimization techniques often fail to meet these new requirements. It has become a major technology problem to optimize the query whose information resources change frequently and to meet the required response times in order of seconds.The paper firstly develops the definition of atomic viewpoint eliminating function between attributes,and further classifies the atomic viewpoint into two kinds:Differentiation Perspective and Aggregation Perspective. The granularity of Differentiation Perspective is also analyzed in detail. Extensible Query Graph Model is used to seek candidate Automatic Summary Table (AST) and plan the AST according to matching function, pull-up and push-down rules. The system Individual user queries can then be optimized by accessing the ASTs instead of the raw data. Furthermore, the key issues of how to performing AST technology in the database and C# development environment are also introduced. The practical application in large scale ferry ticketing system has proved that this technology has improved the query optimization dramatically.The AST is a technology which trades space for time, especially designed for the aggregation query, apportions the aggregation time into daily transaction time which can be ignored, and aggregate the data with same atomic viewpoint using database function. The technology enriches current theories and provides meaningful guidance for large-scale report query optimization.