JP2005018430A - Database managing system and query optimizing method - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To provide a database managing system and a query optimizing method to perform the much more accurate optimization of a query. <P>SOLUTION: A control part 11 generates a candidate execution plan regarding a predetermined query, executes all of the generated execution plans, acquires the actual costs of each execution plan and selects the execution plan of the minimum costs, and registers it in an optimal execution plan file 122 with the query. The control part 11 receives a query inputted by a user and judges whether or not the query has been registered in the optimal execution plan file 122. When the query has been registered, the control part reads the execution plan registered with the query from the optimal execution plan file 122 and executes it and outputs an execution result. When the query has not been registered in the optimal execution plan file 122, the control part performs general optimization processing and selects and executes an execution plan and outputs the execution result. <P>COPYRIGHT: (C)2005,JPO&NCIPI

Description

[0001]
BACKGROUND OF THE INVENTION
The present invention relates to a database management system (DBMS) and a query optimization method for selecting an optimal execution plan for a query input for a database.
[0002]
[Prior art]
Conventionally, in a general DBMS, when a query (question, query) is input, an optimization process for selecting an efficient procedure (execution plan) for executing the query is performed. This query optimization process is extremely important in database processing. For example, even an query that can be processed within a few seconds if it is an optimal execution plan, the process is tens of times due to an execution plan selection error. It can take a minute.
[0003]
For example, there is a system that optimizes a query by preparing a query that is semantically equivalent to the query and expanding the selection range of execution plans for the query (see Patent Document 1).
[0004]
[Patent Document 1]
JP-A-5-334368 (page 3, FIG. 1)
[0005]
[Problems to be solved by the invention]
In general, in the execution plan optimization process, for each candidate execution plan, statistical information (table size, attribute value distribution, etc.) is used to calculate the estimated value of each cost. Select the plan with the lowest cost estimate.
However, for example, when the data is fragmented in the disk, it is difficult to actually select the optimum execution plan by the method for calculating the estimated cost as described above.
[0006]
The present invention has been made in view of the above circumstances, and an object thereof is to provide a database management system and a query optimization method capable of performing query optimization more reliably.
Another object of the present invention is to provide a database management system and a query optimization method capable of improving performance during database operation.
[0007]
[Means for Solving the Problems]
In order to achieve the above object, a database management system according to the first aspect of the present invention provides:
For a given query, a cost acquisition unit that generates candidate execution plans, executes all the generated execution plans, and acquires the actual cost of each execution plan;
Selecting means for selecting an execution plan with the minimum cost from the costs of all execution plans acquired by the cost acquisition means;
Registration means for associating the inquiry with the execution plan selected by the selection means and registering it in the optimum execution plan file;
Search execution means for searching the database with reference to the optimal execution plan file for the query input by the user;
It is characterized by providing.
[0008]
According to such a configuration, the actual cost is obtained for all execution plans generated as query candidates, and the actual execution system hardware is selected by selecting the execution plan having the lowest actual cost as the optimum execution plan. Inquiries can be reliably optimized based on hardware performance, actual database status, and the like. Further, by using the query execution plan optimized in this way for database search, the performance during database operation is improved.
[0009]
The search execution means includes
Means for accepting inquiries entered by the user;
Means for determining whether or not the inquiry is registered in the optimum execution plan file;
First plan execution means for reading out and executing an execution plan registered in association with the inquiry from the optimum execution plan file when it is determined that the inquiry is registered in the optimum execution plan file;
Means for outputting an execution result by the first plan execution means;
When it is determined that the query is not registered in the optimum execution plan file, a second optimization is executed to perform a general optimization process on the query, select an execution plan, and execute the selected execution plan Means,
Means for outputting an execution result by the second plan execution means;
May be provided.
As a result, the database search is performed using the execution plan selected based on the actual cost, so that the performance during database operation is improved.
[0010]
The cost acquisition means may perform a process of acquiring an actual cost of an execution plan for a predetermined inquiry every predetermined period,
The registration unit may update the registered content with the selected execution plan when an inquiry about the execution plan selected by the selection unit has already been registered.
[0011]
A method according to the second aspect of the present invention is as follows.
A query optimization method in a database management system,
Generate a candidate execution plan for a given query, execute all the generated execution plans, and acquire the actual cost of each execution plan, and all executions acquired by the cost acquisition step A selection step of selecting an execution plan with the lowest cost from the costs of the plan;
A registration step of associating the execution plan selected in the selection step with the inquiry and registering it in an optimal execution plan file;
A search execution step of searching a database with reference to the optimal execution plan file for the query input by the user;
It is characterized by providing.
[0012]
According to such a configuration, the actual cost is obtained for all execution plans generated as query candidates, and the actual execution system hardware is selected by selecting the execution plan having the lowest actual cost as the optimum execution plan. Inquiries can be more reliably optimized based on hardware performance and actual database status. Further, by using the query execution plan optimized in this way for database search, the performance during database operation is improved.
[0013]
The search execution step includes:
Receiving a query entered by the user;
Determining whether the inquiry is registered in the optimal execution plan file; and
When it is determined that the inquiry is registered in the optimum execution plan file, a first plan execution step of reading and executing the execution plan registered in association with the inquiry from the optimum execution plan file;
Outputting an execution result of the first plan execution step;
When it is determined that the query is not registered in the optimum execution plan file, a second optimization is executed to perform a general optimization process on the query, select an execution plan, and execute the selected execution plan Steps,
Outputting an execution result of the second plan execution step;
May be provided.
As a result, the database search is performed using the execution plan selected based on the actual cost, so that the performance during database operation is improved.
[0014]
The cost acquisition step may perform a process of acquiring an actual cost of an execution plan for a predetermined inquiry every predetermined period,
In the registration step, when an inquiry about the execution plan selected in the selection step has already been registered, the registration content may be updated with the selected execution plan.
[0015]
DETAILED DESCRIPTION OF THE INVENTION
Hereinafter, a database management system according to an embodiment of the present invention will be described with reference to the drawings.
[0016]
FIG. 1 shows a functional configuration diagram of the database management system according to the embodiment of the present invention. The database management system 1 includes, for example, a computer including a control unit 11, a storage unit 12, a communication control unit 13, and the like.
The control unit 11 includes a CPU (Central Processing Unit) and the like, and reads and executes an operation program stored in the storage unit 12 to thereby execute a management control unit 111, a syntax analysis unit 112, an optimization processing unit 113, The plan execution unit 114 and the like are logically realized.
[0017]
The management control unit 111 controls execution of inquiry execution processing, preparation processing for inquiry execution processing, and the like.
For example, in the preparation process, the management control unit 111 controls the syntax analysis unit 112, the optimization processing unit 113, and the plan execution unit 114 to perform a syntax analysis process on a prepared process inquiry (query). After the execution, a candidate execution plan is generated and executed, a plan with the lowest actual cost (for example, execution time, etc.) is selected from all candidates, and is associated with the corresponding inquiry in the storage unit 12 The optimal execution plan file 122 is stored. The execution timing of the preparation process can be arbitrarily set. For example, the preparation process may be executed at predetermined intervals using a timer (not shown).
[0018]
In the query execution process, the management control unit 111 controls the syntax analysis unit 112, the optimization processing unit 113, and the plan execution unit 114, and receives a query (query) input by the user that is received from another computer. After performing the parsing process, it is determined whether or not the query is registered in the optimum execution plan file 122. If registered, the execution plan registered in association with the query is taken out, and The database 121 is searched according to the above. When no inquiry is registered, an optimization plan based on general statistical information or the like is performed to select an execution plan, and the database 121 is searched according to the selected execution plan. Then, the search result of the database 121 is returned to the search request source computer. The execution timing of the inquiry execution process is, for example, when a search request including an inquiry is received from another computer via the network.
[0019]
The syntax analysis unit 112 has a parser function and the like, and performs a syntax analysis check on the input query in accordance with an instruction from the management control unit 111.
[0020]
The optimization processing unit 113 has an optimizer function and the like, and determines an optimal execution plan for a query that has undergone a syntax analysis check from the syntax analysis unit 112 in accordance with an instruction from the management control unit 111.
Specifically, when the management control unit 111 instructs to select an optimal execution plan based on the actual cost, a candidate execution plan is generated for the inquiry, and each generated execution plan is displayed as a plan execution unit. 114, the actual cost value is measured, and the actual cost is obtained for all execution plans. For example, when the cost is the execution time of the execution plan, the time from the execution start to the completion of the execution plan is measured. Then, the one with the lowest cost is determined as the optimal execution plan, and the determined optimal execution plan is registered in the optimal execution plan file 122 together with the corresponding inquiry. An example of registration data (inquiry and optimum execution plan) of the optimum execution plan file 122 is shown in FIG.
[0021]
When general management processing is instructed by the management control unit 111, the optimization processing unit 113 generates, for example, candidate execution plans, and statistical information (table size, Based on the distribution of attribute values, etc.), an estimated cost value is calculated, a general optimization process such as selecting an execution plan with the minimum cost value is executed, and the selected execution plan is a plan execution unit. 114.
[0022]
The plan execution unit 114 searches the database according to the procedure indicated by the execution plan passed from the optimization processing unit 113 according to the instruction from the management control unit 111.
[0023]
The storage unit 12 includes a hard disk device or the like, and includes a database 121 and an optimal execution plan file 122. The storage unit 12 stores an operation program executed by the control unit 11 and various data necessary for processing.
The database 121 stores various data to be searched.
The optimum execution plan file 122 stores the inquiry and the optimum execution plan corresponding to the inquiry.
[0024]
The communication control unit 13 is connected to a network and performs data communication with other computers.
Next, the operation of the database management system 1 of this embodiment will be described.
First, the preparation process will be described with reference to the flowchart of FIG.
For example, the management control unit 111 starts a preparation process at a predetermined time interval, retrieves a query to be processed from the storage unit 12 (step S1), and instructs the syntax analysis unit 112 to perform a syntax analysis check of the query. In response to this, the syntax analysis unit 112 performs a syntax analysis check on the inquired query (step S2).
When the query parsing check is completed, the management control unit 111 instructs the optimization processing unit 113 to select an optimal execution plan. In response to this, the optimization processing unit 113 generates a candidate execution plan for the inquiry (step S3), and sequentially passes the generated execution plans to the plan execution unit 114 to execute the actual cost. The measurement process is performed for all execution plans (steps S4 and S5).
Then, the optimization processing unit 113 selects, as the optimal execution plan, the optimal execution plan from the costs obtained by actually executing each execution plan, that is, the lowest cost (step S6). Then, the management control unit 111 registers the optimum execution plan selected by the optimization processing unit 113 in the optimum execution plan file 122 in association with the inquiry (step S7), and ends this process. In step S7, if the corresponding inquiry has already been registered, the registered data is updated.
[0026]
When there are a plurality of queries to be processed, the above preparation process is executed for each query. For example, the management control unit 111 may sequentially execute the preparation processing for a plurality of inquiries set in the storage unit 12. The inquiry about the processing target may be input and set in advance from an input unit (not shown) by an administrator or the like, and the management control unit 111 refers to a past search log or the like to determine a predetermined target. Inquiries may be selected and set according to criteria (for example, a large number of executions).
[0027]
Next, an inquiry execution process for executing an inquiry input by the user using the optimum execution plan obtained by the above preparation process will be described with reference to the flowchart of FIG.
For example, when receiving a search request including a query from another computer via the network (step S11), the management control unit 111 instructs the syntax analysis unit 112 to perform a syntax analysis check on the received query. In response to this, the syntax analysis unit 112 performs a syntax analysis check on the inquired instruction (step S12).
When the query parsing check is completed, the management control unit 111 determines whether the query has been optimized in advance by the preparation process, that is, whether the query is registered in the optimal execution plan file 122 (step S13). ).
When it is determined that the inquiry is registered in the optimum execution plan file 122 (step S13: YES), the management control unit 111 reads out the optimum execution plan corresponding to the inquiry from the optimum execution plan file 122, and executes the plan execution unit. 114 for execution (step S15).
[0028]
If it is determined that the inquiry is not registered in the optimum execution plan file 122 (step S13: NO), the management control unit 111 instructs the optimization processing unit 113 to execute general optimization processing. In response to this, the optimization processing unit 113 generates execution plan candidates, calculates an estimated cost value for each execution plan, selects an execution plan with the lowest cost, passes it to the plan execution unit 114, and executes it. (Steps S14 and S15).
Then, the management control unit 111 transmits the execution result of the search process by the plan execution unit 114 in step S15 to the search request source computer via the network (step S16).
[0029]
As described above, according to the present invention, the actual cost is obtained for all execution plans generated as query candidates, and the execution plan that actually has the minimum cost is selected as the optimal execution plan. Inquiries can be reliably optimized based on the hardware performance of the system and the actual database status. This improves performance during database operation.
[0030]
In the above embodiment, in the query execution process, when the query is not registered in the optimum execution plan file 122, the general optimization process is performed. As in the process, all generated execution plans may be executed, and the execution plan with the lowest cost may be selected. Furthermore, the selected execution plan may be registered in the optimum execution plan file 122 together with the inquiry.
[0031]
Further, the above system configuration is an example. For example, the database 121 may be included in another computer connected to the system via a network.
Further, the functions of this system may be realized by a plurality of computers. For example, a first computer that performs a preparation process and a second computer that performs an inquiry execution process may be included. The database 121 and the optimal execution plan file are stored in the first computer, the second computer, and the first computer. And a second computer connected to the second computer via a network. In this case, the first computer stores the processing program for performing the preparatory processing and necessary data among the functions of the database management system 1 in the storage unit, and the control unit reads the processing program from the storage unit. By executing, a management control unit, a syntax analysis unit, an optimization processing unit, and a plan execution unit for executing the preparation process are realized. The second computer stores, in the storage unit, a processing program and necessary data for performing the query execution process among the functions of the database management system 1, and the control unit reads the processing program from the storage unit. By executing, a management control unit, a syntax analysis unit, an optimization processing unit, and a plan execution unit for executing the preparation process are realized.
[0032]
In the above description, the inquiry is received from another computer via the network. However, the present invention is not limited to this. For example, the inquiry input by the user through an input unit (not shown) provided in the system. The above various processes may be performed.
[0033]
The system of the present invention can be realized using a normal computer system, not a dedicated system. For example, a program for executing the above operation is stored in a computer-readable recording medium (FD, CD-ROM, DVD, etc.) and distributed, and the program is installed in the computer to execute the above processing. The database management system 1 or the like may be configured. Alternatively, it may be stored in a disk device of a server device on a network such as the Internet and downloaded to a computer, for example.
In addition, when the OS realizes the above functions by sharing the OS or jointly with the OS and the application, etc., only the part other than the OS may be stored and distributed in the medium, or may be downloaded to the computer. Good.
[0034]
【The invention's effect】
According to such a configuration, the inquiry can be optimized more reliably.
[Brief description of the drawings]
FIG. 1 is a diagram showing a configuration of a database management system according to an embodiment of the present invention.
FIG. 2 is a diagram illustrating an example of registration data of an optimum execution plan file.
FIG. 3 is a flowchart for explaining preparatory processing.
FIG. 4 is a flowchart for explaining inquiry execution processing;
[Explanation of symbols]
DESCRIPTION OF SYMBOLS 1 Database management system 11 Control part 111 Management control part 112 Syntax analysis part 113 Optimization processing part 114 Plan execution part 12 Storage part 121 Database 122 Optimal execution plan file 13 Communication control part

Claims (6)

A cost acquisition unit that generates candidate execution plans for a predetermined query to the database, executes all the generated execution plans, and acquires the actual cost of each execution plan;
Selecting means for selecting an execution plan with the minimum cost from the costs of all execution plans acquired by the cost acquisition means;
Registration means for associating the inquiry with the execution plan selected by the selection means and registering it in the optimum execution plan file;
Search execution means for searching the database with reference to the optimal execution plan file for the query input by the user;
A database management system comprising: The search execution means includes
Means for accepting inquiries entered by the user;
Means for determining whether or not the inquiry is registered in the optimum execution plan file;
First plan execution means for reading out and executing an execution plan registered in association with the inquiry from the optimum execution plan file when it is determined that the inquiry is registered in the optimum execution plan file;
Means for outputting an execution result by the first plan execution means;
When it is determined that the query is not registered in the optimum execution plan file, a second optimization is executed to perform a general optimization process on the query, select an execution plan, and execute the selected execution plan Means,
Means for outputting an execution result by the second plan execution means;
The database management system according to claim 1, further comprising: The cost acquisition means performs a process of acquiring an actual cost of an execution plan for a predetermined inquiry every predetermined period,
The registration unit updates the registered content with the selected execution plan when an inquiry about the execution plan selected by the selection unit is already registered.
The database management system according to claim 1 or 2, characterized by the above. A query optimization method in a database management system,
Generate a candidate execution plan for a given query, execute all the generated execution plans, and acquire the actual cost of each execution plan, and all executions acquired by the cost acquisition step A selection step of selecting an execution plan with the lowest cost from the costs of the plan;
A registration step of associating the execution plan selected in the selection step with the inquiry and registering it in an optimal execution plan file;
A search execution step of searching a database with reference to the optimal execution plan file for the query input by the user;
A query optimization method characterized by comprising: The search execution step includes:
Receiving a query entered by the user;
Determining whether the inquiry is registered in the optimal execution plan file; and
When it is determined that the inquiry is registered in the optimum execution plan file, a first plan execution step of reading and executing the execution plan registered in association with the inquiry from the optimum execution plan file;
Outputting an execution result of the first plan execution step;
When it is determined that the query is not registered in the optimum execution plan file, a second optimization is executed to perform a general optimization process on the query, select an execution plan, and execute the selected execution plan Steps,
Outputting an execution result of the second plan execution step;
The query optimization method according to claim 4, further comprising: The cost acquisition step performs a process of acquiring an actual cost of an execution plan for a predetermined inquiry every predetermined period,
In the registration step, when an inquiry about the execution plan selected in the selection step is already registered, the registration content is updated with the selected execution plan.
6. The inquiry optimization method according to claim 4, wherein the inquiry is optimized.

Images