WO2018182058A1

WO2018182058A1 - Join method for relational database

Info

Publication number: WO2018182058A1
Application number: PCT/KR2017/003320
Authority: WO
Inventors: 강조현; 백성인; 한혁; 진성일
Original assignee: (주)리얼타임테크
Priority date: 2017-03-28
Filing date: 2017-03-28
Publication date: 2018-10-04
Also published as: KR101928819B1; KR20180109379A

Abstract

The present invention relates to a join method for a relational database, by which a join condition for a table having a range value including an integer column and having sequentially sorted indexed data values is reversed to reduce the number of operations for a comparison operation between two tables to be joined, thereby enabling range join to be efficiently performed, and as a table has more data to be compared, the performance of a range search can be improved.

Description

How to Join a Relational Database

The present invention relates to a method of joining a relational database, and more particularly, to a method of joining a relational database for performing an efficient range join on a table having a range of integer columns and having indexed data values sequentially sorted. It is about.

A relational database is a collection of data items consisting of a series of structured tables. The relational database is a database in which the structure of the database and the location of data to be constructed are predetermined. Relational databases also serve as a major data source for delivering data to the Web through Web applications.

The relational database expresses the relationship between the data and the data in the form of a two-dimensional table, and extraction operators for extracting specific data from the table include selection, extraction, set operation, Cartesian product, and join.

The select operation selects only records that satisfy a given condition among all records (rows), and the project operation is an operation that takes a record consisting of only the values of fields (columns). Is an operation that refers to a field (key) to concatenate two or more tables and takes the necessary data.

In a relational database, the join operation method is that most join conditions have equal comparisons (=) between fields, and the join condition is compared sequentially from the first row to the last row of the table to find a result that meets the join condition.

In the conventional relational database join method, a comparison operator (=,>, <,> =, <=) is used. Therefore, the larger the number of rows of the table to be joined, the lower the data retrieval performance and the efficient range join is impossible. have.

According to the present invention, an efficient range join can be performed by reducing the number of operations for a comparison operation between two tables to be joined by reversing the join condition for a table in which the indexed data values are sequentially sorted. Provides a join method for relational databases.

In embodiments, a method of joining a relational database may include joining two or more tables using a query processor, an index manager, and a join processor to perform a join operation that takes necessary data. Checking an inverse comparison condition having a range of integer columns, the range of values having a range in which ranges do not overlap each other, and sequentially arranged in size order; If a table to be joined satisfies the inverse comparison condition, checking a user join condition including a start condition and an end condition; Retrieving an index key value satisfying an inversion condition in which the starting condition is inverted from the plurality of tables to be joined using the index manager, and setting a previous index key value of the retrieved index key value as a join key; And performing a range join by comparing the join key and the user join condition, and returning a join execution result to the query processor.

Comparing the join key with the user join condition and performing a range join, and returning a join execution result to the query processor, sets a table having a small number of rows as a preceding table and a number of rows. Setting many tables as trailing tables; And comparing a user with a user join condition by searching for a join key in the following table by the number of data of the preceding table.

If the table to be joined does not satisfy the inversion comparison condition, the index manager searches for index information used in a user join condition, and compares the searched index information with the user condition for equality or general size comparison. The method may further include returning a join execution result to the query processor after performing the comparison operation.

If the table to be joined satisfies the inversion comparison condition, the step of confirming a user join condition including a start condition and an end condition includes an inversion symbol indicating an inversion of a start condition to the user join condition. The symbol is characterized by using at least any one of a symbol, a number, and a letter by user setting.

In the relational database joining method of the present invention, a table having a range of integer columns and an index of a comparison operation between two tables to be joined by inverting the join condition for a table in which the values of the indexed data are sequentially sorted It is possible to perform range join efficiently by reducing it, and the performance of range search can be improved as more tables have more data to compare.

1 is a diagram illustrating a configuration of a join system of a relational database according to an embodiment of the present invention.

2 is a flowchart illustrating a join method of a relational database according to an embodiment of the present invention.

FIG. 3 is a diagram for explaining a first range join method of FIG. 2.

FIG. 4 is a diagram for explaining a second range join method of FIG. 2.

Configurations shown in the embodiments and drawings described in the present invention are merely preferred embodiments of the present invention, and do not represent all of the technical idea of the present invention, the scope of the invention is the embodiments and drawings described in the text It should not be construed as limited by That is, since the embodiments may be variously modified and may have various forms, the scope of the present invention should be understood to include equivalents capable of realizing the technical idea. In addition, the objects or effects presented in the present invention does not mean that a specific embodiment should include all or only such effects, the scope of the present invention should not be understood as being limited thereby.

All terms used herein have the same meaning as commonly understood by one of ordinary skill in the art unless otherwise defined. The terms defined in the commonly used dictionary should be interpreted to correspond with the meanings in the context of the related art, and should not be interpreted as having an ideal or excessively formal meaning not explicitly defined in the present invention.

Referring to FIG. 1, a join system of a relational database includes a query processor 110, a join processor 120, and an index manager 130.

The query processor 110 analyzes a user's SQL query and provides a query result corresponding to a user join query based on indexed data of a relational database. When the input query is a query using a join, the user's join query Parse to generate and send data request command for user join condition including start condition and end condition set range condition, and query result corresponding to user join query based on indexed data of relational database to provide.

The query processor 110 includes a query compiler and an execution engine. The query compiler parses a query and delivers a query plan to the execution engine through optimization. The execution engine generates data request instructions to join the processor. Or pass it to the index manager 130.

The index manager 130 indexes the columns of the plurality of tables, and searches for the data by using the indexed index information of the corresponding tables.

The join processor 120 searches the index information used for the user join condition by using the index manager 130 and compares the two tables to be joined with the user join condition to return the join result to the query processor 110. .

The join processor 120 checks the inverse comparison condition that the tables to be joined have a range value composed of integer columns, and the range values are sequentially arranged in size order while the range values have values that do not overlap each other.

When the table to be joined satisfies the inverse comparison condition, the join processor 120 receives a data request command from the query processor 110, checks a user join condition including a start condition and an end condition, and checks the index manager 130. Search for index key value that satisfies inversion condition by inverting start condition, set previous index key value of searched index key value as join key, perform range join by comparing join key and user join condition, and then join The execution result is returned to the query processor 110.

In this case, when the table to be joined satisfies the inversion comparison condition, the join processor 120 uses the index manager 130 through the inversion symbol of the user join condition by including an inversion symbol indicating the inversion of the start condition. Finds the index value by inverting the starting condition based on the indexed data.

However, if the table to be joined does not satisfy the inverse comparison condition, the join processor 120 searches for index information used in the user join condition using the index manager 130, and compares the retrieved index information with the user join condition. After performing an equal range join or a general range join that performs a comparison operation through equal comparison or general size comparison, the join execution result is returned to the query processor 130.

2 is a flowchart illustrating a join method of a relational database according to an exemplary embodiment of the present invention, FIG. 3 is a diagram illustrating a first range join method of FIG. 2, and FIG. 4 is a diagram illustrating the second range join method of FIG. 2. It is a figure explaining.

2 to 4, in the relational database join method, when a structured query language (SQL) query is input from the query processor 110, the join operation is confirmed through SQL analysis to check a user join condition. ~ S4)

The two tables to be joined are range values composed of integer columns, and the range values have values that do not overlap each other, and the table satisfies the inverse comparison condition if the indexed data values are sorted in ascending or descending order. do.

Therefore, if the table to be joined satisfies the inverse comparison condition, the join processor 120 finds an index key value that satisfies the inversion condition that inverts the starting condition by checking the user join condition, and the previous index key value of the index key value. The first range join method performs a comparison operation of comparing the join key and the user join condition after using the join key. (S6)

In this case, the join processor 120 performs the first range join method when the inversion symbol is confirmed in the start condition among the user join conditions. The inversion symbol may include at least one of a special symbol such as 또는, ＃, or a user setting symbol, a number, or a character. Any one or more can be used.

For example, in the first range join method, the join processor 120 starts the condition A.ip> @ when the user join condition is "A.ip> @ B.Start and A.ip <= B.End". B.Start, and the end condition is A.ip <= B.End.

The join processor 120 sets the table A to the preceding table and the table B to the following table when joining the table A and the table B as shown in FIG. The data values in table B are sorted in ascending order.

In addition, the join processor 120 uses the index manager 130 to obtain an index key value satisfying the inversion condition (A.IP <B.Start) in which the start condition (A.ip> @ B.Start) is inverted in the trailing table. The join key is compared with the range condition using the previous index key value of the index key value found in the trailing table as the join key.

At this time, if the range condition is satisfied, A.ip is included in the range of the index kit value, so the join is successfully performed. If the range condition is not satisfied, there is no range including A.ip. .

That is, the join processor 120 searches the table B for an index key value satisfying A.ip <B.Start for A.ip = 10, which is the first data in table A, and finds the index key value 31. 31 A.ip> B.Start and A.ip <= B, where A.ip (10) is the range condition in the range value (1-30) of the column with the join key, with 1 as the join key. If End is satisfied, A.ip (10) is included in the corresponding range value, so A.ip (10) can successfully join the column of “Code US”.

Accordingly, the join processor 120 performs a comparison operation for joining the range as much as the number of data in Table A, and then returns the result of joining between Table A and Table B to the query processor 110, and the query processor 110 joins the join. The query result is provided to the user by reflecting the execution result. (S8 ~ S10)

Meanwhile, when the table to be joined does not satisfy the inverse comparison condition, the join processor 120 is a range condition in which the user join condition is “A.ip> = B.Start and A.ip <= B.End”. 4, the second range join method is performed (S7).

The second range join method is a general equal range join or a general range join. The join processor 120 uses the index manager 130 to sequentially combine all the data of the table B from the data of the first row of the table A and the range condition. Performs a comparison operation to compare.

That is, the join processor 120 performs an equal range join if there is a comparison operator including an equal comparison (=) in the range condition, and a general range join if there is a comparison operator including only an inequality (<,>) without an equal comparison (=). Do this.

The join handler 120 compares the table B sequentially to satisfy the start condition of A.ip> = B.Start, and if the start condition is satisfied, the join processor 120 satisfies the end condition of A.ip <= B.Start. do.

The join handler 120 determines the range value of that column if A.ip satisfies both the start condition and the end condition for A.ip> = B.Start and A.ip <= B.End for a particular column of table B. If the start condition and the end condition are not satisfied, it is determined that it is not included in the range value of the corresponding column. Therefore, the join processor 120 outputs the join result of as many times as the number of data of the table A times the number of data of the table B.

The join processor 120 returns the join result to the query processor 110, and the query processor 110 joins Table A and Table B to reflect the join result and provides the query result to the user. S10)

As described above, in the relational database joining method according to an embodiment of the present invention, two tables to be joined in the relational database are indexed data, have range values composed of integer columns, and range values do not overlap ranges with each other. When the inverse comparison conditions are sequentially ordered in size, the first range join method is performed to reduce the number of operations of the comparison operation, thereby efficiently performing the range join, thereby improving the performance of the range search on the indexed data. .

In addition, the join method of the relational database according to an embodiment of the present invention uses a general comparison operator when two tables to be joined in the relational database are indexed data but the data values are range values consisting of integer columns but are not sequentially sorted. To perform a second range join method that performs an equal range join or a general range join.

Although described above with reference to a preferred embodiment of the present invention, those skilled in the art will be variously modified and changed within the scope of the invention without departing from the spirit and scope of the invention described in the claims below I can understand that you can.

Claims

In a join processor coupled to a query processor and an index processor, in a join method of a relational database for joining two or more tables to perform join operations that take necessary data,

Defining an inversion comparison operator for processing an inversion comparison operation when the table has a range value composed of integer columns, and the range values have values that do not overlap with each other and are sequentially sorted in size order;

In performing the join operation, when the operator is an inverted comparison operator, an index key value that satisfies the inversion condition that inverts the starting condition is searched for in a plurality of tables to be joined, and the previous index key value of the searched index key value Setting the join key; And

And performing a range join by comparing the join key with the user join condition and returning a join execution result to the query processor.
The method of claim 1,

After performing the range join by comparing the join key with the user join condition, and returning a join execution result to the query processor,

Setting a table having a small number of rows as a preceding table among the plurality of tables, and setting a table having a large number of rows as a subsequent table; And

And performing a comparison operation of searching for join keys in the trailing table by the number of data of the preceding table and comparing the join keys with the user join condition.
In a join processor coupled to a query processor and an index processor, in a join method of a relational database for joining two or more tables to perform join operations that take necessary data,

Confirming an inverse comparison condition in which the table has a range of integer columns, the range of values having a range in which ranges do not overlap each other, and which are sequentially arranged in size order;

If a table to be joined satisfies the inverse comparison condition, checking a user join condition including a start condition and an end condition;

Retrieving an index key value satisfying an inversion condition in which the starting condition is inverted from the plurality of tables to be joined using the index manager, and setting a previous index key value of the retrieved index key value as a join key; And

And performing a range join by comparing the join key with the user join condition and returning a join execution result to the query processor.
The method of claim 3,

After performing the range join by comparing the join key with the user join condition, and returning a join execution result to the query processor,

Setting a table having a small number of rows as a preceding table among the plurality of tables, and setting a table having a large number of rows as a subsequent table; And

And performing a comparison operation of searching for join keys in the trailing table by the number of data of the preceding table and comparing the join keys with the user join condition.
The method of claim 3,

If the table to be joined does not satisfy the inversion comparison condition, the index manager searches for index information used in a user join condition, and compares the searched index information with the user condition for equality or general size comparison. And performing a comparison operation through the method, and returning a join execution result to the query processor.
The method of claim 3,

When the table to be joined satisfies the inverse comparison condition, the step of checking a user join condition including a start condition and an end condition may include:

And a inverting symbol for inverting the starting condition in the user join condition, wherein the inverting symbol uses at least one of a symbol, a number, and a character by user setting.