JPS62236050A - Join processing system - Google Patents

Abstract

PURPOSE:To greatly shorten join processing time by dividing a join processing into plural CPU for parallel execution. CONSTITUTION:The 1st table for execution of joins is decentralized to local memories 7-10 and the 2nd table for joins is superposed on those memories 7-10 respectively. Here processors 3-6 perform the 1:1 comparison between the partial records of the 1st and 2nd tables and store successively the pairs of records satisfying the connecting conditions into the prescribed areas of a common memory device 1. Thus a low table undergone the join processing is finally produced on the device 1. As a result, the joint processing is possible in a short period without carrying out any sorting process in case one of both tables is especially large or small.

Description

DETAILED DESCRIPTION OF THE INVENTION [Field of Industrial Application] The present invention relates to the field of relational databases in the field of information processing, and particularly to a processing method for join operations therein.

[Conventional technology]

First, a relational database and its join operation will be explained using FIGS. 6 to 8.

FIG. 6 shows the first table for performing the join process, and is a list (parts list) showing the correspondence between parts purchased by a certain company and the manufacturer codes of those parts.

FIG. 7 shows a second table for performing the join process, which is a list (manufacturer table) showing the manufacturer names corresponding to the manufacturer codes and the addresses of the mechanisms.

1 shown in these Figures 6 and @7. The second table is a relational database (hereinafter simply referred to as a database)
These tables are stored on a magnetic disk as one of the tables (relays), and when there is a request such as a database search, these tables are moved from the magnetic disk to the memory and processed.

Since these tables are not directly related to the present invention, the following discussion will proceed assuming that these tables are already in the memory.

In addition, in the parts table 20 in Fig. 6, the parts names in the vertical direction,
The manufacturer code will be referred to as an "item", and a horizontal line such as 1C101 type 03 will be referred to as a record.

A join operation in a relational database is to create a new table by linking two tables using a certain item. For example, the parts list in Figure 6 and the manufacturer table in Figure 7 are joined using their respective manufacturer code items as join items. This is a join operation, and the result is the join result table shown in FIG.

For example, in the parts list 20, the IC103 type has the manufacturer code °01.
', and search for this from manufacturer table 151.
and manufacturer code 01'' are company A Tokyo, and the result obtained by combining these becomes the first row of the join result table 21 in FIG.

Note that a join operation may join three or more tables, but if you break it down, it means joining two tables.

Next, the above join operation will be explained using the a-in processing device shown in FIG.

FIG. 9 shows the configuration of a conventional digital processing device. In the figure, 22 is a processor (hereinafter referred to as CPU) and 23 is a common storage device (hereinafter referred to as memory).

Here, when creating the join result table shown in Figure 8 by performing a join operation on the parts list shown in Figure 6 and the manufacturer table shown in Figure 7, first, the parts list and manufacturer table are entered by the manufacturer code that is the joined item. Sort. In this example, the manufacturer list is already sorted by manufacturer code, so only the parts list is sorted and the sorted parts list 2 shown in Figure 10 is used.
Make 4. Once the sorted parts table 24 is created, by looking at it and the manufacturer table 15 in order from the top, a join operation can be easily performed and the join result table 21 shown in FIG. 8 can be obtained. The most time-consuming process in the above-mentioned stein processing is the sorting process, which requires a large amount of time when the number of records in the parts list 20 is large.

[Problem that the invention seeks to solve]

As described above, the conventional gigoin processing method has the problem that the sorting process takes a very long time when the number of records in the parts list and manufacturer's list is about several hundred.

This invention was made in order to solve the above-mentioned problems, and it is an object of the present invention to provide a method that can perform stain processing in a short time without performing sort processing, especially when one table is large and the other table is small. The purpose is

[Means for solving problems]

Therefore, in the present invention, the data of the first table to be joined is distributed and arranged in each of the local memories, the data of the second table to be joined is redundantly arranged in each of the local memories, and the processor compares the partial records of the first table and the records of the second table on a one-to-one basis, and sequentially stores the pairs that meet the join condition in a predetermined area of the common storage device. The system is characterized in that a new table is created on the common storage device after the sine processing is performed.

[Effect]

In this invention, the first table to be joined is distributed and placed in each local memory, and the second table to be joined is redundantly placed in each local memory.

Here, the processor compares the partial records of the first table and the records of the second table on a 1:1 basis, and sequentially stores the bare records for which the join condition has been met in a predetermined area of the common storage device. Finally, a new processed table is created on the common storage.

〔Example〕

First, the stain processing to which the present invention is applied will be explained.

In the stein processing, when dealing with a large table, for example, a table consisting of records, it takes time to sort the table. However, in actual Jijiin processing, there are quite a few cases where one table is large and the other table is small (several tens of records), and in this case, sorting is not performed. , it is faster to compare all the records in the manufacturer table with all the records in the parts list than to sort the parts list.

The present invention utilizes this idea.

Next, an embodiment of the present invention will be described based on the above explanation with reference to the drawings.

FIG. 1 is a block diagram showing a join processing method according to an embodiment of the present invention. In FIG. 1, ■ is a common storage device (hereinafter referred to as common memory), 2 is a common bus, and 3 to 6 are 1st CPU, 20th PU, and 3rd CPU, respectively.
u, the fourth CPU. Each CPU3-6 is an independent processor. 7 to 10 are the 10th Cal Memory, the 20th Cal Memory, the 30th Cal Memory, respectively.
The 40th memory is a memory outside the CPU, and is a local memory of each of the CPUs 3 to 6. Each CPU3~6 has its own local memory7~
10 or the common memory is determined by the memory address range. each cpu
The programs 3 to 6 run are usually in local memory.

When execution of the input process is started, first the sizes of the two tables to be input are checked. When the number of records in one table is large and the number of records in the other table is small, the join processing method of the present invention is activated. The join processing method is shown below.

Figure 2 shows the parts list 20 in Figure 6 divided into approximately four equal records, each with a partial parts list 1.
1.12.13.14. These partial bill of materials are
The data is loaded into each of the local memories 7 to 10 from an external input/output device such as a magnetic disk device, which is not shown since it is not directly related to the present invention. The entire manufacturer table 15 in FIG. 7 is loaded redundantly into each of the local memories 7 to 10. When the partial table of the parts list, which is a large table, and the entire manufacturer table, which is a small table, are loaded into each of the local memories 7 to IO, each of the CPUs 3 to 6 starts processing.

FIG. 4 shows the sirloin treatment of the first CPU3. The first CPU 3 performs an injection process on the partial parts list 11 and manufacturer table 15 on the tenth memory 7 using the program loaded in the tenth memory 7 . That is, one record of the partial parts list 11 is taken out, and this maker code item is sequentially compared with the maker code items of the records of the maker table 15. If they match, the two records are linked and stored in a predetermined location in the common memory 1. For example, the manufacturer code of the first row record of the partial parts table 11 is "03", and when this is compared with the record of the manufacturer table 15, it matches the third record of the manufacturer table.

So, by linking these two records, IC101 type C
Create a record called Company Osaka Prefecture and enter it into the partial join result table 16 on the common memory 1. By performing the above operations on all records in the partial parts list 11,
A partial join result table 16 shown in FIG. 4 is obtained. The above processing is performed in the second and third steps. The same process is performed in the fourth CPU 4.5.6.

FIG. 5 shows that each CPU performs a-in processing in parallel and stores partial join result tables 16.17.about.18.19 in the common memory 1. When all CPUs finish the comparison operation, the join process ends. That is, the result obtained is a collection of four partial result tables on the common memory 1.

Now, the processing times of the conventional method and the method according to the present invention will be compared. In the conventional method, the processing time is determined mostly by the time taken to sort a large table.

It is known that when a quick sort algorithm is used as a sorting algorithm, the average number of comparisons is 1.39NlolλN, where N is the number of records.

Considering this as time, and using the same time unit, in the present invention, each CPU compares the code in the partial parts list with the code in the manufacturer table, and if they match, stores the partial result table in the common memory. The average time is estimated to be approximately "0.8".

The time for the join processing of the present invention is approximately equal to the time for the partial join processing performed by each CPU, which is 1,000·n, where n is the number of records in the small table (manufacturer table) and N is the number of records in the large table (parts list).・It becomes 0.8. N=2=163
84. If n=20, 1.39N islands λN=1.39xNx14=49.5N"
1--n.0.8 = 4 N, and it can be seen that the join processing method of the present invention is about 5 times faster.

It is clear that the present invention is applicable not only to the case of four CPUs as shown in the embodiment, but also to the case of a plurality of CPUs in general. In the above embodiment, the partial join result table is stored in the common memory, but it may be stored in the common disk, for example. Also, if the partial parts table is too large to fit in the local memory, or if each partial join result table is too large to fit in the common memory,
The partial parts list is read into the local memory several times, the same processing as shown in the example is performed each time, and the resulting partial join result table is written to the magnetic disk each time. Just try to repeat it.

Also, in this embodiment, a plurality of CPUs and a common memory are connected by a common bus, but it is not necessary to connect them by a bus.
For example, each CPU may be coupled to a common storage device by a channel. Furthermore, in the embodiment, an example was shown in which a record of a large table is joined to one of the records of a small table, but it is clear that the present invention can also be applied to cases where a record of a large table is joined to a plurality of records of a small table.

Furthermore, although the embodiments have been described in connection with a join operation for a relay differential database, it is clear that the present invention can be applied to cases in which similar processing is performed in other fields.

〔Effect of the invention〕

As described above, according to the present invention, the data of the first table to be joined is distributed and arranged in each of the local memories,
The data of the second table to be joined is placed redundantly in each local memory, and the processor divides the partial records of the first table and the records of the second table one pair, respectively.
By sequentially storing the bare data for which the join condition has been met in a predetermined area of the common storage device, a new joined table is finally created on the common storage device, so each CPU By sharing the zicoin processing and executing it in parallel, this has the excellent effect of significantly shortening the processing time for the join processing.

[Brief explanation of drawings]

Fig. 1 is a block diagram showing a join processing method that is an embodiment of the present invention, Fig. 2 is a diagram showing a partial parts list, Fig. 3 is a diagram showing the contents of local memory, and Fig. 4 is a diagram showing the first CPU. Figure 5 shows the join process for each CPU, Figure 6 shows the parts list, Figure 7 shows the manufacturer list, and Figure 8 shows the join result table. 9 are diagrams showing a conventional join processing method, and FIG. 10 is a diagram showing a sorted parts list. 1 is a common storage device, 3 is a first CPU, 4 is a second CPU,
5 is the 3rd CPU, 5 is the 4th CPU, 7 is the 10th CPU
8 is the 20th Cal memory, 9 is the 30th Cal memory, IO is the 40th Cal memory, 11 to 14 are partial parts lists, 15 is a manufacturer table, 16 to 19 are partial join result tables, and 20 is a parts list. In the figures, the same reference numerals indicate the same or equivalent parts. Agent Masu Oiwa (M and 2 others) No. 70 Figure 9 Figure 8 Creation ○ Drawing procedure amendment (spontaneous, Showa year, month, day 2, name of the invention, Jitwin processing method 3, representative of the person making the amendment, Moriya Shiki) 4. Agent i, column for detailed explanation of the invention to be amended. 3. Contents of amendment + 11 Amend item 5 to read "common storage device" in line 6 of page 4 of the specification as "storage device."that's all

Claims (1)

[Claims] A join processing method that has a plurality of processors each having a local memory and a common storage device that can be accessed by the plurality of processors, and creates a new table by linking two tables using a certain item. The data of the first table to be joined is distributed and placed in each of the local memories, the data of the second table to be joined is redundantly placed in each of the local memories, and the processor By comparing the partial records of the first table and the records of the second table on a one-to-one basis, and sequentially storing the pairs that meet the join conditions in a predetermined area of the common storage device, the partial records of the second table are finally stored in the common storage device. A join processing method characterized by creating a new table that is joined above.