JPS6318777B2 - - Google Patents

Description

DETAILED DESCRIPTION OF THE INVENTION The present invention relates to a method for determining a minimum work relation that can minimize the size of a work relation generated as a result of reduction or join in a relational database system. be.

Relational databases provide data to users in tabular format, but when a relational database system receives an inquiry from a user, it selects related relations and performs reduction processing or join processing on them. The processing is performed to create the desired relationship, which is then provided to the user as the inquiry result. In the process of obtaining query results by combining reduction processing and join processing, a working relation is required to temporarily store the reduction results or join results. In order to improve processing efficiency in inquiry processing,
It is necessary to minimize the size of the working relation, which requires minimizing the field configuration and the number of records in the working relation.

The present invention meets the above-mentioned requirements, and provides a minimum working relation that allows the field configuration of the working relation used during the inquiry processing process to be reduced to the minimum necessary configuration. The purpose is to provide a decision method. Therefore, the method for determining the minimum work relation of the present invention determines a processing sequence including reduction processing or join processing according to inquiry information sent from the user, and performs reduction processing or join processing in the process of executing the processing sequence. In a relational database system that stores the result of join processing in a working relation, there is a one-to-one correspondence with the field that appears in the above query, and the number of occurrences of the field in the query is set as an initial value. A reference counter control mechanism having a plurality of reference counters, and requesting the reference counter control mechanism to change the contents of the reference counter based on the query conditions and processing sequence, and changing the contents of the reference counter group consisting of the plurality of reference counters. and a minimum working relation determining mechanism that determines the field configuration of a working relation for storing the results of each reduction process or join process based on the i-th ( i=1, 2...n, n
When determining the field configuration of the working relation corresponding to the process (the last process), if that process is a reduction process, the condition in which only the fields of the reduction source relation are used is considered. Checks whether the field exists in the query condition, and if it exists, requests the reference counter control mechanism to reduce the contents of the corresponding reference counter by the number of times the field appears, and also makes a query that targets the condition. If the content of the reference counter group is read after the content of the reference counter is changed and the field configuration of the working relation for the reduction process is determined, and the i-th process is a join process. To do this, check whether there is a condition in the target query condition that uses only the fields of multiple relations to join, and if it exists, decrement the contents of the corresponding reference counter by the number of times the field appears. the reference counter control mechanism, and excludes the condition from the target inquiry conditions,
The present invention is characterized in that after the contents of the reference counters are changed, the contents of the reference counter group are read to determine the field structure of the work relation for the join process. Hereinafter, the present invention will be explained with reference to the drawings.

Figure 1 shows an overview of the relational database system, Figure 2 shows the flow of processing in the logical data subsystem, Figure 3 explains the processing in the scheduler, and Figure 4 shows the minimum work required. Figure 5 is a diagram showing an example of processing in the interpreter; Figure 6 is a diagram showing an example of a query; Figure 7 is a diagram showing an example of query processing. FIG. 8 is a diagram showing an example of the order, and FIG. 8 is a diagram showing an example of the state transition of the reference counter.

In FIG. 1, 1 represents a relational database management system, 2 represents a relational database, 3 represents a logical data subsystem, and 4 represents a logical storage subsystem. When an inquiry is input, the relational database management system 1 accesses the relational database 2, creates an inquiry result, and
This is transferred to the user. The relational database management system consists of a logical data subsystem 3 and a logical storage subsystem 4. The logical data subsystem 3 provides query analysis, optimization, execution, etc., and the logical storage subsystem 4 provides data storage, exclusive control, recovery, etc. The relational database 2 stores a plurality of relations (tables).

FIG. 2 shows the flow of processing in the logical data subsystem. As shown in FIG. 2, the logical data subsystem 3 includes syntax parsing,
Performs processing such as semantic analysis, scheduler, and interrupter. Queries input to the logical data subsystem 3 are written in a non-procedural language. FIG. 6 shows an example of a query written in a non-procedural language. The query in Figure 6 consists of relation A with fields a ₁ , a ₂ , a ₃ , a ₄ , and a ₅ , relation B with fields b ₁ , b ₂ , b ₃ , and b ₄ , and field c _{1 .} , c ₂ , c ₃ , c ₄ , fields a ₁ , a ₃ , b ₁ , which satisfy the conditions shown below WHERE
The request is to create a relation with b ₂ , c ₁ , and c ₄ . The syntax analysis unit checks the syntax of the input query sentence, the semantic analysis unit checks the existence of relations and fields specified in the input query sentence, and the scheduler performs optimization and It generates executable procedure statements, and the interpreter executes executable procedure statements. An example of an execution procedure statement corresponding to the inquiry statement written in the non-procedural language of FIG. 6 has a structure as shown in FIG. Figure 7 shows the following: Reduce relation A, sort the results by a ₁ , and store the sorted results in working relation TA. Reduce relation B, sort the results by b ₁ , Store the sort results in the working relation TB.

Join the working relations TA and TB under the condition that a ₁ = b ₁ , sort the result by b ₂ , and use the sorted result as the working relation TAB.
Reduce relation C, sort the result by c ₂ , and store the sorted result in working relation TC. Set working relations TAB and TC under the condition that b ₂ = c ₂ . join and store the result in working relation TABC.

This means that.

FIG. 4 is a diagram showing an example of a mechanism for determining the field configuration of the minimum working relation.
In FIG. 4, numeral 5 indicates a minimum work relation determining mechanism, 6 a reference counter control mechanism, 7 a reference counter group, and 8 a reference counter. Inquiry conditions and processing order are input to the minimum work relation determining mechanism 5. This condition is specified by the statement following WHERE in FIG. 6, and the processing order is as shown in FIG. The reference counter control mechanism 6 has a reference counter group 7. The reference counter group 7 has a plurality of reference counters 8 corresponding to each of the fields cited in the inquiry, and each reference counter 8 has an initial value set. For example, in the case of the query shown in Figure 6, field _a1 appears twice in the sentence following GET, so the initial value of the reference counter 8 corresponding to field _a1 is set to "2", and field _a2 teeth
Since it appears once in the statement following GET, the initial value of reference counter 8 corresponding to field a ₂ is “1”.
It is said that The same applies hereafter. When determining the minimum field configuration of a working relation TA that reduces relation A and stores the result, the condition related only to the original relation A is a ₂ = 5, so the minimum working relation TA The relation determination mechanism 5 requests the reference counter control mechanism 6 to subtract "1" from the content of the reference counter corresponding to field _a2 . When the minimum work relation determination mechanism 5 finishes executing this request, it sends a counter reference request to the reference counter control mechanism 6. Upon receiving the counter reference request, the reference counter control mechanism 6 returns the contents of the reference counter group 7. At this time, since the contents of the reference counter corresponding to field _a2 are zero, the reference counter control mechanism 6 returns the contents of the reference counter group 7. Decide to have fields a ₁ and a ₂ . Thereafter, field configurations of other work relations are determined in the same manner. The mechanism in Figure 4 is
This is included in the optimization section shown in FIG. The minimum field configuration of the working relation will be discussed in more detail below.

FIG. 5 shows an example of processing in the interpreter. This example is for executing the process shown in FIG. The interpreter performs reduction or join according to the execution procedure statement created by the scheduler, creates a work relation to store the reduction result or join result according to the scheduler's decision, and stores the processing result in this work relation. Store.
For example, the working relation for storing the reduction result of relation A is field a ₁
The interpreter creates a working relation TA with _such a configuration and writes the result of reducing relation A into it.

As mentioned above, the inquiry shown in FIG. 6 is processed in the processing order shown in FIG. 7, for example. In this case, the least efficient method of not minimizing the field configuration of the working relations TA, TB, TC, and TAB required in the processing process is to change all the fields of the original relation to the fields of the working relation. It is to be included. That is, the configuration is as follows.

TA (a ₁ , a ₂ , a ₃ , a ₄ , a ₅ ) TB (b ₁ , b ₂ , b ₃ , b ₄ ) TC (c ₁ , c ₂ , c ₃ , c ₄ ) TAB (a ₁ , a ₂ , a ₃ , a ₄ , a ₅ , b ₁ , b ₂ , b ₃ , b ₄ ) The above simple but inefficient method,
By using the reference counter 7, it is possible to improve the efficiency of the method using the minimum field configuration. Hereinafter, a method for finding a working relation having a minimum field configuration will be explained with reference to FIG. FIG. 8 shows the transition state of the reference counter when performing the process shown in FIG. 7, and in FIG. 8, the * mark indicates the reference counter changed in the immediately previous step.

Regarding the condition _P1 (see FIG. 6) in which only the field of relation A is used in the step condition, the reference counter 8 corresponding to each field is decremented by the number of times it appears. The resulting state of the reference counter group 7 is as shown in FIG. 8B (condition _P1 is excluded from subsequent processing). Fields in relation A whose reference counter 8 is zero indicate that they are unnecessary as a field configuration of the working relation as a result of reduction, and the field configuration of the working relation TA can be configured as shown below. It is shown that.

TA (a ₁ , a ₂ ) Condition in which only field of relation B is used among step conditions (J1, J2, P2, P3, P4)
Perform the same processing as in step regarding P2 (condition P2 will be excluded from subsequent processing). The state of the reference counter group 7 obtained as a result is shown in FIG. 8C. It can be determined that the working relation TB should have the following field configuration.

TB (b ₁ , b ₂ , b ₃ ) Regarding conditions J1 and P3 that use only the fields of the two working relations TA and TB that join in the step conditions (J1, J2, P3, P4) Performs the same processing as the step (conditions J1, P3
will be excluded from further processing). The state of the reference counter group 7 obtained as a result is shown in FIG. 8D. It can be determined that the working relation TAB should have the following field configuration.

TAB (a ₁ , a ₂ , b ₁ , b ₂ ) Perform the same processing as in step 1 for condition P4, which uses only the field of relation C among step conditions J2 and P4 (condition P4 is (excluded from the scope). The state of the reference counter group 7 obtained as a result is shown in FIG. 8E. It has been determined that the working relation TC should have the following field configuration.

TC (c ₁ , c ₂ , c ₄ ) Step Performs the same processing as the step for condition J2, which uses only the fields of the two working relations TAB and TC among the remaining conditions (this (This completes checking all conditions.) The state of the reference counter group 7 obtained as a result is the 8th
As shown in Figure F. The field structure of the working relation (which matches the final relation of the query result) is as follows.

TABC (a ₁ , a ₃ , b ₁ , b ₂ , c ₁ , c ₄ ) As is clear from the above description, according to the present invention, the working relation used in query processing in a relational database system By using a reference counter, the field configuration of can be determined in a very simple way, and the efficiency of optimization can be improved.

[Brief explanation of the drawing]

Figure 1 is a diagram showing an overview of the relational database system, Figure 2 is a diagram showing the flow of processing in the logical data subsystem, Figure 3 is a diagram explaining the processing in the scheduler, and Figure 4 is for minimum work. FIG. 5 is a diagram showing an example of processing in the interpreter; FIG. 6 is a diagram showing an example of a query; FIG. 7 is a diagram showing an example of the query processing order. FIG. 8 is a diagram showing an example of the state transition of the reference counter. 1... Relational database management system, 2... Relational database, 3
... Logical data subsystem, 4 ... Logical storage subsystem, 5 ... Minimum work relation determination mechanism, 6 ... Reference counter control mechanism, 7 ... Reference counter group, 8 ... Reference counter.

Claims (1)

[Claims] 1 A relational system that determines a processing sequence including reduction processing or join processing according to inquiry information sent from the user, and stores the results of the reduction processing or join processing in the process of executing the processing sequence in a working relation. In a database system, a reference counter control mechanism having a plurality of reference counters that have a one-to-one correspondence with a field appearing in the query and whose initial value is set to the number of occurrences of the field in the query;
Requests the reference counter control mechanism to change the contents of the reference counter based on the inquiry condition and processing sequence, and stores the results of each reduction process or join process based on the contents of the reference counter group made up of the plurality of reference counters. and a minimum working relation determining mechanism for determining the field configuration of the working relation for the purpose of
The above-mentioned minimum working relation determining mechanism determines the field configuration of the working relation corresponding to the i-th process (i=1, 2...n, n is the last process), when the process is a reduction. If it is a process, it is checked whether a condition that uses only the field of the reduction source relation exists in the target query condition, and if it exists, the contents of the corresponding reference counter are added to the field. Requests the reference counter control mechanism to decrease by the number of occurrences, excludes the condition from the target query conditions, reads the contents of the reference counter group after changing the contents of the reference counter, and performs the reduction. Define the field configuration of the working relation for the process, and if the i-th process is a join process,
Check whether a condition that uses only the fields of multiple relations to join exists in the target query condition, and if it exists, decrement the contents of the corresponding reference counter by the number of times the field appears. is requested to the reference counter control mechanism, the condition is excluded from the target query conditions, and after the contents of the reference counter are changed, the contents of the reference counter group are read and the field of the work relation for the join processing is read. A method for determining a minimum work relation, characterized in that the method is configured to determine a configuration.