JPS59125461A - Outer join operation method in relational database - Google Patents

Abstract

PURPOSE:To realize a high-speed processing system by scanning simultaneously relation to be joined together, comparing to decide whether a join field value is larger, smaller, or is equal, etc., to perform outer joining, and to produce the relation of result. CONSTITUTION:A relational data base processing mechanism 1 accepts an inquiry from a user and passes it to a relational operation control part 2. The relational operation control part 2 checks the adequacy of the inquiry. An execution control part 6 sorts and extracts relation operation to determine its execution procedure, which is passed to an interpretation execution part 3 and executed. Then, an outer joining execution part 10 performs outer joining processing. In outer join operation processing, the values of joint field are extracted and compared successively from the starting record of plural sort relation to generate outer joining result responding to the result decided to be larger, smaller or is equal, and the result is inserted into the relation of results.

Description

Detailed Description of the Invention [Technical Field of the Invention] The present invention relates to an outer join processing method in a relational database, and in particular an outer join operation of two relations to a normal join operation (equal join), set difference, and set union. Rather than processing them as a combination of operations, we sort the join-to-i relations by the join field, then simultaneously scan the records of those two sorted relations, and determine the outer join result by determining the size and equality of the join fields. The present invention relates to a join processing method that can obtain records at high speed.

[Prior art and problems]

The conventional join operation processing method is equal join +
It only extracts and joins records that satisfy the conditions from two relations, and records that exist only in one relation are not included in the join result. An outer join operation joins records that have values that correspond to the join fields of both relations in the same way as an equal join operation, and also combines records that have values that only exist in the join fields of one relation into the resulting relation. can be included.

At this time, a field with a join result (relation attribute information) may not have a value, but an undefined value (N
It is common to supplement the IL value.

The outer join operation can be processed using the equality join operation and the set difference/set sum operation 7. The definition formula is shown below.

A (al, B2) Relation A consists of fields al and a2.

B (bl, bl) Relation B consists of fields bl and b2.

E(al, az, bl)=ACax=b1'] B is
Joy 7 - As a result of concatenating records with corresponding values in fields al and bl, relation E is shown to consist of fields al (or bl) and a2sb2.

Outer join operation 0(al, B2.bl) = E(al, B2.b
l)L)A 1 (al, B2.NIL)UBJ(
bt, N1. l) 2 tanx L AlCa1e 82
)=A-g(jL1+B2)B1(bl, bl)=A
-E(at, bl) is the result of the N operation, and - represents the set difference 'x'.

FIG. 1 shows the procedure for conventional outer join calculation processing. According to the conventional method shown in FIG.

In order to create the intermediate results Al and Bl and to obtain the final result O, it is necessary to scan each of E, At, and Blyg. Compared to the case where Oi is created directly from A and B, The disadvantage is that the processing time is long. As the number of records in the A and B relations increases, it causes a decrease in processing efficiency.

[Purpose of the invention]

In the outer join calculation process, the present invention assumes that the join target relations are sorted, and simultaneously scans these relations and determines the outer join between records based on the magnitude and equality comparison of the values of the two join fields. Purpose: To create a relatively high-speed processing method by performing a join join and creating a result relation without creating intermediate relations, compared to the conventional method that processes using a combination of equality join operations and set operations. It is said that

[Structure of the invention]

Therefore, the outer join calculation method in the relational database of the present invention combines multiple attribute information into 21 sets of records, treats a set of records of the same type as one relation, and creates a relational database composed of multiple relations. Obtain the result of outer join calculation on multiple relations non-join fields.In a data processing system, arrange the records in ascending order based on the values of the join fields of the relations to be joined. In addition to providing a sort relation creation process that creates a sort relation, multiple sort relations and sequential join fields from the first record of John! The present invention is characterized by an outer join operation processing step 7 for extracting and comparing results, creating an outer join result record 7 according to their size and equality, and inserting the records 7 into the χ result relation.

[Embodiments of the invention]

Outer join calculation processing is considered to be an extension of the equality join calculation processing method. The equal sign join operation process involves sorting the join targets by the relation join field, then scanning those sort fields - yFJ,
Extract only pairs of records with equal values, but skip records that do not match. Combine the extracted records to create a single record and insert it into the resulting relation.

According to the present invention, a processing method for realizing an outer join operation is devised by extending a processing algorithm for an equal join operation. FIG. 2 shows a flowchart of the outer join calculation process -f'. In Figure 2, the symbols have the following meanings.

tl, t2 Record scan pointer of relation A r l, r 2 Early record scan pointer of relation Record rx Record EOP of B pointed to by pointer r1! Scan end point (NIL, Lr 1 > NIL value and record trlk are combined in this order.

In the actor join calculation process, the following process is performed.

Step 5O Set as the first record for relations 11 and 12'ff, and set as the first record of relation B for rl and r2'll.

Step 810 Check whether conditions Y of t1=EOP and r1=EOP are satisfied. If Yes, it ends and N0O
), move to step 1].

Step 5ll Check whether the conditions of t1=EOP and r1\EOP are satisfied. If Yes, go to Stella 1812, and if No, go to Step 5134.

Step S12 <NIL, tr 1) Insert into vO, rl==r+l,
In other words, advance the pointer r1. Next, return to Stetsuno 810.

Step 513 Check whether the conditions of t1\FOP and rl=EOP are satisfied. If YES, the process moves to Stella 7'814, and if NO, the process moves to step 820.

Step S] 4 <ttl, NIj> is inserted into O and advances ts=tx+1, that is, pointer t17f. Next, the process moves to step 810.

Step 520 Check whether alLl=l)lyl.

If Yes, the process moves to step 821, and if No, the process moves to step 8.22.

Step S21 <ttl, trl") insert into ytO, rl=rl+l
and then moves to step 830.

Step 522 Check whether altl>bl, yi. Yes
If yes, the process moves to step 823, and if NO, the process moves to 824.

Step 823 <NIL, trl>'a' OK insert, r, = r, +l
Then, r2=r1, and the process returns to step 810.

Stetsuno 524 (tzx, NIL> insert into O, 11=11+1゜t
2=11, and the process returns to step 810.

Step 830 Check whether r1=EOP. If YES, the process moves to step S3], and if NO, the process moves to step 332.

Step 531 71=t1+1 and return to step 810.

Step 532 Check whether al/-1=blrl. If YES, proceed to step 833; if NO, proceed to step 834.
Move to.

Step 833 Insert <t 11 , t r 1>Y O, f1=
fl+1, and the process returns to step 830.

Step 834 Let jl=t1+1.

Step 535 Check whether z1=EOP or not. If yes, step 3
If the answer is NO, the process moves to step S37.

Step 836 Insert <N I L , t r 1 >Y O and r
Set l=r+1 and return to step SIO.

Step 537 Check whether altl=alt2 or not. If yes, proceed to step 838; if NO, proceed to step 33.
Move on to 9.

Step 838 rl=r2. That is, scan pointer r I Y −1
do. The process moves to the next busy step 832.

Step 839 t2=f1 meeting r2=f], and then return to step s20. .

FIG. 3 shows the structure of one embodiment of the present invention. In FIG. 3, 1 is a relational database processing mechanism, 2 is a relational calculation control unit, 3 is an interpretation execution unit, 4 is a data access unit, 5 is a query solution, a folding unit, 6 is an execution control unit, and 7 is a beam 8 is a join execution section, 9 is an equal sign join execution section, 10 is an outer join execution section, 11 is a database, 12 is a data dictionary, and ]3 is a relation t.

The relational database processor m1 includes a relational calculation control section 2, an interpretation execution section 3, and a data access section 4. The relational calculation control section 2 is composed of a query analysis section 5 and an execution control section 6, and the analysis execution section 3 is composed of a reduction note section 7 and a join execution section 8. The join execution unit 8 includes an equal sign join execution unit 9 and an outer join execution unit 10. The database [1] has a data dictionary [2] and a plurality of entries 13.

In FIG. 3, the relational database processing mechanism 1 passes user inquiries (relational calculation processing) to the relational calculation control unit 2. The relational arithmetic control unit 2 uses a query analysis unit 5 to check the syntax and meaning of the query for validity. The execution control unit 6 classifies and extracts seven relational operations from the correct query,
The implementation procedure will be determined. The query for which an execution plan has been established is passed to the interpretation execution unit 3 and executed. The interpretation execution unit 3 accesses the database j1 via the data access unit 4.
Data dictionary 1 that manages the attribute information of the rations in
2 or refer to multiple references with lol data 1
3 accesses. The data access unit 4 provides the basic functions of creating a relation as a result of reduction/sorting or joining processing, or scanning a target relation.

FIG. 4 shows a seventh specific example of a query including an outer join operation. This inquiry asks for the names and salaries of employees with a monthly salary of 300,000 yen or more from the employee/salary relation, and asks for employees who work in management department (ma) or engineering department (ma) from the affiliation relationship, and returns the results of both. This means that an outer join operation should be performed using the name as the join field.

FIG. 5 is an explanatory diagram of the content processed by the interpretation execution unit 3 when the query shown in FIG. It is expressed. The system extraction/sorting unit 7 extracts Irjyx records that satisfy each restriction condition from the salary relation and affiliation relation, sorts them in ascending order by the value of the name field, and then creates intermediate relations A and B for each. do. Further, when an inquiry includes an equal sign join operation for a plurality of relations, the equal sign join processing section 9 of the join execution section 8 creates a join operation result as a hot middle relation. Furthermore, the outer join execution unit [0] performs seven outer join processes for the intermediate relations A and H according to the arithmetic algorithm shown in FIG. 2, and creates the salary affiliation relation which is the final result.

The present invention performs outer join operation processing 7 as an extension of the processing algorithm for equal sign join operations, and can be considered as one of the basic means.

In the present invention, this is achieved by outer join operation Xyx scan for two relations, comparison of join field values, and merging of NIL values and records. le (
(3) The outer join operation for two rotations can be easily achieved by repeatedly applying the algorithm according to the present invention to two rotations.

〔Effect of the invention〕

As is clear from the above explanation, according to the present invention, it is possible to extract records that cannot be retrieved by the equal sign join calculation process (records where the join field value is only in one relation).
To provide outer join calculation processing that can provide great convenience to users. In addition, the outer join operation itself can be processed by an equal join operation or a set operation (phase and difference), but such operations require extra intermediate relations and scanning them multiple times, which may speed up the processing. According to this method, simultaneous scanning of two relations, size comparison of join fields, and N
This can be achieved only by combining IL values and records. The only difference from the equal sign join operation process is the presence or absence of NIL value/record join processing. According to the present invention for the above reasons,
This has the effect of significantly increasing processing speed compared to conventional methods.

[Brief explanation of drawings]

FIG. 1 is a diagram illustrating a conventional outer join calculation processing method, FIG. 2 is a diagram illustrating an outer join calculation processing method according to the present invention, and FIG. 3 is a system configuration showing a specific embodiment 7 of the present invention. 4 is a diagram showing a specific example 7 of a query including outer join operation processing Z, and FIG. 5 is a flowchart of an interpretation process for the query. 1... Relational database processing mechanism, 2...
... Relational calculation control unit, 3... Interpretation execution unit,
4... Data access section, 5... Query analysis section, 6... Execution control section, 7... Reduction sort section, 8... Join execution section, 9... Equal sign join execution section , ]0... Outer join execution part, 11...
Database, 12...Data dictionary, ]3...Relation. Patent Applicant Fujitsu Ltd. Representative Patent Attorney Yotsuta Kyotani Figure 1 [Figure 2 (A) Figure 5]

Claims (1)

[Claims] It collects multiple attribute information into 21 sets of records, treats a collection of records of the same type as one relation, has a relational database consisting of multiple relations, and performs actor joins on join fields. In a data processing system that performs arithmetic processing and obtains the result as a relation, it has a sort relation creation process that creates a sort relation in which records are arranged in ascending order based on the values of the join fields of the relations to be joined, and also includes An outer join calculation process that sequentially extracts and compares the join field values from the first record of the note relation, creates an outer join result record according to their size, equality, and inserts it into the result relation. An outer join processing method in a relational database characterized by having processes.