Query optimization Technology of database

Database system is the core of MIS, and online transaction processing (OLTP) and online analytical Processing (OLAP) based on database is one of the most important computer applications in banks, enterprises and government departments. From the application examples of most systems, query operation occupies the most proportion in various database operations, and the SELECT statement based on query operation is the most expensive statement in SQL statement. For example, if the amount of data accumulated to a certain extent, such as a bank's account database table information accumulated to millions or even thousands records, a full table scan often takes 10 minutes, or even hours. If you adopt a better query strategy than full table scan, you can often reduce the query time to a few minutes, thus the importance of query optimization technology.
The author found in the implementation of the application project, many programmers in the use of some front-end database development tools (such as PowerBuilder, Delphi, etc.) to develop database applications, only pay attention to the gorgeous user interface, do not pay attention to the efficiency of query statements, resulting in the development of the application system inefficient, The waste of resources is serious. Therefore, how to design efficient and reasonable query statement is very important. Based on the application example and database theory, this paper introduces the application of query optimization technology in the real system.
Analyze problems
Many programmers think that query optimization is the task of DBMS (database management System), which is not related to the SQL statements written by programmers, which is wrong. A good query plan can often improve the performance of the program by dozens of times times. A query plan is a collection of SQL statements that a user submits, and a query plan is a collection of statements that are produced after an optimized process. The process of the DBMS processing the query plan is as follows: After the lexical and grammatical checking of the query sentence, the statement is submitted to the query optimizer of the DBMS, after the optimizer finishes the optimization of algebraic optimization and access path, the statement is processed by the precompiled module and the query plan is generated. It is then submitted to the system for execution at the right time, and the execution results are returned to the user at the end. In the high version of the actual database products, such as Oracle, Sybase, and so on, the cost-based optimization method is used to estimate the cost of different query plans based on the information obtained from the System Dictionary table, and then choose a better plan. Although the current database products in query optimization has been doing better, but the user-submitted SQL statements is the basis for system optimization, it is difficult to imagine a bad query plan after the system optimization will become efficient, so users write the pros and cons of the sentence is critical. Query optimization for the system we do not discuss, the following highlights to improve the user query plan solution.
　
Solve the problem
The following is an example of a relational database system, Informix, that improves the user query plan.
1. Rational use of indexes
Index is an important data structure in database, and its basic aim is to improve the efficiency of query. Most of the database products now use IBM's first proposed ISAM index structure. The use of indexes is just right, with the following principles: