The Research And Application Of Database Value-based Query Optimization

Query optimization is an important technique for designing and implementing database system. It is a crucial factor that affects the capability of database. The great success of relational database and SQL mainly benefits from the development of query optimization technology. Searching an optimized executive strategy for every complex query is the key of success for database development. In database field, it is still an N-P problem. In the process of query, ineffective SQL, shortage of statistics and incorrect join order are all the reasons for lowering the query rate. Based on the statistics dictionary and the model, the value-based query optimization is to rewrite the ineffective SQL and select the best join order for multi-tables.According to the factual problem of query efficiency in Chinese Cognitive Database and Heilongjiang Decision-making and Supporting System, this paper analyzes detailedly the concretely executive process of SQL statement reference to the characteristic of database query. Searching the approach of improving the query efficiency in mass data query and the creation of data-warehouse, there are several schemes, such as "automating statistics management for query optimizers", "rewriting the SQL statement based on statistics".At the same time, this paper is not limited in a project and will apply these schemes to the similar database model. In this foundation, Particle Swarm Optimization (PSO), a new evolutive compute model is introduced to query optimization to construct a particle swarm model in the state space of query strategy, and to optimize the join operations using PSO. Experiments indicate that the new method is better than traditional methods in searching the best executive strategy. Finally, combining theory and practices, it is demonstrated that the optimized strategy above is applied to practical projects, "Chinese Cognitive Database Management System" and "Heilongjiang Decision-making and Supporting System". It has improved users' query efficiency indeed.