JPS61170840A - Join processing system by partial convolution joining method - Google Patents

Abstract

PURPOSE:To perform the processing in a high speed by providing a means which generates an intermediate table A' with respect to a table A and a means which generates an index from a table B and accessing the table just after the table A' is generated. CONSTITUTION:In a data processor provided with the processing function for relational data bases on a file memory 4, the join agreement condition which a channel processor 3 reads out from a main storage device 2 by the input/ output instruction of a CPU1 is used to join tables A and B. In this case, an index 22 and a column list 23 are provided on the main storage device 2 to perform the join processing and the generation processing of an intermediate table 21 in a high speed. Pairs of data of columns of a table B42 and their record address are generated preliminarily as the index 22 to fetch directly record addresses of column data. The intermediate table A'21 is generated, and the column list 23 or the like is set to obtain data items.

Description

[Detailed Description of the Invention] [Industrial Application Field] In a computer system equipped with a relational database (RDB) processing mechanism, tables A and B are searched based on equivalence join conditions specified by an inquiry command, and the corresponding The present invention relates to a JOIN processing method using a biased join method to quickly search for records specified by a command.

BACKGROUND OF THE INVENTION With recent advances in computer utilization technology, user requests for data processing have become increasingly diverse and complex.

In response to this situation, conventional databases have problems such as requiring a lot of specialized knowledge and not being able to utilize information in a timely manner.Relational databases (RDBs) have been proposed as a solution to these problems.
) system is known.

The relational database (RDB) system has the following features: ■ Ease of construction and expandability, such as the ability to easily introduce the database from the initial stage, handle data with minimal knowledge, and easily change the design after introduction. , flexible.

■ It is possible to operate the database optimally according to the business content.

■ A series of tasks such as database creation, operation, and maintenance can be easily performed from the user terminal.

It has the following characteristics.

Furthermore, when we look at relational database (RDB) systems from the perspective of the operating language used by users, (11) users do not need to know how data is stored or how to access it, they just want to know what they want. All you have to do is specify the name of the data (column name).

(2) Instead of operating on a record-by-record basis, it is possible to operate on a set of records that satisfy a condition at once.

It has the following characteristics.

Among these data manipulation languages (inquiry commands), the most basic one is 5ELECT: Specifies the search target (column name). (What) FROM: Specify each table to be joined. (From where) Wl (BRI!: Specify join conditions and constraint conditions. (
Under what conditions is there a
WHt! This means that the object that satisfies the RE constraints is to be processed.
-In this way, relational database (RDB)
A major operational feature of the system is that it has a function to dynamically join tables to be operated on under conditions specified by an inquiry command, that is, a JOIN function.

The methods for joining the above tables include: (a) Renormalization join method: This method uses AX
This is a join method to obtain B (direct product), in which one record of table A is scanned, and in contrast, all records of table B are scanned.

This process is performed on all records of table A. Therefore, the number of scans of table A is once.

The number of scans for table B is for all records in table A.

(b) Match join method: This method joins table A and table B using the equivalence join conditions specified by the query command, such as al.
This is a join method using bi, in which the join table is sorted in advance by the key column of each equivalence join condition, and tables A and B are scanned in parallel once each.

Both methods have the problem that it takes time to search for the required data (records).

However, with the recent diversification of data processing operations in the industry, the above-mentioned relational database (RD
B) systems are required, and performance improvements in such database systems are required.

(Prior Art) FIG. 3 shows input/output issued by a central processing unit (CPtl) 1 in a data processing device equipped with a processing function for a relational database (RDB) built on a file memory (PM) 4. Based on the instructions, the channel processing unit (CHP) 3 stores the main storage unit (MSU) 2.
A schematic diagram of the join method when performing a JOIN from the equivalence join condition indicated by the inquiry command read from .

This is shown in .

First, the inquiry command for the required data is set as follows: 5ELIIICT: column al, b2FROM: table A, B WHERE: al=bl AND a2=10.

In this case, ■ First, in order to obtain the optimal search access bus, table A41 is set to conditions (
Specifically, the so-called 5ELEC is narrowed down by a2=10).
TION is performed and an intermediate table A°21 is generated on the main storage unit (MSU) 2.

■ Regarding the table A°21 and table B42,
The equivalence join condition (al
=bl), the main storage unit (MSU) 2. File memory (FM)
4. Scan in the forward direction to find data that meets the above conditions.

The method was adopted.

For example, when generating the intermediate table A'21, all the column parts expressed in the inquiry command are scanned, and unevaluated columns are generated in the intermediate table A'.
There were 21 data items.

[Problem that the invention seeks to solve]

Therefore, in the conventional method, when intermediate table A'21 and table B42 are joined (JOIN) L using the equivalence join condition (al=bl), and necessary data is obtained, table A'21 is The search has been narrowed down to the optimal search conditions using the WHERE condition as the narrowing condition, but
Regarding table B 42, there was a problem in that in the worst case, it was necessary to scan all the records, and an average response could not be obtained due to variations in key data values.

Also, when generating the intermediate table A'21, all the columns expressed in the query command were scanned, so the more complex the command, the larger the number of intermediate tables. There is a problem in that the number of times the inquiry command portion is scanned increases in proportion to the number of questions.

*arq′-z″′E″, iai*o*, +, cm, b
・"7"z6'l>4.

A table join processing method, that is, a method of speeding up JOINs based on equivalence join conditions by using a biased join method that combines the advantages of (a) renormalization join method and (b) matched join method; To provide a method for simplifying the process of determining items in an intermediate table and speeding up the process by counting down the number of times a column described in an inquiry command is referenced when generating an intermediate table. This is the purpose.

[Means for solving problems]

The purpose of this is: (1) To join (JOIN) table A and table B in a relational database (RDB),
A first means for generating an intermediate table A' by selecting only the records meeting the conditions specified by the inquiry command for the table A, and an index for searching the record address of the data from the column data of the table B. After generating the table A' by the first means, one record of the table A is searched, and the column data of the record is indexed by the index generated by the second means. Table B is directly accessed using the record address obtained as the search range.

(2) The number of columns described in the inquiry command to generate the intermediate table A'.

The number of references to the column is set in advance as a table, and the number of columns used in the conditional expression evaluated when executing the above inquiry command is counted down from the number of references in the table, so that the number of references to the table is 1.
times or more as unevaluated columns, the intermediate table A
Let it be the data item of ゛.

This is achieved by the JOIN processing method using the biased join method of the present invention.

[Effect]

That is, according to the present invention, (1) In order to JOIN intermediate table A' and table B using the equivalence join condition specified by the inquiry command, the data in the columns of table B and the record address of the data must be A pair is generated as an index, and after one record of the table A' is searched, the index is searched using the column data of the table A', and the record address for the column data is directly fetched.

■ In addition, to generate the above intermediate table A, a list of columns described in the above inquiry command and the number of times the columns are referenced are set in advance, and evaluated for each execution procedure of the inquiry command. The number of columns used in conditional expressions, etc. is counted down from the number of references in the column list, and columns with a reference number of 1 or more are treated as unevaluated and set as data items in the intermediate table A''.

Therefore, in the JOIN process (■), it is possible to access table B and obtain search results in as many records as table A', which is about 20 times faster than the conventional "matching method".
You can search by 50% response and number of accesses.

Furthermore, in the intermediate table creation process (2), the column portion of the inquiry command is scanned only once regardless of the number of intermediate tables, which has the effect of speeding up the creation of the intermediate table.

〔Example〕

Embodiments of the present invention will be described in detail below with reference to the drawings.

FIG. 1 is a diagram schematically showing an embodiment of the present invention regarding JOIN processing, and FIG. 2 is a diagram illustrating a method for creating an intermediate table. The same reference numerals as in FIG. It shows.

In FIG. 1, an index 22 provided on the main storage unit (MSU) 2 is a means necessary for speeding up the JOIN process, and a column list 23 on the same main storage unit (MSU) 2 is This is a necessary means to speed up the process of creating intermediate tables.

First, according to FIG. 1, the inquiry command, 5ELI
ECT: Column al, b2 FROM: Table A, B W) IERE: al=bl AND Based on the equivalence join condition (al=bl) indicated by a2=10
- The procedure for high-speed JOIN of table A41 and table B42 of a relational database (RDB) will be explained.

(a) When implementing the present invention, table A41. From the equivalence join condition (al, bl) of table B 42, for column b1 of table B 42, an index 22 with each data as a key and a record address where the data is stored as a pointer value is set in the main storage ( MSU) 2.

(b) From table A 41, by using the intermediate table creation processing procedure described later, the narrowing conditions regarding table A specified by the above inquiry command, for example, rWHE.
Rt! : Based on a2=10J, an intermediate table A''21 is generated on the main storage unit (MSU) 2.

(C) After searching one record of intermediate table A21, the data in the al column, for example "1', "1000"
0', searches the index 22 of the table B 42, alternatively reads the record address related to the data, accesses the table B 42 on the file memory (FM) 4, and directly directs the data. to fetch.

As a result, a fetch access to table B 42 is
The desired search data can be obtained using the number of records in table A'21 (two records in this example).

Data in column a1 of intermediate table A21 °1.

Or, a method of searching the index 22 for .degree.1oooo' and alternatively obtaining a record address for the data is disclosed in Japanese Patent Application No. 53-137650, for example.

This method arranges pair information of key values and pointer values in a predetermined order, and has an inverted file in which indexes for indexing are extracted in a hierarchical structure based on the arrangement, and retrieves the data to be searched. (key) as the index of the above pair information, index the inverted file,
This system extracts the data (pointer) to be searched by following the 8-point index, and has the feature that the desired data can be obtained quickly and uniformly.

Next, a method for creating the intermediate table A°21 will be explained with reference to FIG. 2 while referring to FIG.

(d) First, as shown in the "Initial Settings" column in Figure 2,
The column list and the number of references to that column written in the above inquiry command are stored in the column list table 23.
is set in advance on the main storage unit (MSU) 2.

To be more specific, when looking at the above inquiry command, column a1 is referenced twice, column a2 is referenced once, column b1 is referenced once, and column b2 is referenced once, so the second The initial setting values in the figure are set to the above values.

(e) Next, narrow down table A with condition ra2=10.”
So, do 5 ELECTION. The number of columns used in the conditional expressions etc. evaluated in this execution procedure is counted down from the number of references in the list 23 above. As a result, in the intermediate data structure at the execution stage, items that have been referenced 1 or more in the column list 23 are treated as unevaluated items and are used as intermediate data items at that time.

In this example, ra2=10 J and 5ELECTIO
N, so only the value of column a2 is counted down to 1=OO.

At this time, if you look at table A, column list 23
Since only "al" has a reference count of 1 or more, the intermediate table related to table A is table A'21 in FIG.

(f) When intermediate table A"21 and table B are JOINed, ral=bl J becomes the evaluation condition expression, so the values of columns al and bl are, respectively, column al:2=+1 column bl:1 =00, and the data items when creating the intermediate table at this stage are as clear from Figure 2.
The columns are al and b2.

In this way, with this intermediate table creation method, intermediate data items can be obtained by simply counting down the number of column references each time an inquiry command is executed, making it possible to easily and quickly determine the structure of intermediate data. There is a characteristic that it can be done.

〔Effect of the invention〕

As explained in detail above, the bias of the present invention
The JOIN processing method using the I-included join method is as follows: ■ To JOIN intermediate table A' and table B using the equivalence join condition specified by the inquiry command, the column data of table B and the record address of the data must be specified in advance. After searching one record of the above intermediate table A', search the index using the column data of the table A', and directly write the record address for the column data. fetch

■ In order to generate the above intermediate table 8, a list of columns described in the above inquiry command and the number of times the columns are referenced are set in advance, and evaluated for each execution procedure of the inquiry command. The number of columns used in conditional expressions, etc. is counted down from the number of references in the column list above, and columns with a reference number of 1 or more are treated as unevaluated and set as data items in the intermediate table A. be.

Therefore, in the JOIN process (■), it is possible to obtain search results in as many accesses to table B as there are records in table A'.
You can search by ~50% response and number of accesses. Furthermore, in the intermediate table creation process (2), the column portion of the inquiry command is scanned only once regardless of the number of intermediate tables, which has the effect of speeding up the creation of the intermediate table.

[Brief explanation of drawings]

FIG. 1 is a diagram schematically showing an embodiment of the present invention. FIG. 2 is a diagram explaining a method of creating an intermediate table. FIG. 3 is a diagram schematically showing a JOIN processing method according to the prior art. It is. In the drawing, ① is the central processing unit (CPtl), 2 is the main memory (
MSU). 21 is an intermediate table A I, and 92 is an index. 23 is a column list. 3 is a channel processing unit (CHP). 4 is file memory (FM). 41 is table A, 42 is table B. are shown respectively.

Claims (2)

[Claims] (1) In a computer system equipped with a relational database (RDB) processing mechanism, conditions specified by an inquiry command for table A to join (JOIN) table A and table B of the relational database (RDB). A first means for selecting only the records of , and generating an intermediate table A′, and a second means for generating an index for searching the record address of the data from the column data of table B, After generating table A' by the first means, search one record of table A', and directly search the column data of this record using the record address obtained as the search range of the index generated by the second means. A JOIN processing method using a biased join method characterized by accessing table B. (2) To generate the above intermediate table A', the number of columns described in the above inquiry command and the number of times the columns are referenced are set in advance as a table, and the conditional expression evaluated when executing the above inquiry command is used. The number of used columns is counted down from the number of references in the table, and columns that are referenced once or more in the table are treated as unevaluated columns and as data items in the intermediate table A'. A JOIN processing method using a biased join method according to claim 1.