JPH09231115A - Data base processing method - Google Patents

Abstract

PROBLEM TO BE SOLVED: To execute optimization corresponding to a situation at the time of execution by selecting a unique access method at the time of executing an inquiry from the access methods. SOLUTION: A plan candidate generation part 12 models an access means based on the analysis result of the inquiry by an inquiry analysis part 11 and generates a plan for showing a database processing procedure. At the time of executing a processing, information contained in the plan is evaluated and the final access method is decided. A database management system DBMS1 generates the plural plans in the plan candidate generation part 12 if necessary as the result of the inquiry analysis part 11 and selects one plan 3 which is most inexpensive in an optimum plan selection part 13. Namely, the plan that the minimum storage area and CPU use rate can be reduced is selected. At the time of actually executing the plan 3, a value is set in the variable 'HV' of the inquiry 2, and DBMS1 executes a database processing by an execution time processing part 14.

Description

Detailed Description of the Invention

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a database management system, and more particularly to a database processing method suitable for query processing of a relational database management system.

[0002]

2. Description of the Related Art In order to efficiently perform a database access process (hereinafter, simply referred to as "database process"), a database management system that performs a process called optimization has become common. In the conventional optimization process of database access (query), when a variable exists in the query condition, an access request to a certain database is requested in order to avoid that the optimization has no effect depending on the value assigned at execution time. On the other hand, a plurality of access processing procedures are generated as a plan, the cost of each processing procedure is evaluated, and then one or a plurality of processing procedures judged to be efficient are generated. And after setting the concrete value of the variable when executing database processing,
Database processing is executed according to the generated processing procedure. In addition, when multiple processing procedures are generated, the processing procedure that is judged to be the most efficient among the conditions is selected as the technology to select only one procedure according to a certain selection condition and realize efficient execution. The technology to select and execute is JP-A-1
Japanese Patent Laid-Open No. 6-4587 discloses a technique in which all processing procedures are simultaneously executed (on a multiprocessor) and a result obtained first is selected.

[0003]

As described above, conventionally, until the actual database processing is executed, such as when a variable (host variable or dynamic parameter) is included in a query to the database. It was difficult to optimize the database processing including indeterminate elements.
Therefore, as in the prior art described above, multiple database processing procedures are generated in consideration of all possibilities that undetermined elements can be taken at the time of optimization, and the optimal database processing can be performed from them at the time of execution. It was picked out and executed, or all database processing was executed at the same time.

However, even in these processing methods, there is a large area for storing a plurality of database processing procedures, and it is systematic from the viewpoint of selecting one from a plurality of database processing procedures and executing them simultaneously. For example, there were problems that the total amount of processing increased and that the applicable range was limited to the variables being inquired.

The present invention has been made to solve the above problems, and its purpose is to optimize not only variables specified in access requests but also other optimizations without degrading the performance of database access processing. An object of the present invention is to provide a database processing method that realizes optimization taking into consideration factors that are not determined in the case of.

[0006]

In order to achieve the above object, a database processing method according to the present invention analyzes the contents of a database access request and performs optimization processing to access the database. In the database management system that determines only one internal processing procedure for the database access, before the database access is executed, the model creation step for modeling the access means for accessing the database and the access request before the database access are executed. A plan creation step that creates a plan that includes data access conditions from the contents, and an access means selection step that evaluates the contents of the plan and selects the optimum access means from the modeled access means when executing database access. Access by the access method And having a processing execution step of performing management.

Further, the access means selecting step is as follows.
It is characterized in that the most efficient access means is selected based on the relationship between the selection rate of access target data and the processing time.

Further, the plan candidate creating step is characterized in that the plan is created in consideration of the updatable state of the data to be accessed.

Further, the method further comprises a step of checking a data update enable / disable state at the time of executing the database access, and the access means selecting step selects an access means according to the data update enable / disable state.

Further, the access means selecting step is as follows.
It is characterized in that the most efficient access means is selected on the basis of the relation between the selection rate of access target data and the processing time and the updatable state.

Further, a part or all of the database access processing is executed in an additional type database processing dedicated apparatus.

In the present invention as described above, when the query to the database is optimized and the database processing procedure is generated, the access means for accessing each table constituting the database is modeled, Generate a plan that is the only database processing procedure that is loosely optimized without specifying the access path of the table. Then, at the time of executing the database processing, by analyzing the parameters determined at the time of execution for each table and determining the most specific access means from the modeled access means included in the plan, This is for optimally executing database processing.

That is, in the optimization processing of the present invention, since only one database processing procedure is finally generated,
Optimization between a plurality of database processing procedures having completely different processing procedure structures is performed at compile time. However, in normal database processing, even if a database processing procedure with a different structure of processing procedure can be selected at the time of execution, the characteristics of the database do not change dramatically, so there are very few queries that can be effective. It is a thing. Therefore, according to the present invention, by focusing on the substantial optimization, the wasteful storage area and C
PU usage can be reduced and the range of application can be expanded.

[0014]

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS Preferred embodiments according to the present invention will be described below with reference to the drawings.

Embodiment 1 FIG. 1 is a diagram used to explain the outline of the operation of a database management system (hereinafter referred to as “DBMS”) that uses an embodiment of the database processing method according to the present invention. The database language for accessing the DBMS 1 is usually CO
It is embedded in a programming language such as BOL language which is processed and executed by a host computer, or dynamically generated when the program is executed like dynamic SQL. In the present embodiment, the case where SQL is used as the database language will be described. The DBMS 1 processes a database access request command (hereinafter, referred to as "query") 2 by an SQL statement as shown in FIG. Of course, not only this example but also other commands and other database languages may be used.

In response to such a query 2, the DBMS 1 is compiled at the same time as the program is compiled if it is a query embedded in a programming language.
If it is QL, compilation is performed when the PREPARE statement is executed. This compile processing is performed by the compile time processing unit 10. This compile time processing unit 10
Is a query analysis unit 11 that analyzes the access processing to the database specified by the access request, a plan candidate creation unit 12 that creates at least one plan based on the analysis result, and the lowest cost from the created plans. And the optimum plan selecting unit 13 that selects only one plan that performs each process. Since the compile process is usually performed before the actual database access is executed, a variable (in the example shown in FIG. 1, “: H
If V ″) exists, the optimum processing procedure cannot be determined at this point.

Therefore, the plan candidate creating unit 12 creates a plan representing the modeling of the access means and the database processing procedure based on the analysis result of the inquiry by the inquiry analyzing unit 11. That is, in the present embodiment, the access means for reading data from the table 41 of the database 4, that is, the access method here is modeled without determining the final access means at the time of compilation. Create only plans that include the information needed to determine access methods. Then, it is characterized in that the final access method is determined by evaluating the information included in the plan when the processing is executed. In other words, a tight optimization of performing a loose optimization at the compile time and determining the final access method at the time of processing is performed.

The plan candidate creation unit 1 in this embodiment
2 creates the plan 3 having the structure shown in FIG. 1 as follows. In the example below, the access request is
The case of a data read request from a database such as the inquiry command (SELECT statement) shown in FIG.

First, in the processing procedure 31 in which the database processing procedure is shown in a tree structure, a portion for reading data from the table 41 in the database 4, that is, a reading access method is modeled, and is simply expressed as a reading processing here. deep. The modeled access method will be described later. Since only the specific access method for this read process is decided at runtime, here
As the read condition 32 required when reading data in the query 2, the access target column that needs to be sorted (sort designation) and the access target column that needs to be selected (selection designation) are extracted and stored as the plan 3. To do. As for the column designated for sorting, the sorting information 33 holds information such as whether the sorting in each column is ascending or descending. As for the selected and designated column, the selection information 34 holds statistical information such as the number of data in each column and information such as a selection rate indicating the proportion of the access target data obtained from the selection condition designated in the inquiry 2. At this time, when a variable is used as the selection condition in the inquiry 2, the selection rate also takes a variable value including the variable.

As described above, the DBMS 1 generates a plurality of plans in the plan candidate creating unit 12 if necessary as a result of the query analysis unit 11 and selects only one plan 3 having the lowest cost in the optimum plan selecting unit 13. To do. In other words, the plan that maximizes the efficiency of resources so as to reduce the minimum storage area and the CPU usage rate is selected.

When the plan 3 is actually executed, a value is also set in the variable ": HV" of the inquiry 2 and the DBM is set.
In S1, the runtime processing unit 14 performs database processing. The runtime processing unit 14 includes an access method selection unit 15 that evaluates data read conditions and selects an optimum access method, and a process execution unit 16 that actually performs access processing by the selected access method. .

First, the access method selection unit 15 determines the access method of each read process in the processing procedure 31 with reference to the data read conditions of the sort information 33 and the selection information 34 included in the plan 3. This determination method will be described later. When the access method is determined, the process execution unit 16 actually performs the read process from the table 41 on the database 4 according to the access method according to the process procedure 31 in the plan 3 to execute the database process.

FIG. 2 is a DBM to which this embodiment is applied.
It is a block diagram showing an example of a hardware configuration of S. Specifically, CP as found in general computers
It is composed of a U51, a main storage device 50, and a disk 53, and the disk 53 is a CPU 51 via a channel 52.
And the main storage device 50. The DBMS 1, the inquiry 2, the plan 3, and the database 4, which are the respective constituent elements in FIG. 1, are arranged in the disk 53 and the main storage device 50 in FIG. 2, and are actually processed by the CPU 51. When data is transferred between the disk 53 and the main storage device 50, the process is relayed by the channel 52.

The modeled access method will now be described. FIG. 3 is a diagram showing an example of an access method in which a part of a process of actually reading a table from the database 4 is modeled. In the present embodiment, the processing content will be specifically described for the following six types of access methods.

First, as shown in FIG. 3 (a), a sequential scan for sequentially reading the entire table from the database 4 at the highest speed, as shown in FIG. 3 (b),
Sequential scan & selection, in which the selection condition is judged and only the data matching the condition is read out, Fig. 3
As shown in (c), an index is added to the table, and the index scan is read through the index when the index can be applied to a sort or selection condition.
As shown in (d), when a selection condition is specified for another column, index scan & selection is performed to read out only the data that matches the selection condition.
As shown in (e), when a plurality of selection conditions or sort designations are made, only data that meets the conditions is read by sequential scan and the sorted result is used as intermediate data in sequential scan & selective sort, and
As shown in FIG. 3F, there is an index scan & selective sort in which an index scan is performed instead of the sequential scan. In the present embodiment, the modeled read process is replaced with any one of the six types of access methods and optimally executed.

Next, a procedure for determining only one access method from the modeled access methods will be described.

First, FIG. 4 is an example of a characteristic diagram showing how the above six types of access methods change the processing time depending on the selectivity. In addition, (a) -in FIG.
(F) corresponds to the symbol attached to each access method shown in FIG. The sequential scan of (a) shows a constant value because all the data must be read without affecting the selectivity. Similarly, in the sequential scan & selection of (b), the selection process cannot be performed unless all the data is read out, and thus the characteristic is almost the same as that of (a). In the index scan of (c), since only the data that meets the selection condition is read out by the index, the index scan shows a value proportional to the selection rate. (D) Index scan & selection,
It exhibits almost the same characteristics as (c). In the sequential scan & selection sort of (e), all the data is read once by the sequential scan & selection of (b), and further sorting is performed on the data that meets the conditions.
The characteristic of upward rising is shown with (b) value + α as an initial value.
In the index scan & selective sort of (f), necessary data is once read by the index scan & select of (d), and further sorting is performed on the data that meets the condition. Therefore, the value + (β) is the initial value. As shown in FIG.

FIG. 5 is a diagram showing, in a table format, which access method should be used for the read process depending on how the sort information 33 and the selection information 34 are specified in the read condition 32 of the plan 3. is there. In the present embodiment, the selection designation and the sort designation are each divided into three types of “absence”, “present (single)”, and “present (duplicate)”, and nine combinations of the respective conditions (a) They are categorized as ~ (ri). However, "absence" and "presence" here mean that there is a selection designation or sort designation, and that an index is added to the designated column. Also,
"(Single)" indicates that there is only one designation,
“(Duplicate)” indicates that there are multiple designations. And when determining the access method based on these nine conditions,
Considering the characteristics of the selection rate and the processing time of FIG. 4, the most efficient access method is determined when executing the query 2.

FIG. 6 shows a flow chart of an access method selection process of selecting and deciding only one access method from the access methods modeled by the DBMS at the time of executing the above query. In the access method selection process, the presence or absence of sort designation is first determined, and then the presence or absence of selection designation is determined according to this flowchart.
Finally, with reference to FIG. 5, the most efficient access method is determined according to the selectivity obtained on the way. The access method determination process using FIG. 5 constitutes a part of the database processing method according to the present embodiment.

For example, when the selection designation is "Yes (single)" and the sort designation is "Yes (multiple)" in FIG.
Two access methods, (e) sequential scan & selective sort or (f) index scan & selective sort, which can be sorted by a plurality of columns, are candidates. From the characteristics of (e) and (f) shown in FIG. 4, it can be seen that (f) is faster when the selectivity is less than about 25% and (e) is faster when the selectivity is higher than that. Therefore, the access method is determined as shown in FIG. As a result, it becomes possible to select the access method most suitable for the condition at the time of executing the database processing, and the efficient database processing is realized.

Needless to say, when the sort column is not indexed in the flowchart of FIG. 6 and the sort designation is “none”, the sort is executed outside the range of the access method.

As described above, according to the present embodiment, even if a parameter that may change dynamically at the time of query execution is included, access that is narrowed down to some extent by loose optimization before query execution is performed. Since only one access method can be selected from among the methods at the time of query execution, optimization can be performed according to the situation at the time of execution.

Also, since loose optimization is performed before query execution and necessary information is narrowed down to some extent, resources are not wastefully consumed, and therefore optimization processing does not deteriorate performance.

As parameters that may dynamically change during query execution, query selection conditions (dynamic parameters, host variables, etc.), updatability (transaction setting statements, LOCK mode, etc.), hardware resources ( Disk capacity, memory, etc.), statistical information (database structure, characteristics, etc.).

Embodiment 2 Next, a second embodiment of the present invention will be described with reference to FIGS. In the above-described first embodiment, the case of the search process of reading data as the access process has been described, but the present embodiment further includes the update process of writing data.

In a database language such as SQL, it is possible to explicitly specify whether or not a query can be updated by cursor specification or the like. Generally, the procedure for processing a query that can be updated is Because they are different, compile-time optimization also considers updatability. Also, even if the cursor can be updated with the cursor specified when the query is compiled, read-only is specified as the transaction attribute, write permission is revoked by privilege management, etc. The condition is different from that at compile time, and it is not always possible to optimize.

Therefore, in this embodiment, the efficiency at the time of execution is increased by modeling the access method of the database in the update processing. Further, it is characterized in that the access method is determined by further examining the availability of data update during execution of update processing.

FIG. 7 shows the plan 130 of the present embodiment, which is the plan 3 shown in FIG. 1 of the first embodiment.
It is the one extracted the part corresponding to. Here, the plan is different from the plan in the first embodiment in that the read condition 132 includes a data access condition “update designation” indicating a data updatable state. In the update specification, if the update is specified during the inquiry, the update specification is set to "Yes", otherwise the update specification is "No".
By doing so, it can be used as a judgment material when deciding the access method at the time of execution.

FIG. 8 is an access method determination table of this embodiment, which corresponds to FIG. 5 of the first embodiment. It is the same that both selection designation and sort designation are categorized into three types of "no", "yes (single)" and "yes (duplicate)". The difference is that they are classified into 18 types. In FIG. 8, each of the nine types of fields is divided by diagonal lines, and the upper left corner is “updated”,
The lower right is shown as "no update".

At the time of executing the database processing, the sort designation and selection designation columns are determined and the access method is determined in accordance with a processing procedure substantially similar to the flowchart of the access method selection processing shown in FIG. In this case, in addition to the update designation (“present” or “absent”) held in the read condition 132 of the plan 130, it is possible to further match the updateable / non-updatable state of the transaction attribute and the authority. Select “with update” or “without update” of to determine the access method.

However, when (e) or (f) is selected as the access method, mapping with the original data becomes difficult because the intermediate data is created. Therefore, in the present embodiment, when the update is “present” ( The access methods of e) and (f) are not selected ((f), (yo)). So, for example,
In FIG. 8, when the selection designation and the sort designation are both “Yes (single)” ((F)), the access method is changed depending on the selection rate in “No update”, but the access method is changed to “(c)” in “With update”. ) Fixed. In the present embodiment,
Inquiries that have multiple sort specifications are not updated (error) ((ta), (re), (so)).

By defining the access method determination table as shown in FIG. 8 in this way, the access method is considered in consideration of the above-mentioned update-disabled state in addition to the relationship between the selectivity and the processing time. Because you can make a decision
Further, since unconfirmed items such as the authority not included in the inquiry can be taken into consideration, the original optimization can be achieved also in the update processing, and the efficient database processing can be realized.

Embodiment 3. Next, a third embodiment of the present invention will be described with reference to FIG. The present embodiment is an extension of the hardware configuration of the first embodiment. In the first and second embodiments, the database processing is made efficient by modeling the read or write part and selecting the optimum access method at the time of execution. On the other hand, in the present embodiment, the input / output of the database processor (database engine) which is an additional-type database dedicated device is added to the modeling of the database access process.

FIG. 9 shows the hardware configuration of the present embodiment, and corresponds to FIG. 2 in the first embodiment. Here, as the additional database engine, the disk-integrated database engine 154 shown in FIG. 9A and the disk-separated database engine 254 shown in FIG. 9B can be considered.
In this embodiment, both cases can be handled in the same way. When considering the access method modeled in these hardware configurations, the model performed by the database engine is added to the model example of the access method shown in FIG. 3, and the processing time characteristics shown in FIG. Create a corresponding access method decision table. Then, if only one access method is determined by the procedure described above and the access method related to the database engine is included, by executing some or all of the database access according to the access method by the database engine, Optimal execution can be performed even in the configuration including the database engine as in the embodiment.

[Brief description of drawings]

FIG. 1 is a diagram used for explaining an outline of operation of a database management system using a first embodiment of a database processing method according to the present invention.

FIG. 2 is a block diagram showing an example of a hardware configuration of a database management system to which the first embodiment is applied.

FIG. 3 is a diagram showing an example of modeling a database access method according to the first embodiment.

FIG. 4 is a diagram showing an example of the relationship between the selection rate and the processing time for each access method modeled in the first embodiment.

FIG. 5 is a diagram showing a table used for determining an access method used in database processing in the first embodiment.

FIG. 6 is a flowchart showing a process in which the database management system determines an access method when a database process is executed in the first embodiment.

FIG. 7 is a diagram used to explain the outline of the operation of the database management system using the second embodiment of the database processing method according to the present invention, and is an outline showing an example of the structure of a plan in the present embodiment. It is a figure.

FIG. 8 is a diagram showing a table used for determining an access method used in database processing in the second embodiment.

FIG. 9 is a block diagram showing an example of a hardware configuration of a database management system that uses a third embodiment of a database processing method according to the present invention.

[Explanation of symbols]

1 database management system (DBMS), 2 queries, 3,130 plans, 4 databases, 10 compile time processing unit, 11 query analysis unit, 12 plan candidate creating unit, 13 optimal plan selecting unit, 14 runtime processing unit, 15 access Method selection section, 16 processing execution section,
31, 131 processing procedure, 32, 132 read condition,
33,133 sort information, 34,134 selection information,
41 tables, 42 statistical information, 50, 150, 250
Main storage device, 51, 151, 251 CPU, 52,
152,252 channels, 53,253 disks,
154,254 DB engine.

Claims (6)

[Claims] 1. A database management system that analyzes the contents of a database access request and determines only one internal processing procedure for accessing the database by performing optimization processing, before executing database access. A model creation step that models the access means for accessing the database, a plan creation step that creates a plan that includes data access conditions from the contents of the access request before the database access is executed, and a plan creation step when the database access is executed. The method comprises: an access means selection step of evaluating the contents of the above and selecting an optimum access means from the modeled access means; and a processing execution step of actually performing the access processing by the selected access means. Database processing method. 2. The database processing method according to claim 1, wherein the access means selecting step selects the most efficient access means based on a relationship between a selection rate of access target data and processing time. How to process the database. 3. The database processing method according to claim 1, wherein the plan creating step creates a plan in consideration of an updatable state of the data to be accessed. 4. The database processing method according to claim 1, further comprising a step of checking a data update enabled / disabled state at the time of executing a database access, wherein the access means selecting step is performed according to the data update enabled / disabled state. A database processing method characterized by selecting an access means. 5. The database processing method according to claim 3, wherein the access means selecting step is the most efficient access means based on a relationship between a selection rate of access target data and processing time and an updatable state. A database processing method characterized by selecting. 6. The database processing method according to claim 1, wherein a part or all of the database access processing is executed by an additional database processing dedicated device.