JPS6258332A - Optimizing system for partial inquiry of decentralized data base control system - Google Patents

Abstract

PURPOSE:To simplify the cost calculation by omitting each coupling arithmetic or applying the semi-coupling or a coupling to carry out the partial inquiry for cost calculation and then performing the minimum cost calculation. CONSTITUTION:When a command is supplied for coupling of tables A, B and C, three cases are first studied with two tables A and B as follows. That is, (1) the coupling is omitted for the time being and then the coupling is attained, (2) the semi-coupling is performed at first and then the coupling is attained and (3) the coupling is attained from the first. Then a case of the minimum cost is selected among those three cases. When the case (2) is discriminated satisfactory according to the cost calculation, the semi-coupling result A(B) and a table C are studied. Thus, it is discriminated that the case (1) is satisfactory. Finally the coupling results A and C(B) and a table B are studied and the case (1) is discriminated preferable. Thus each case is carried out according to the result of each discrimination.

Description

[Detailed Description of the Invention] [Industrial Application Field] The present invention provides a distributed database management system that simultaneously decomposes an alignment command input by a user into partial alignments that are processed on each computer. Regarding optimization processing methods.

[Conventional technology]

In a distributed database management system, there are multiple computers with databases, each connected by a communication line, and each database is used to perform comprehensive data processing.
When executing an alignment command given to the system by a user, it is broken down into partial alignments that can be executed on each computer and executed, but the amount of data transferred and the amount of internal processing of the computer are minimized. Consider a decomposition method, that is, perform optimization.

The main types of processing performed by distributed database management systems are joins and semi-joins. The work performed by individual computers is mainly data transmission, combination, and semi-combination processing. The database includes the employee table in Figure 6 (Al's employee table and the family table in Figure 6 1b), and joining is the operation of matching (combining) these tables to create a new large table as shown in Figure 7. means. Figure 7 focuses on the employee number in Figure 6 and shows the employee's name, place of work (these are in the employee table), family names and relationships (these are in the family table). It is listed. The command to perform this join is "Join the worker table and family table by employee number." 6th
It is noteworthy that even if the employees are in Figure 6(a), those who are not in Figure 6(b) (those without families) are dropped from the join result table (Figure 7).

Such a combination is effective when obtaining data on employees with family members. A semi-join is an operation in which the result of the join is extracted for only the data of the items in either of the original tables, and FIG. 8 is an example of this.

In a semi-join, only the fields of interest are sent and the join is performed. FIG. 8 shows the item group of FIG. 6(a) (items of the employee table) extracted from the combined result of FIG. 7.

The difference from Figure 6(a) is that employees without families are missing. Binding is performed by the user inputting a command, but semi-binding is not specified by the user, but is performed internally by the system in the process of executing the user command.

[Problem that the invention seeks to solve]

When a user instructs a join (specifies which tables are to be joined for which items), the system determines what kind of join or semi-join to perform and in what order based on the cost minimum. Conventionally, when making this decision, we always focused on the whole, and for example, we semi-combined XX and XX, then semi-combined XX and XX, and finally, we combined XX and XX to obtain the answer. If there is, calculate the cost required for the entire schedule, calculate the cost required for all other schedules with different combinations (generally, multiple schedules occur), compare these, and choose the best one (lowest cost). Recruit and implement. However, this method requires a large amount of cost calculation and is extremely troublesome.

The present invention aims to improve this point and simplify cost calculation.

[Means for solving problems]

The present invention is a distributed database management system created on a plurality of computers and a communication channel network connecting these computers, and a part that executes on each computer a query that is input by a user and includes a combination of a plurality of databases. In the partial alignment optimization method that is divided into alignment groups, the partial alignment executed on each computer is divided into two computers, each with its own database or processing result (1).
Omit the join for now and join later (2
) Process it as a semi-join for now, and then join it later.
■Calculate the cost of each of them, either perform the join from the beginning, and execute the one with the lowest cost.
■ above between the computer that has the processing results and the computer that has other databases.

This method is characterized in that costs are calculated for cases (1) and (2), the one with the lowest cost is executed, and the same process is repeated one after another until an answer to the user's arrangement is obtained.

[Effect]

As shown in FIG. 1, when combining table A and table B (this is input by a command), there are the following three processing methods. In other words, table A is on computer A, table B is on computer B, and the join result is obtained on computer C. As shown in Fig. (joins, semi-joins) are omitted, tables A and B
is sent in its original form from computers A and B to computer C, where it is combined, and the amount of data transferred is V cA) +
V (B) (where vO indicates the amount of data), the computer's internal processing amount is the amount of the combination of A and B, and the entire amount is subject to cost calculation.

As shown in FIG.
(A), the computer's internal processing amount is B■A's calculation amount and AH(B
IXA), the total amount of these is the subject of cost calculation. Here, X indicates a semi-bond, X indicates a bond, and a indicates an item to be noted during the bond. The amount of data sent from computer A to computer B is V (a), the amount of data sent from computer A to computer C (A), the result of semi-combination is sent from computer B to computer C, and the amount of data is V ( BKA).

To explain this case ■ using the example in Figures 6 to 8, the family table is on computer A, and the employee table is on computer B.
, the desired result is the table of employees with families shown in Figure 7.In this table, employees without families are excluded (not targeted), so the employee number (of interest) is transferred from computer A to computer B. item) (this is information about a family member, the amount of data transmitted is V(a)), and computer B
Now, take out only the column (employee number, name, workplace) of the person whose employee number came from computer A (this is the semi-combined result), and send it to computer C. Computer C receives the family table from computer A and compares them to obtain the combined result shown in FIG.

As shown in Figure 4, the method (■) is a method in which the connection is performed immediately from the beginning, and the amount of data transferred is V (A) +
V (AKB), the computer internal processing amount is the calculation amount of AXB, and the entire amount is subject to cost calculation.

In this invention, when joining two tables, the processing method is as follows:
Since there are three possible options shown in FIG. 2, FIG. 3, and FIG. 4, the costs are calculated for these, and the one to be selected is determined based on which one has the lowest cost. Then execute the selected one, and again between the result and the next table ■,■,
■ Calculate the cost of the cases, select the one with the minimum cost, and execute it. In the same manner, the above operations are performed until the answer to the input command is obtained. The feature is that the cost calculation is limited to only the part of the connection of interest. On the other hand, in the conventional method, the cost calculation is performed for every combination, and the combination with the minimum cost is determined, and the combination with the minimum cost is adopted. This makes cost calculation complicated and time-consuming, especially when there are many tables. It was something like that.

〔Example〕

The method of the present invention is effective when there are three or more tables (
If there are two tables, the method of the present invention and the conventional method are the same). FIG. 5 shows an example where there are three tables. Tables A, B,
When a command to join C is input, the system first performs the above three cases for two tables, namely (1
) For now, omit the join and join later (second
(Figure 3), (2) Process it as a semi-join for now and then combine it later (Figure 3), ■ Perform the join from the beginning (
Figure 4), and select the one with the lowest cost.

In Figure 5, tables A and B are taken up (A, C1B, and C
(Although it is possible to choose any two as they are the same), as a result of cost calculations, it was determined that case (■) is better. Next, we will consider semi-join result A (B) and table C, and it is determined that case ■ is better, and finally join result A,
After considering C('B) and table B, it was determined that the case of ■ was the best choice, so that was the case.

The amount of data transferred in this case is V (A) + V (b) + V
(AKB)+V (C)+V ((AKB) Flash C)+V
(B), and the amount of internal processing of the computer is
It is. However, in the present invention, it is not necessary to calculate these sums (it is best to use ■, ■, or All you have to do is consider whether or not.

On the other hand, in the conventional method, the whole is considered, so the sum of the above data transfer amount and the computer's internal processing amount (more specifically, the processing amount required for data input/output is included in this) is calculated, and various combinations of tables and partial results are also used. Since there are various combinations, we calculated the overall cost for all of the combinations and decided which combination was optimal.

〔Effect of the invention〕

As explained above, in the present invention, in a distributed database management system created on a plurality of computers and a communication channel network connecting them, a query input by a user is processed by partial matching executed on each computer. When decomposing into groups, the optimization is to decide whether to omit, semi-combine, or combine each join operation, but instead of calculating the overall cost, Since the cost calculation is performed to determine the processing method by looking at only the parts involved in the one connection of interest, there is an advantage that the cost calculation is simple and the processing method can be determined quickly.

[Brief explanation of the drawing]

1 to 5 are explanatory diagrams of the present invention, FIG. 6 is a diagram showing an example of a table, FIG. 7 is an explanatory diagram of a join, and FIG. 8 is an explanatory diagram of a semi-join.

Claims (1)

[Claims] In a distributed database management system created on a plurality of computers and a communication path network connecting them, an inquiry including a combination of a plurality of databases input by a user is executed on each computer. In the sub-alignment optimization method that is used when decomposing the sub-alignments into groups of sub-alignments, the sub-alignments executed on each computer are combined (1) for the time being between two computers, each with its own database or processing results. (2) For now, process it as a semi-join and then combine it later. (3)
) Perform the join from the beginning, calculate the cost of each, execute the one with the lowest cost, and connect the computer with the processing result with the computer with the other database ( The part characterized in that the costs are calculated for the cases 1), (2), and (3), the one with the lowest cost is executed, and the same process is performed sequentially until an answer to the user inquiry is obtained. Time optimization method.