JPH03223965A - Inequality sign condition connection system for related data base system - Google Patents

Abstract

PURPOSE:To shorten the overall processing time by obtaining a pair of record Nos having different column values based on an assembly of record Nos and the pairs of record Nos. CONSTITUTION:A retrieving request input means 1 is provided together with a retrieving result output means 2, an inequality sign condition connection means 3, an equal sign condition connection means 4, a record No acquiring means 5, an input/output control means 6, a secondary storage 7, and a data base 8. Then a record No assembly 1 consisting of the Nos of all records included in a table 1 is obtained together with a record No assembly 2 consisting of the Nos of all record included in a table 2. The pairs of record Nos of tables 1 and 2 including the pairs of same values of columns 1 and 2. Based on these pairs of record Nos, a pair of record Nos including the unequal values of both columns 1 and 2 is obtained. As a result, the comparing frequency can be reduced between the values of columns 1 and 2. Then it is possible to shorten the processing time required for production of a pair of desired record Nos.

Description

Detailed Description of the Invention (Field of Industrial Application) The present invention relates to an inequality conditional join method in a relational database system that uniquely identifies records stored in a table in a database by an identifier called a record number. When the user specifies table 1, table 2, column 1, which is a column in table 1, and column 2, which is a column in table 2, in the records of table 1 and table 2, the value of column 1 and the value of column 2 are specified. This paper relates to an inequality conditional join method that outputs sets of record numbers that are unequal.

(Prior art) A conventional method for outputting a set of record numbers of records in Tables 1 and 2 specified by the user in which the values in column 1 and column 2 are not equal is as follows. It is.

First, read the first record of Table 1, then read the records of Table 2 sequentially from the beginning to the end.
Each time a record is read, compare the value in column 1 of the current record in Table 1 with the value in column 2 of the current record in Table 2, and only if they are not equal, output the pair of current records in Table 1 and Table 2. do. By repeating the same process every time the next record is read from Table 1,
Compare the values in column 1 and column 2 for all combinations of records in Tables 1 and 2.

(Problem to be Solved by the Invention) In order to compare the values of columns 1 and 2, the records of Tables 1 and 2 must be read from the secondary storage device, but the input/output processing in the secondary storage device The processing speed is extremely slow compared to the processing speed of a central processing unit. Therefore,
By reducing the number of comparisons between columns 1 and 2, the number of input/output processes in the secondary storage device can be reduced, and the overall processing time can be shortened.

However, in the conventional method, since all combinations of records are compared, it takes a lot of time to create a target set of record numbers.

(Means for Solving the Problems) The inequality conditional join method in the relational database system of the present invention has the following advantages: When the user specifies Table 1, Table 2, Column 1 which is a column of Table 1, and Column 2 which is a column of Table 2, in the records of Table 1 and Table 2, Column 1
This is an inequality conditional join method that outputs a set of record numbers in which the value of column 2 is not equal to the value of column 2. a record number acquisition means for creating a record number set 2 consisting of the record numbers of all records included in the table; and a combination of records in Table 1 and Table 2 in which the value in column 1 and the value in column 2 are equal. an equal sign condition combination means for creating a record number set 1 consisting of record numbers; and an inequality condition combination means for creating a set of record numbers that are not equal to the value of column 2.

(Example) Next, the present invention will be explained in detail with reference to the drawings.

FIG. 1 is a schematic block diagram showing one embodiment of the present invention. This embodiment includes a search request input means 1, a search result output means 2, an inequality condition combination means 3, an equality condition combination means 4, a record number acquisition means 5, an input/output control means 6, a secondary storage device 7, and a database 8. It is composed of. The user is
Tables 1, 2, columns 1 and 2 are specified through the search request input means 1, and the search results are outputted through the search result output means 2. The equal sign condition combination means 4 is a means for outputting a set of record numbers in which the values of column 1 and column 2 are equal in the specified records of table 1 and table 2. If an index has been built, use that index; if no index exists, sort Tables 1 and 2 by the values in column 1 and column 2, respectively, so that the values in column 1 and column 2 are To quickly create sets of record numbers with equal combinations.

The input/output control means 6 controls the process of reading records of a specified table from a database 8 built on a secondary storage device 7. The equality condition combination means 4, the input/output control means 6, the secondary storage device 7, and the database 8 are all similar to those used in a normal relational database system. The record number acquisition means 5 acquires the record numbers of all records stored in a specific table, and creates a set of record numbers. In actual database systems, there is a system that handles three-valued logic, which stores a NULL value for a column whose value is omitted when adding a record, and makes comparisons with this NULL value indeterminate, neither true nor false. However, in such a system,
Creates a set of record numbers for records where the value of an unspecified column is not a NULL value.

FIG. 2 is a flowchart showing the processing of the inequality condition combination means 3. The processing of the inequality condition combination means 3 will be explained with reference to FIG. The inequality condition combination means 3 first inputs Table 1, Table 2, Column 1 and Column 2 specified by the user through the search request input means 1 (Step 201). Next, specify Table 1, Table 2, Column 1, and Column 2, call the equality condition combination means 4, and create J, which is a set of record numbers in which the values of Column 1 and Column 2 are equal.Step 202 ), J are rearranged in ascending order of priority according to the record numbers in Table 1 and the record numbers in Table 2 (step 203). Next, the record number acquisition means 5 is called to create R1, which is a set of record numbers of all records stored in Table 1 (step 204), and further the record numbers of all records stored in Table 2 are created. R is a set
2 is created (Step 205), and R2 is rearranged in ascending order of record number (Step 206). Next, K, which is an area for storing a set of record numbers in which the values of column 1 and column 2 are not equal, as a result of a combination of inequality conditions, is initialized to be empty (step 207). Finally, record number inversion processing creates a set of record numbers with unequal combinations of column 1 and column 2 values based on R1, R2, and J created in each of the above processes, and adds this to K. I do. This processing includes record number reversal processing 1 (step 208) that creates a combination in which the record number in Table 1 is included in J out of the set of target record numbers; The record number reversal process 2 (step 209) creates combinations that are not found, and the set of record numbers created in each process is added to K.

FIG. 3 is a flowchart showing the processing procedure of record number inversion processing 1 in step 208 of the flowchart of FIG. The record number inversion process 1 will be explained with reference to FIG. Record number inversion processing 1 first assigns the first set of record numbers of J, which is a set of record numbers with equal values in column 1 and column 2, to variable j (step 3).
01), it is determined whether or not a set of record numbers with the same values in column 1 and column 2 exists in j. Step 302>; if not, the process ends. If a set of record numbers exists, the record number in Table 1 is extracted from the set of record numbers indicated by j and assigned to variable j1 (step 303). Next, the record number in Table 2 is extracted from the set of record numbers indicated by j and assigned to variable j2 (step 303).

Next, the record number indicated by jl is deleted from R1, which is the set of record numbers in Table 1 (step 305).

Next, read the first record number from R2, which is a set of record numbers in Table 2, and assign it to variable r2 (
306). Next, compare the record number of Table 2 assigned to r2 and the record number of Table 2 assigned to j2 (steps 307 and 308), and if the record number of r2 and j2 are equal, The next record number after 2 is read from R2 and assigned to variable r2 (step 309). Then, the next set of record numbers is read from J and assigned to variable j (step 310), and it is determined whether or not record number r2 in Table 2 exists (step 311), and if it does not exist, step The process returns to step 302 and continues the process. If r2, which is the record number in Table 2, exists, it is determined whether or not there is a record number set in j that has the same value in column 1 and column 2 (step 312), and the set of record numbers exists. If so, it is determined whether the record number in Table 1 of the record number is the same as the previous one by comparing it with the current value of jl (step 313). If the set of record numbers does not exist or if the record number in Table 1 is different from the previous record number, the record number in Table 1 indicated by jl and r2
The set of record numbers in Table 2 shown by is added to K that stores the inequality condition combination result (step 315). After this, read the next record number in Table 2 from R2 and
(step 316), the process returns to step 315 and continues processing until the next record number no longer exists (step 317), and when the next record number no longer exists, the process returns to step 302. If the set of record numbers read in step 310 exists and the value of the record number in Table 1 is the same as the previous one, j
Step 314: Assign the next record number of Table 2 to 2.
), the process returns to step 307 to continue the process. In the comparison of r2 and j2 in steps 307 and 308, if the record number in Table 2 indicated by r2 is larger than the record number indicated by j2, the process proceeds to step 310 and from then on, r2 and j2 are equal. Perform the same processing as in the case.

If the record number of r2 is smaller than the record number of j2, the record number of Table 1 indicated by jl and r2
Add the set of record numbers of Table 2 shown by to K that stores the inequality condition combination result (Step 318), read the Table 2 record number from R2 and assign it to r2 (Step 319), the record number of Table 2. It is determined whether or not r2 exists (step 320). If it exists, the process returns to step 307; if it does not exist, the process returns to step 3.
At step 10, the next set of record numbers is read from J and assigned to j, and the process returns to step 302 via step 311 to continue the processing.

FIG. 4 is a flowchart showing the processing procedure of record number inversion processing 2 in step 209 of the flowchart of FIG. The record number inversion process 2 will be explained with reference to FIG.

Record number reversal processing 2 first begins with table 1 remaining in R1.
Read the first record number from the record number 2 and assign it to variable r1 (step 401), determine whether the record number rl in Table 1 exists (step 402), and if it does not exist, end the process . If rl, which is the record number of Table 1, exists, read the first record number from the record numbers of Table 2 stored in R2 and assign it to variable r2 (step 403), r2, which is the record number of Table 2.
(step 404), and if so, add the pair of the record number of Table 1 indicated by rl and the record number of Table 2 indicated by r2 to K that stores the inequality condition combination result. In step 405), the next record number in Table 2 is read from R2, and the process returns to step 404 to continue the process.

In the judgment at step 404, if r2, which is the record number in Table 2, does not exist, the next record number in Table 1 is read from R1 and substituted into rl (Step 407).
, and returns to step 401 to continue the process.

When the process is finally completed, a set of records in which the value of column 1 and the value of column 2 are not equal are stored in K as a result of the inequality condition combination.

(Effect of the invention) In the inequality conditional join method of the present invention, a record number set 1 consisting of the record numbers of all records included in Table 1 is used.
Create a record number set 2 consisting of the record numbers of all the records included in Table 2, and a set of record numbers from Tables 1 and 2 in which the values in column 1 and column 2 are equal. First, create a set of record numbers in which the value in column 1 and the value in column 2 are not equal.

In the process of outputting a set of record numbers with the same values in column 1 and column 2, an index built for column 1 or column 2 is used, or if an index does not exist, Table 1 is used. By sorting Table 2 and Table 2 by the values in Column 1 and Column 2, it is possible to reduce the number of times the values in Column 1 and Column 2 are compared, which is required to create the desired set of record numbers. Processing time can be greatly reduced.

[Brief explanation of drawings]

FIG. 1 is a block diagram showing an embodiment of the present invention, FIG. 2 is a flowchart showing the processing of the inequality condition combination means 3, and FIG. 3 is the processing of record number inversion processing 1 of step 208 in the flowchart of FIG. Flow chart showing the procedure. FIG. 4 is a flow chart showing the processing procedure of the record number inversion process 2 at step 209 in the flow chart of FIG. DESCRIPTION OF SYMBOLS 1... Search request input means, 2... Search result output means, 3... Inequality condition combination means, 4... Equal sign condition combination means, 5... Record number acquisition means, 6... Input/output control means, 7... secondary storage device, 8... database.

Claims (1)

[Scope of Claims] In a relational database system in which records stored in tables in a database are uniquely identified by an identifier called a record number, a user requests a table 1, a table 2, and a column 1 which is a column of table 1. Column 2 is specified as a column in Table 2, and column 1 is specified in records in Table 1 and Table 2.
This is an inequality conditional join method that outputs a set of record numbers of combinations in which the value of and the value of column 2 are not equal, the record number set 1 consisting of the record numbers of all records included in the table 1, and the table 2. a record number acquisition means for creating a record number set 2 consisting of the record numbers of all records included in the table; and a combination of records in Table 1 and Table 2 in which the value in column 1 and the value in column 2 are equal. an equal sign condition combination means for creating a record number set 1 consisting of record numbers; An inequality condition combination method in a relational database system, comprising: an inequality condition combination means for creating a set of record numbers whose combinations are not equal to the values of column 2.