JPH10326216A - Data base management system and inquiry processing method - Google Patents

Abstract

PROBLEM TO BE SOLVED: To accelerate an inquiring process by providing a 2nd node which performs a data base operating process as to sent-out data and outputs the result and equipping this 2nd node with a halfway result storage means which temporarily stores a halfway result of the data base operating process. SOLUTION: This system has 1st nodes which take stored data out and sends them out at extraction requests, the 2nd node which performs the data base operating process as to the data sent out of the 1st nodes and outputs the result at an inputted data base operation request. This 2nd node is equipped with a halfway result storage means which temporarily stores a halfway result of the data base operating process. In this system, a decision means equally allocates the distribution of extracted information to coupled nodes 94 to 96 to the respective coupled nodes 94 to 96 according to the predicted amounts of extracted information at determined distribution nodes 90 to 93.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

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

[0002]

2. Description of the Related Art A database management system (hereinafter referred to as DBM)
S), in particular, a relational DBMS processes a query expressed in a non-procedural language, determines an internal processing procedure, and executes it according to the internal processing procedure. SQL is used as this database language (Database L
anguage SQL ISO 9075: 1989). The main method of the conventional query processing is to determine a single internal processing procedure based on a preset rule, and from a plurality of candidate processing procedures selected using various statistical information, by cost evaluation, Some determine what seems optimal. In the former, although the load for creating the processing procedure is small, there is a problem in the validity of uniformly set rules, and also in the optimumness of the selected internal processing procedure. The latter manages various kinds of statistical information, creates a plurality of candidate processing procedures, calculates a load for cost evaluation thereof, and gives an optimal processing procedure. For example, Satoh, K., et.al. "
Localand Global Optimization Mechanisms for Relati
onal Database ", Proc. VLDB, 1985. In the related art, a processing procedure is determined by estimating a data amount from a query condition.

In many DBMSs, query processing is realized through two-phase processing of query analysis processing and query execution processing. Host languages (COBOL, PL / I
When the query language is incorporated into the application program, the query is analyzed beforehand by executing the application program, and one internal processing procedure, which is an execution format, is created. In this query expression, in many cases, variables in the host language are described in the search condition expression. The constant is assigned to this variable when the internal processing procedure resulting from the query analysis processing is executed, that is, when the query is executed. The problem in this case is that a plurality of optimal processing procedures can be considered according to the values assigned to the variables. In order to solve this problem, there is a method in which a plurality of processing procedures are created at the time of query execution processing, and the processing procedure is selected according to a value assigned to a variable at the time of query execution. Japanese Patent Application Laid-Open No. 1-194028 and Gr
aefe, G., et.al. "Dynamic Query Evaluation Plans", Pr
oc. ACM-SIGMOD, 1989.

[0004] Further, in response to an increase in the amount of transactions and an increase in the amount of databases that exceed the increase in CPU performance and disk capacity, there is a demand from users for providing a scalable parallel database system. User performance requirements for a database system include handling more than tens of thousands of concurrent users, the appearance of search transactions in terabytes, and guaranteeing response times that are not proportional to table size. Parallel database systems have received attention in recent years, coupled with a reduction in hardware costs. For a parallel database system, see DeWit
t, D., et.al.: "Parallel Database Systems: The Future
of High Performance Database Systems ", CACM, Vol.
35, No. 6, 1992. In such a system, it is necessary to connect processors tightly or loosely, and to allocate and schedule database processing statically / dynamically to a plurality of processors. Although the response performance is improved by increasing the degree of parallelism, excessive degree of parallelism adversely affects the increase of overhead and the extension of the response time of other transactions. Therefore, setting an appropriate degree of parallelism is important.

In the database processing, data to be processed exists in a secondary storage device, and it is necessary to read and transfer a large amount of data for each database operation. Become. Even in a parallel database system, when the amount of data to be transferred is large, the data transfer time is limited to the data base.
System performance bottleneck. Therefore, a method of effectively utilizing the time for transferring data from the secondary storage device can be considered. This overlaps the data transfer time and the time required for database processing on the data, and is well known in the prior art. This method is also applicable to data transfer between processors connected by an interconnection network.

[0006]

In the above-mentioned prior art, the query optimizing process is based on various statistical information of the database system based on a query input by a user and determines the most efficient processing procedure. It is automatically determined by the DBMS. Furthermore, when a variable is embedded in the query selection conditional expression, a plurality of processing procedures are developed at the time of query analysis, and the processing procedure is selected according to the value assigned to the variable at the time of executing the query. The optimal procedure is selected.

In parallel database processing, a database operation is divided into nodes (processors or pairs of processors and disk devices), and each node operates in parallel or in a pipeline at each node. According to the above-described prior art, even in this parallel processing mode, a method of selecting a processing procedure at each node is applicable.

However, in the processing that operates in parallel, each node performs parallel processing at the same time, but there is a problem that the number of each node cannot be determined in accordance with the database operation executed in each node. That is, since the criterion for determining the number of nodes is not clear, excessive parallelization adversely affects the increase of overhead, and it is difficult to optimally distribute the load.

Further, in the processing for performing the pipeline operation, the database operation is divided and stored in each node. However, if there is a variation in the division of data, the method of equally dividing each node is not clear.

Further, when a plurality of processes are performed within the time, such as when there is a restriction on the processing time, each database operation executed at each node is parameterized, and the time is calculated based on the expected processing time. It is not clear how to make adjustments.

An object of the present invention is to provide a query processing method and a database system for speeding up query processing.

[0012]

In order to solve the above-mentioned problems, the present invention comprises a plurality of nodes for executing database processing, wherein the plurality of nodes are connected to another node via a network. A management system, a distribution node including storage means for distributing and storing a database to be queried and distribution means for extracting information from the storage means and distributing the extracted information to other nodes; and Rearranging means for rearranging the distributed information; merging means for merging the rearranged information when there are a plurality of the information; and matching means for executing a match for an inquiry based on the merged information. Receiving the query, analyzing the query and creating a query processing procedure. Analyzing means for determining, a determining means for determining a distribution node and a joint node to perform an execution process based on an analysis result of an inquiry of the analyzing means, and an output means for outputting a result of the inquiry obtained from the joint node. And a decision management node.

The determining means determines the distribution node based on an analysis result of the inquiry of the analysis means, calculates an expected processing time in the distribution node, and determines a connection node based on the processing time. be able to.

[0014] The determining means is configured to allocate the distribution of the extracted information to the connection nodes equally to each of the connection nodes based on the amount of information to be extracted which is expected at the determined distribution node.

[0015] The decision management node may include storage means for storing optimization information relating to information in the storage means of each of the nodes, so that the determination means equally allocates the extracted information to the connection nodes. .

[0016] The decision management node uses a predetermined hash function in order for the decision means to equally allocate the extracted information to the joining node.

Further, the plurality of nodes perform processing independently of each other, and the connection node sequentially inputs information distributed from the distribution node, and performs processing for each input information.

Further, the distribution node may have a reordering means for reordering information distributed by the distribution node.

The deciding means rearranges the distribution nodes in the distribution node rearranging means after the distribution processing to the distribution nodes whose processing ends earlier from the calculation result of the expected processing time in the distribution nodes. Can be determined.

The deciding means decides to increase the number of the connection nodes determined based on the processing time by a predetermined number.

[0021] The reordering means of the connection nodes may have a function of performing a merge process after the end of the reordering process. The matching means may have a function of performing the merge processing.

The determining means calculates an expected processing time in the matching means and the output means,
If the processing time of the output means is longer than the processing time of the matching means based on the calculation result, it is determined to cause the matching means to perform the merge processing.

According to another aspect of the present invention, there is provided a database management system including a plurality of nodes connected to each other via a network. The storage system stores data constituting a database in a distributed manner. Means for retrieving data stored in the storage means when instructed, and sending the retrieved data to the network for distribution to other nodes; and Receiving data sent to the network, performing a database operation on the received data, and receiving inquiries from a plurality of second nodes for sending the result to the network, and a user, and analyzing the query. And, based on the analysis result, a pair with at least one first node of the plurality of first nodes. Instructs extraction of the data Te,
A third node that instructs at least one second node of the second nodes to execute the database operation, receives an operation result transmitted over the network, and outputs a result corresponding to the inquiry; Having.

[0024] The third node determines a first node instructing the retrieval of the data based on an analysis result of the received query, and calculates an expected processing time in the first node. A second node instructing execution of the database operation can be determined based on the processing time.

[0025] The third node may include the determined first node.
, The distribution of the extracted data to the determined second node is evenly allocated to the determined second node based on the expected amount of extracted data at the node.

[0026] The third node may determine the determined second node.
Storage means for storing optimization information on the data in the storage means of each of the nodes for uniformly assigning the extracted data to the other nodes.

[0027] The third node may include the determined second node.
A predetermined hash function is used to evenly allocate the extracted data to the nodes.

Further, the plurality of nodes perform processing independently of each other, and the determined second node sequentially inputs data distributed from the determined first node, and receives the input data. Perform processing for each data.

Further, each of the plurality of first nodes may further rearrange the data distributed by the first nodes.

[0030] The third node may include the determined first node.
From the calculation result of the expected processing time at the node of
It is possible to determine that the sorting is to be performed at the first node after the distribution processing to the first node whose processing ends earlier.

The third node determines to increase the number of the second nodes determined based on the processing time by a predetermined number.

[0032] The determined second node is the first node.
The merge processing may be performed after the rearrangement of the distributed data at the node is completed.

The second node performs a merge process after the rearrangement of the distributed data in the first node, and performs a matching process for an inquiry based on the merged data.

The third node calculates an expected processing time in the matching processing and the processing of outputting a result in response to the inquiry, and calculates the processing time of the output processing based on the calculation result. If the time is longer than the time for the matching process, the merge process is performed during the matching process.

According to another aspect of the present invention, there is provided storage means for distributing and storing data constituting the database, and when instructed, retrieves the data stored in the storage means and retrieves the data. A plurality of first nodes for distributing data to other nodes;
Receiving data distributed by one node, executing a database operation on the received data, and receiving a query from a plurality of second nodes that output the results, and analyzing the query; In accordance with the result, instructing at least one of the plurality of first nodes to take out the data, and instructing at least one of the second nodes to acquire a second one of the second nodes
A third node that instructs the node to execute the database operation, receives the operation result output from the second node, and outputs a result corresponding to the query, wherein the third node Determining a first node for instructing the retrieval of the data based on an analysis result of the received inquiry, calculating an expected processing time in the first node, and calculating a processing time of the second node. Calculating an expected processing time, calculating the expected processing time at the first node and the second processing to be executed;
The number of second nodes that instruct the execution of the database operation can be determined based on the expected processing time for the number of nodes.

[0036]

The decision management node determines the distribution node based on the analysis result of the inquiry from the analysis means, calculates an expected processing time in the distribution node, and determines a connection node based on the processing time. I do. The determining means is configured to equally allocate the distribution of the extracted information to the connection nodes to each of the connection nodes based on the determined amount of information to be extracted at the distribution nodes.

Each of the determined distribution nodes is:
The information is extracted from the storage unit based on the result of the analysis of the inquiry, and the extracted information is distributed to other nodes. The distribution node and the connection node perform processing independently of each other, and the connection node sequentially inputs information distributed from the distribution node, and performs processing for each input information. Each of the determined joining nodes rearranges the information distributed from the distribution node, merges the rearranged information when there are a plurality of pieces of information, and matches the inquiry based on the merged information. And outputs the result for the query obtained from the join node.

In addition, the determining means rearranges the distribution nodes after the distribution processing to the distribution nodes whose processing ends earlier based on the calculation result of the expected processing time in the distribution nodes. To be determined. The determined distribution node rearranging means rearranges the information distributed by the distribution node.

Further, the determining means calculates an expected processing time in the matching means and the output means, and, based on the calculation result, when the processing time of the output means is longer than the processing time of the matching means. Is determined to cause the matching means to perform the merge processing. The determined matching means performs a merging process.

In addition, when the processing time is predetermined, in order to perform the processing within the processing time, the determining means determines the number of the connection nodes determined based on the expected processing time in the distribution node. Is determined to be increased by a predetermined number. As a result, the number of connection nodes increases, and the connection node rearrangement unit performs the rearrangement processing in a short time, and performs the merge processing after the rearrangement processing ends.

According to the query processing method of the present invention, the number of each node can be determined according to the database operation executed at each node. In addition, when there is a variation in data division, the data is equally divided into each node, and each database operation executed in each node is parameterized to equalize expected processing time. And smooth pipeline operation is possible.

Further, according to another configuration of the present invention, the third node receives an inquiry from a user, analyzes the inquiry, and, based on the analysis result, determines whether the plurality of first nodes have the same configuration. Instructing at least one of the first nodes to retrieve the data, and instructing at least one of the second nodes to execute the database operation. A third node configured to distribute the extracted data to the determined second node based on an expected amount of extracted data at the determined first node; To be evenly distributed.

Each of the determined first nodes, when instructed, retrieves the data stored in the storage means and sends the retrieved data to the network for distribution to other nodes. The first node and the second node perform processing independently of each other, and the determined second node sequentially inputs the distributed data from the determined first node, and receives the input data. The process is performed every time. Each of the determined second nodes, when instructed, receives the data transmitted to the network,
Perform database operations on the received data,
The result is sent to the network. The third node receives the operation result transmitted on the network and outputs a result for the inquiry.

Further, the third node, after calculating the expected processing time at the determined first node, distributes the first processing to the first node whose processing ends earlier, after the distribution processing to the first node. It is determined that the data is rearranged at the node of. The determined first node rearranges the data after the distribution processing.

Further, the third node calculates an expected processing time in the matching processing and the processing of outputting a result with respect to the inquiry, and based on the calculation results, calculates a processing time of the output processing. If the time is longer than the time of the matching process, the merge process is performed during the matching process.

When the processing time is predetermined, the third node determines the number of the second nodes determined based on the processing time by a predetermined number in order to perform the processing within the processing time. Decide to increase. As a result, the number of second nodes increases, and the sorting process can be performed in a shorter time. After the rearrangement process is completed, a merge process can be performed.

According to the present invention, a node that extracts and distributes data and a node that executes a database operation can be determined. Also, if there is a variation in data division, the data is equally divided into each node, each database operation executed in each node is parameterized, and the expected processing time is equalized. And the pipeline operation can be performed smoothly.

[0048]

Embodiments of the present invention will be described below in detail with reference to the drawings.

FIG. 2 shows a conceptual diagram of the database system of the present embodiment. In FIG. 2, the database system includes a plurality of application programs (hereinafter abbreviated as AP) 10 and 11 created by a user.
And a DBMS 20 for managing the entire database system such as query processing and resource management, and for reading and writing data to be input / output processing in the database processing.
An operating system that manages the entire computer system (hereinafter, operating system is referred to as OS
30) and the data to be processed in the database.
It has a database 40 for storing data and a dictionary 50 for managing definition information of the database. DBM
S20 is connected to another database management system. The dictionary 50 also stores optimization information and the like regarding the binding columns used in the present embodiment.

The DBMS 20 includes a system control unit 21 for performing input / output management and the like in addition to management and control of the entire system, a logical processing unit 22 for performing logical processing related to an inquiry, and a physical processing unit for executing physical processing of a database. A processing unit 23 and a database buffer 24 for storing data to be processed by the DBMS 20 are provided. The logic processing unit 22 includes a query analysis 220 that performs syntax analysis and semantic analysis of the query, a static optimization process 221 that generates an appropriate processing procedure, and a code generation 222 that generates a code corresponding to the processing procedure. , A dynamic optimization process 223 for selecting an optimal process procedure candidate from the process procedure candidates generated in the static optimization process 221, and a code interpretation execution unit 224 for interpreting and executing the code. The physical processing unit 23 includes a data access process 230 for realizing condition determination, editing, record addition, and the like of the accessed data, a database record, and the like.
Database buffer control 23 for controlling read / write of data
1, a mapping process 232 for managing the storage location of data to be input / output, and an exclusive control 233 for implementing exclusive control of resources shared by the system.

FIG. 3 shows an example of a hardware configuration to which the present invention is applied. Specifically, FIG. 3 shows an example of an application configuration of a parallel processor system in which a processor and a disk device constitute one node and a plurality of nodes are provided. In FIG. 3, the processors 60 to 65 and the disk devices 70 to 75
0 is connected. The hardware configuration shown in FIG.
Is a configuration for parallel processing of the database system shown in (1) by a plurality of processors, and the processing is distributed to each node.

FIG. 1 shows a configuration in which functions are distributed to each of the above nodes. FIG. 1 is a schematic diagram of a database system to which the present embodiment is applied. Hereinafter, a processing example of the parallel database system will be described with reference to FIG. In this example, parallel processing is applied to a search request for a database. In FIG. 1, each node is assigned to each node a sort function for extracting and distributing data and a merge function for combining data sorted by a plurality of nodes. Some nodes have only a sort function and some nodes have a sort function and a merge function. The database shall consist of a table which is viewed from the user in the form of a two-dimensional table,
The table has data for each row or row. A row is composed of one or more attributes (this is called a “column”). In FIG. 1, there are T1 and T2 as database tables. Table 1 is stored from node 1 (90) to node 4 (91), and table T2 is stored from node 5 (92) to node 8 (93). Each of these nodes is a distribution node, and data extraction processing and data distribution processing are executed based on a table stored in the distribution node. Also, nodes 9 (94) to 11 (96) are connection nodes,
-4 and the data output from the nodes 5-8, and performs a partial column sort process and a merge process to create a complete column. Further, the node 12 (97) is a decision node that accepts a query, analyzes the query, and determines the number of distribution nodes and connection nodes that execute processing for the query. The node 12 (9
7) receives and outputs data output from nodes 9-11. These nodes are connected by an interconnection network 80, and nodes 1-4 and nodes 5-8
And nodes 9 to 11 operate in parallel, and
-4 and the results processed at nodes 5-8, respectively,
It operates in a pipeline such that the processing is immediately performed in the nodes 9 to 11 (hereinafter, referred to as a parallel pipeline operation). The nodes 9 to 11 and the node 12 also operate in a pipeline manner. In the following, the partial column sorting process at the nodes 9 to 11 is referred to as a slot sorting process, and the complete column creating process is referred to as an N-way merge process. The slot sort process indicates a sort process in a page in which data is stored, and if the data is read in the slot order, the rows can be accessed in ascending order. N-way merge processing
Using an N-way buffer, N sort runs are input at each merge stage, and one sort run is finally created.

An inquiry for a database search process is as follows:
For example,

SELECT T1. C3, T2. C3 FROM T1, T2 WHERE T1. C1 = T2. C1 AND T1. C2 =? When such an inquiry is received at the node 12, the node 12 selects an optimal distribution processing method, and instructs each node via the network. In the above inquiry, node 1 (9
0) to the node 4 (91) with the table T1 and the node 5 (9
Since the table T2 is stored in each of the nodes 2) to 93 (93), the data extraction processing and the data distribution processing are executed in each node. Further, the nodes 9 (94) to 11 (96) sequentially receive the data output from the nodes 1 to 4 and the nodes 5 to 8, and execute the sorting process and the combining process. The node 12 (97) receives and outputs the data output from the nodes 9 to 11. This terminates the database search.

Next, the relationship between the processing time of each node will be described with reference to FIG. FIG. 4 is a schematic diagram for explaining the parallel pipeline operation. In FIG. 4, 100 and 101 are nodes 1 (9
0) to node 4 (91) and the processing from node 5 (92) to node 8 (93), and executes data extraction processing and data distribution processing. 110 and 111 correspond to the processing from the node 9 (94) to the node 11 (96), and the slot sort processing, the N-way merge processing, and the matching processing are executed. Reference numeral 120 corresponds to the processing in the node 12 (97), and the request data output processing is executed. Along the time axis, the data processed in the data extraction processing and the data distribution processing 100 and 101 are sequentially slot-sorted 110 and 1
It moves to 11 and is executed in a pipeline manner. The process from the data fetch process to the slot sort process is called a fetch phase. Also, N-way merge processing 110 and 11
1 is simply executed in parallel at each node. This N-way merge processing period is called a merge phase. Further, the result of the matching process is a request data output process 120.
, And are executed in a pipeline manner. The process from the matching to the output of the requested data is called a combining phase. The time chart shown in FIG. 4 shows the processing contents when the inquiry example shown in FIG. 1 is applied. In the extraction phase, the processing time from node 1 (90) to node 4 (91) is shown as T1 data extraction / data distribution processing time 130. The processing time from the node 5 (92) to the node 8 (93) is indicated by T2 data extraction / data distribution processing time 131, and the transfer time in the interconnection network 80 is represented by the data distribution transfer time 140.
, And the processing time from node 9 (94) to node 11 (96) is T1 / T2 slot sort processing time 1
As shown at 50, each is performed. In FIG. 4, the take-out phase ends up to the point of time 180 for waiting for slot sort processing completion. In the merge phase, the processing time from the node 9 (94) to the node 11 (96) is T1 / T2N way merge processing time 1
It is executed at the time indicated by 51. This merge phase is a T1 / T2N way merge process waiting 181.
Ends with In the connection phase, the processing time from the node 9 (94) to the node 11 (96) is indicated by a matching processing time 152, the processing time in the mutual connection network 80 is indicated by a connection result transfer time 160, and the processing time on the node 12 (97). The processing time is indicated by a request data output processing time 170, and each is executed within that time.

Next, a method of distributing the processing of the node 12 to each node group in FIG. 1 will be described with reference to FIG. FIG. 5 is an explanatory diagram showing a method of assigning data to each node group in the data distribution processing. As a premise, a node group that performs data extraction / data distribution processing includes ten nodes 1 to 10 including processors 200 to 230 and disk devices 201 to 231. Further, it is assumed that the node group performing the coupling process includes five nodes 11 to 15 each including processors 240 to 250 and disk devices 241 to 251. The dictionary 50 stores optimization information 51 on the join column. The optimization information 51 is information for equally dividing the data in the database. For example, since the number of data items for the join column is usually not uniform, it is necessary to divide the data by the join column so that the number of data items becomes uniform. Is what you do. As shown in FIG. 5, the data stored in the nodes 1 to 10 can be equally divided in the respective divided ranges from v1 to v10. In this case, nodes 11 to
V1 to v2, v3
Node numbers 11, 12, 13, 14, and 15 in five sections of 〜v4, v5 to v6, v7 to v8, and v9 to v10, respectively.
What is necessary is just to provide the distribution processing means which matches. If the optimization information does not exist, an appropriate hash function may be set to perform data distribution. In this way, the node 12 in FIG. 1 includes the distribution processing means, and distributes the processing to each node group when performing the N-way merge processing. As a result, in the case described above, data can be equally divided into the nodes 11 to 15, and the processing time becomes equal.

Next, a method of determining the number of connected nodes when performing the N-way merge process will be described with reference to FIG. FIG. 6 is a schematic diagram illustrating a method for determining the number of connected nodes.

Each phase of the parallel connection processing in FIG.
The processing time of each process is graphed and laid out in accordance with the outline of the parallel pipeline operation shown in FIG. In FIG. 6, the data extraction / data distribution processing
To 8 and processing time of 300 to 305, respectively. Here, the processing time 30 of the node 5
4 is the maximum processing time. The slot sort processing time can be derived from the number N of coupling processing nodes, predetermined system characteristics (CPU performance, disk device performance, and the like), and a database operation method. It can be obtained by the following equation.

[0059]

E = a / N + b * N + c In order to maximize the effect when performing the pipeline processing,
It is possible to determine the number of connected nodes 350, which is the intersection between the performance characteristics of the slot sorting process and the maximum processing time 304, as the number of nodes. When the number of connection nodes 350 is determined, the N-way merge processing time 320 and the matching processing time 330 can be similarly estimated from the performance characteristics of the N-way merge processing and the matching processing. The sum of these processing times is the total processing time for the inquiry. In this manner, the number of connection nodes is determined, and the data distributed in the data fetching / data distribution processing are sequentially merged and simultaneously processed, so that the entire processing time (response time from inquiring to output). Can be shortened.

Specific examples of performance characteristics used when determining the number of connection nodes will be described below. For example, the number of rows is 10,000,000 in both Table T1 and Table T2, and the number of conditions is T1-1 (the total number of rows is reduced to 1%).
The number of distribution nodes for data extraction / data distribution processing is equally divided into 16 nodes in each of Tables T1 and T2, the number of connection nodes is 8, and the processor performance is 50 MIPS (50 million instructions executed per second). Suppose that the network transfer rate is 20 Mbytes / sec.
The result of processing by the actual database management system under such conditions or the result calculated from the performance model is as follows.

The processing time of the distribution nodes in Table T1 and Table T2 is 180 seconds, the T1 / T2 slot sort processing time is 80 seconds, the N-way merge processing time is 380 seconds, the matching processing time is 110 seconds, and the requested data output time is Is 1
0 seconds. The processing time for the inquiry is estimated based on the processing performance of these results.

Next, a processing time adjustment method (tuning method) for further reducing the response time based on the connection node number determination method shown in FIG. 6 will be described with reference to FIGS.
This will be described with reference to FIG. The method described below is calculated in advance when the distribution processing means of the node 12 determines the distribution of processing to each node group,
The distribution is determined from the result.

FIG. 7 is a schematic diagram showing the slot sorting preprocessing. Data retrieval / data distribution processing is performed on node 1
8 and the processing time of each of 300 to 305 is required. The processing time for each node varies depending on the number of data in each table. In addition, the slot sorting process is set to be executed in a group of the joining processing nodes. If there is a variation in the processing time for each node, consider a processing procedure for transferring the slot sort processing to the data extraction / data distribution processing node group. As shown in FIG. 7 as the preprocessing of the slot sort, the slot sort processing is performed at the node where the data extraction / data distribution processing ends earlier. According to this processing, the slot sort processing time at the node having the number of assigned joint nodes 350 can be reduced from 310 to 312. At the processing time difference 311, the N-way merge processing is shifted. This is nothing short of extending the run length of the slot sort process.
As a result, the N-way merge processing time can be reduced, and as a result, the response time can be reduced.

FIG. 8 shows a schematic diagram of the slot sort run length tuning. For example, when a plurality of processes are performed within the time, such as when there is a restriction on the processing time, each database operation to be executed at each node is parameterized, and time adjustment (tuning) is performed based on the expected processing time. A method for performing the above will be described. The number of connection processing nodes is increased to a minimum from the number of connection node assignments 350 obtained in FIG. 6 to shorten the response time. In this case, it is assumed that the number of assigned joint nodes is 351. Number of assigned joint nodes 3
If 51, the slot sort processing time is 310 to 3
It is reduced to 12. In order to maximize the pipeline effect, the N-way merge processing is shifted to the slot sort processing in the processing time 311. As a result, the number of merges in the N-way merge process is reduced, the processing time can be reduced to 320, and as a result, the response time can be reduced.

FIG. 9 shows a schematic diagram of N-way merge count tuning. The matching data processing time 330 determined by the number of connection node assignments 350 is the required data output processing time 34
If it is smaller than 0, the merge processing at the last stage of the N-way merge processing can be shifted to the matching processing. N
If the sum of the merge processing time 331 of the last stage of the way merge process and the matching process time 330 does not exceed the required data output processing time 340, the merge process of the final stage is shifted to the matching process. Thereby, the response time can be reduced.

Next, an operation flow of the database management system in the present embodiment will be described. FIG. 10, FIG. 11,
FIGS. 12, 13, 14 and 15 show flowcharts of the processing of the DBMS in this embodiment. In FIG. 10, the DBMS includes a query analysis process 400 for performing query analysis by query analysis performed before query execution (step 220), a static optimization process (step 221), and code generation (step 222); And a query execution process 410 for executing a query by interpreting the code by executing a dynamic optimization process (step 223) and selecting a processing procedure (step 223).

The outline of each processing unit will be described below.

(A) Query Analysis Processing 400 In FIGS. 10A and 10C, in query analysis (step 220), syntax analysis and semantic analysis of a query sentence input by an application program in the node 12 are executed. (Step 2200). FIG.
In (a), in the static optimization process (step 221), the ratio of data that satisfies the condition is estimated from the conditional expression appearing in the query at the node 12, and the effective ratio is estimated based on a preset rule. In this case, an access path candidate (particularly, an index is selected) is created, and a candidate for a processing procedure is created. In the code generation (step 222), the node 1
In step 2, the processing procedure candidates are developed into an executable form.

(B) Query Execution Processing 410 In FIG. 10B, dynamic execution optimization (step 2)
In 23), a processing procedure to be executed in each node group is determined based on the constant substituted in the node 12. In the code interpretation execution (step 224), the processing procedure is interpreted and executed in each node.

Next, a detailed processing flow of each processing unit will be described.

In FIG. 10D, in the dynamic optimization processing (step 221), a predicate selection rate of a conditional expression appearing in a query is estimated (step 2210), and an access path including an index or the like is pruned (step 2210). 2211),
A processing procedure candidate combining these access paths is generated (step 2212).

In FIG. 10E, in the predicate selection rate estimation (step 2210), it is checked whether or not a variable appears in the query conditional expression (step 22101). If a variable appears, it is checked whether the conditional expression has column value distribution information (step 22104). If present, terminate. If not, a default value is set according to the type of the conditional expression (step 22105), and the process ends. If the variable does not appear, it is checked whether the conditional expression has column value distribution information (step 22104). If not, a default value is set according to the type of the conditional expression (step 22105), and the process ends. If there is, the selectivity is calculated using the column value distribution information (step 2210).
3).

In FIG. 11, access path pruning 221
In step 2, the index of the column appearing in the query conditional expression is registered as an access path candidate (step 2212).
0). Next, it is checked whether the table to be accessed by the query is divided and stored in a plurality of nodes (step 2).
2121). If divided and stored, the parallel table scan is registered as an access path candidate (step 22123). If not, the table scan is registered as an access path candidate (step 2).
2123). It is checked whether or not the selectivity of each conditional expression has already been set (step 22124). If set, the index of the conditional expression that minimizes the selectivity for each table is set as the highest priority of the access path (step 2212).
5). If not already set, the maximum / minimum value of the selectivity of each conditional expression is obtained (step 22126). Finally,
A selection criterion for each access path is calculated from system characteristics such as CPU performance and IO performance (step 22127). (Step 22128).

In FIG. 12, processing procedure candidate generation 221
3 checks whether the table to be accessed by the query is divided and stored in a plurality of nodes (step 2213)
0). If divided and stored, the process proceeds to step 22135. If it is not divided and stored, it is checked whether or not the sorting procedure is included in the processing procedure candidate (step 22131). If included, step 22135
Move to. If not, it is checked whether the access path of the table to be accessed by the query is unique (step 22132). If it is unique, a single processing procedure is created (step 22133). Is created (step 22134), and the process ends. In step 22135, the query is decomposed into two-way joins that can be joined. The data reading / data distribution processing procedure is registered as a candidate corresponding to the storage node group of the table to be divided and stored. Also, the slot sorting procedure is registered as a candidate (step 22136). A slot sort procedure, an N-way merge procedure, and a matching procedure are registered as candidates for the join processing node group, and the slot sort run length and the number of merge processes are parameterized (step 22137). The request data output procedure is registered in the request data output node (step 22138). Finally, when all the evaluations for the decomposition result have been completed (step 22139), the process ends.

In FIG. 13, the code generation 222 checks whether or not the processing procedure candidate is unique (step 22).
20). If it is unique, the process proceeds to step 2223.
If it is not unique, the optimization information including the column value distribution information is embedded in the processing procedure (Step 2221), and a data structure for selecting the processing procedure based on the constant assigned at the time of executing the query is created (Step 2222). Finally, the processing procedure is developed into an executable form (step 2223).

In FIG. 14, dynamic optimization processing 223
Checks whether the created processing procedure is single (step 22300). If it is single, it ends.
If not, the selectivity is calculated based on the substituted constant (step 22301). It is checked whether or not the candidate processing procedure includes a parallel processing procedure (step 2230).
2). If not included, the processing procedure is selected according to the access path selection criteria (step 22313), and the process ends. If it is included, the optimization information (column value distribution information of the join column, the number of rows of the table to be accessed, the number of pages, etc.) is input from the dictionary (step 2230).
3) The processing time for data extraction / data distribution is calculated as described above in consideration of each system characteristic (step 22304). The number of nodes p to be allocated to the combining process is determined from the processing time, and the processing procedure a1 is determined (step 22305). It is checked whether there is a variation in the data retrieval / data distribution processing time (step 22306). If there is a variation, a processing procedure a2 for executing the slot sort processing in the data extraction / data distribution processing node group is set (step 22307). Next, a processing procedure a in which the number p of connected nodes is increased by α
3 is set (step 22308). If the requested data processing time is larger than the sum of the matching processing time and the N-way merge processing time for one time (step 22309),
A processing procedure a4 in which one N-way merge process is transferred to the matching process is set (step 22310). The optimum processing procedure is selected from the processing procedures a1 to a4 from the viewpoints of the minimum response time, the minimum load on each node, and the small effect on other transaction response performance (step 22311). Create data distribution information based on optimization information (step 2
2312). If there is no optimization information, data distribution information is created according to the join column evaluation value of the hash function. The processing procedure is selected according to the access path selection criteria (step 22313), and the process ends.

In FIG. 15, the code interpretation execution processing 22
In step 4, processing is performed according to the processing procedure corresponding to each set node.

First, each node determines whether or not data fetching / data distribution processing is set (step 22400). If the data extraction / data distribution processing is set, the database accessing the storage device of each node is accessed and the conditional expression is evaluated (step 22401). Data is taken out based on the data distribution information created based on the optimization information, and the data is sequentially distributed to the buffers of the connection nodes (step 2240).
2). Determine whether the buffer of each join node is full,
If it is full, it is transferred in page format to the corresponding join node. When all the data corresponding to the inquiry is extracted and distributed, the process ends (step 22404).

In each node, it is determined whether or not slot sort processing is set (step 2240).
5). If the slot sort processing has been set, page format data is received from the data extraction / data distribution processing node (step 22406), and the received data is sequentially subjected to slot sort processing (step 22407). The processed slot sort result is temporarily stored, and the slot sort process ends (step 2).
2408).

It is determined whether the N-way merge process has been set (step 22409). If N-way merge processing is set, N-way merge processing is executed based on the slot sort result (step 224).
10), the result of the N-way merge process is temporarily stored in a buffer or the like (step 22411), and the N-way merge process ends.

Further, it is determined whether or not the matching process is set (step 22412). If the matching process is set, the sort list of the N-way merge process result is matched, and data is set in the output buffer (step 22413). If the output buffer is full, it is transferred to the request data output node in page format (step 22415).

Further, it is determined whether or not the request data output process is set (step 22416). If the request data output process has been set, it is determined whether or not there is a page format data transfer from the connection node (step 22417). If the data in the page format is transferred, the data in the page format is received (step 224).
18) Output the query processing result to the application program, and if there is no page format data transfer, output the query processing result as it is (step 22)
419).

In the above code interpretation execution processing,
When slot sorting is performed in the data extraction / data distribution processing node group when the processing time varies, the code interpretation execution processing 224 is executed again after the data extraction / data distribution processing is completed. Perform sort processing.

Further, if the result of the N-way merge processing is not a complete sort sequence in step 22413, the merge and matching processing of the last stage are performed.

By performing the above processing, the inquiry response time of the database management system can be reduced.

The method for allocating joint nodes shown in FIG. 6 and the method for tuning shown in FIGS. 7, 8 and 9 may be applied independently or in any combination. That is, in the dynamic optimization process 223, it is assumed that all combinations can be applied. Further, in the data retrieval processing, application of a parallel input / output access method including a plurality of disk devices,
Applying a prefetch input / output method, applying a data distribution method using optimization information or a hash function to data distribution processing, applying a parallel sorting method to N-way merge processing, and applying a matching processing method between nodes to matching processing It is also conceivable to assign a plurality of nodes to the request data output processing and apply a parallel reception processing method. Step 2 above
In step 2309 and step 22310, one N-way merge process is assumed, but generally n times (n ≧ n)
It may be 1).

Regarding the parallel pipeline operation shown in FIG. 4, the connection node assignment method shown in FIG.
Applying the tuning methods shown in FIGS. 8 and 9 may make it possible to omit the merge phase among the extraction phase, the merge phase, and the join phase. That is, it becomes possible by extending the slot sort run length and moving the N-way merge process. In this case, the merge phase process is omitted from the query execution process.

The query processing method of the present invention is not limited to the use of the rule using the statistical information and the cost evaluation, but may be applied to any method that can provide a processing procedure for giving appropriate database reference characteristic information. . For example, the present invention can also be applied to a DBMS that performs optimization processing such as cost evaluation only, rule use only, and cost evaluation and rule use together.

The present invention can be realized through a software system of a large-scale computer with a tightly-coupled / loosely-coupled multiprocessor system. It is also possible to realize through. Further, the present invention is applicable to a uniprocessor system as long as parallel processes are allocated for each processing procedure.

According to the present embodiment, the number of each node is determined according to the database operation executed at each node.
If there is variation in the data division, the data is equally divided into each node, each database operation executed in each node is parameterized, and the expected processing time is equalized. And a smooth pipeline operation can be performed, and high-speed inquiry processing can be realized.

[0091]

According to the present invention, the number of each node is determined according to the database operation executed at each node, the data is equally divided among the nodes, and the processing time executed at each node is equalized. High-speed inquiry processing without time deviation can be realized.

[Brief description of the drawings]

[Fig. 1] Schematic diagram of parallel connection processing

FIG. 2 is a configuration diagram of a database system.

FIG. 3 is a hardware configuration diagram.

FIG. 4 is a schematic diagram of a parallel pipeline operation.

FIG. 5 is a schematic diagram of data distribution processing.

FIG. 6 is a schematic diagram of a joint node assignment

FIG. 7 is a schematic diagram of slot sort preprocessing.

FIG. 8 Schematic diagram of slot sort run length tuning

FIG. 9 is a schematic diagram of N-way merge count tuning

FIG. 10 is a flowchart of a database management system.

FIG. 11 is a flowchart of a database management system.

FIG. 12 is a flowchart of a database management system.

FIG. 13 is a flowchart of a database management system.

FIG. 14 is a flowchart of a database management system.

FIG. 15 is a flowchart of a database management system.

[Explanation of symbols]

10, 11 application program, 20 database management system, 22 logical processing unit, 220 query analysis, 221 static optimization process, 222 code generation, 223 dynamic optimization process, 224 code execution , 30 operating system, 40 database, 50 dictionary, 80 interconnection network, 90, 91, 92, 93, 94, 95, 96, 9
7 ... Node.

 ──────────────────────────────────────────────────続 き Continued on the front page (72) Inventor Kazuyoshi Negishi 1099 Ozenji Temple, Aso-ku, Kawasaki City, Kanagawa Prefecture Inside Hitachi, Ltd.System Development Laboratory Co., Ltd. (72) Inventor Shunichi Torii 1099 Ozenji Temple, Aso-ku, Kawasaki City, Kanagawa Hitachi, Ltd. Inside the Manufacturing Systems Development Laboratory

Claims (2)

[Claims] A plurality of first nodes for extracting and transmitting stored data in accordance with an extraction request; and performing a database operation process on the data transmitted from the first node in accordance with an input database operation request. A plurality of second nodes for executing and outputting the result of the database operation processing, analyzing the input query request, generating a plurality of database operation requests and distributing them to the second nodes, respectively, A plurality of retrieval requests for the target data are generated by the request, the generated retrieval requests are respectively sent to the first nodes, and a database operation processing result of the database operation request is received from the second node. And a third node that outputs a processing result of the inquiry request. Over de is a database management system, characterized in that it comprises an intermediate result storage means for temporarily storing intermediate results of the database processing. 2. An inquiry method in a database management system comprising a plurality of first nodes, a plurality of second nodes, and a third node, wherein the first node stores data stored in accordance with a retrieval request. The second node performs a database operation process on the data transmitted from the first node in accordance with the input database operation request, and outputs an intermediate result of the database operation process. Temporarily storing and outputting the processing result of the database operation processing, the third node analyzes the input inquiry request, generates a plurality of database operation requests, and distributes the plurality of database operation requests to the second node, Generates multiple retrieval requests for the target data in the database operation request, and Query processing method sends to the first node, a database operation result of said database operation request, receiving from each of said second node, and outputs the processing result of the inquiry request.