JPS63223827A - Data processing system - Google Patents

Abstract

PURPOSE:To realize the reduction of both the memory capacity and the calculation value in the group-by arithmetic of a relational data base by retrieving all items of a designated table as well as all items of a designated index to segment the value of a grouping objective column and performing a grouping action designated by a user. CONSTITUTION:A disk device 15 sorts the gathering of rows of the same group- by-column value as a single group in a table 30 consisting of plural rows. Then a designated function arithmetic operation is carried out in a column 32 to which the gathering of rows sorted at every group. In this case, the arithmetic operations are carried out in terms of the maximum and minimum numbers of rows within a group. Thus a group-by-column domain is carried out at high speed in a process other than the entire retrieving job of a table to undergo the arithmetic by means of a data base machine 13.

Description

DETAILED DESCRIPTION OF THE INVENTION [Field of Industrial Application] The present invention relates to a data processing method in a relational database. This paper relates to a data processing method that applies the functions provided.

[Prior art]

A relational database is a collection of data having a two-dimensional matrix data structure called a table 30, as shown in FIG. This table 3o corresponds to a normal file, and contains rows 31 corresponding to records in the normal file. The table 30 also includes columns 32 that correspond to fields of a normal file.

A relational database having such a structure is used, for example, as a bank account database in which the table 30 is a collection of each individual's accounts.

In this case, the information for each individual's account is stored in one row 31.
In one row 31 called each individual's account, the name of the branch where the account is located.

Column information about the savings account balance of that account is stored.

Furthermore, when performing group pie processing on such a relational database in order to obtain the total savings account balance, average balance, etc. of each branch, it is possible to , it is necessary to group each row 31.

In this case, in the relational database, in order to group column 32, that is, the group-by column specified for grouping, it is necessary to obtain a set of column values of the group-by column of all rows, that is, a domain.

Various methods for performing such processing have been proposed; for example, A Parallel Piblind Relay Processor, Wong Kiim et al., N.C.M., T. Audinis, Volume 9.
Number 2, June 1984, pp. 214-242 (WonKi, m, at al: A Paral,
lcL Pipelined Relationa
l Qup, ry Processor, ACtv
5 TODS.

Vol.9. Nn2. Juno 1984ypp214
242), the contents of the table are read into the main memory, and each row is sorted by the value of the group by column to perform grouping. In addition, a method has also been proposed in which hashing is used to reduce the sorting process. [Problems to be solved by the invention] In the prior art, in the sorting process when performing group pie processing, This places a heavy burden on the central processing unit (hereinafter abbreviated as CPU), and requires a large capacity in the main storage device to store data to be sorted.

SUMMARY OF THE INVENTION An object of the present invention is to provide a data processing method that improves these problems and enables a reduction in storage capacity and calculation amount when performing group pie processing in a relational database.

[Means to solve the problem]

In order to achieve the above object, the data processing method of the present invention includes:
Main memory that implants a data transfer bus.

A set t of rows having the same value in a specific column is defined as one group in a table comprising a channel, a control unit, a disk unit τ, and a central processing unit, and is stored in the disk unit and is composed of a plurality of rows. , in a data processing system that classifies the rows of one table into one or more groups and performs data processing by executing a function specified for each column for each group. A data processing means (database machine) is provided within the storage area or between arbitrary storage areas, and the first processing is to perform the group byte processing used when grouping the rows of the table above.・Get the cent, or domain, of the column value of the column, and then as a second process,
The domain is sorted, and the third step is to use the database machine (hereinafter referred to as DB machine) to read each row of the table and extract the column value of the group-by column. , perform a binary search between So1 in the second process and the information from which duplicated values have been removed after processing, determine which group each row belongs to, and synchronize with the data transfer of the table. It is characterized by executing the above specified functional operation.

Regarding the first process above, search the entire table that is the target of the above function operation, search all pairs of indexes attached to the column values of that table, and search the above table in tables other than the table that is the target of the function operation. Which method should be used: a full search for tables that have the same domain as the group-by-column domain, or a full search for indexes attached to columns of tables that have the same domain? , or the above group/
It is characterized by receiving a processing request for a bi-column domain from the issuing party.

Further, the first process is characterized in that if a group-by-column domain is stored in advance in the data processing system, the stored domain is extracted and used and omitted.

Furthermore, the above 1. As for the second process, if the sorting results of the group-by-column domain are stored in advance in the data processing system, the sorting results can be omitted by transferring them to the DB machine. It has characteristics.

[Operation Go] In the present invention, first, an all-item search of a specified table or an all-item search of a specified index is performed, the values of the column to be grouped are extracted, and the values of the grouping specified by the user are searched. Accept gatherings.

Normally, a set of column values for a group-by-column is recognized when the target table is read. However, for example, when aggregating the balance of each individual's savings account for each branch of a bank, tables containing the branch name, location, number of employees, etc. of each branch are often created in advance. By performing a full search of the table that stores all branch information, before reading out the table that stores the balance of each individual's savings account and the name of the branch where the account is located, It is also possible to obtain a list of each branch of that bank. Further, regarding this entire search, since the number of bank branches is on the order of 100 and the number of depositors is on the order of too many, the overhead is only about 1%.

Note that this process is omitted if the group-by-column domain is stored in advance in the data processing system, and if the group-by-column sorting results are similarly stored.

Next, the values to be grouped thus obtained are sorted. Note that the process of is omitted if the group-by-column domain sort results are stored in advance in the data processing system.

Furthermore, in the DB machine, the table to be searched is searched, the value of the group by column of each row is cut out, and a binary search is performed with the value to be sorted and grooving is performed to determine which group the row belongs to. determines whether to yield to the specified function and executes the specified function function.

This DB machine reduces the CPU load.

Main memory capacity can be significantly reduced. In addition, the processing by this DI3 machine accounts for most of the processing amount, and can be executed in synchronization with data transfer, so it can be By installing the DB machine at any position on the data transfer path between the main storage and the main storage, processing can be completed almost simultaneously with the data transfer time.

〔Example〕

An embodiment of the present invention will be described below with reference to the drawings.

FIG. 1 is a block diagram of a data processing system according to an embodiment of the present invention, FIG. 2 is a block diagram of a DB machine according to an embodiment of the present invention, and FIG. 3 is a block diagram of a data processing system according to an embodiment of the present invention. Explanatory diagram showing the software configuration of
Figure 6 is an explanatory diagram of a table in an embodiment of the present invention.
FIG. 7 is an explanatory diagram of the common memory in one embodiment of the present invention, FIG. 7 is an explanatory diagram of function information in one embodiment of the present invention, and FIG. 5 is an explanatory diagram of the sort information storage memory in one embodiment of the present invention. , FIG. 9 is an explanatory diagram of the interface information section in one embodiment of the present invention, and FIG. 8 is an explanatory diagram of the dedicated memory B in one embodiment of the present invention.

The data processing system of this embodiment is as shown in FIG.
PU10. Main storage device 11. Channel 12, DB machine 13. The control device [14] and the disk device 15 are turned on.

In addition, the DB machine 13 has a channel and
Interface Professor (hereinafter abbreviated as CIP) 5
1. Disk Interface Processor (DI)
(abbreviated as P)50. Database processor (hereinafter referred to as DB)
(abbreviated as P)54. Batched Search Engine 55. Sort information storage memory 56. Common memory 57. Dedicated memory A52° Dedicated memory B53. and a dedicated memory C58.

The DIP50 includes a dedicated memory A52 and controls IF! It realizes an interface with the controller 14 and transfers data between the control device 14 and the controller 14 .

CrP51 is equipped with a dedicated memory B53 and has channel 12.
It implements an interface with the channel 12, performs data transfer with the channel 12, and also executes part of the DB calculation.

The DBP 54 includes a dedicated memory 058 and executes DB operations.

The batched search engine 55 is hardware dedicated to binary search processing, and the sort information storage memory 56 is
Stores sorted information during binary search processing.

The common memory 57 includes CIP51. DrP50° and D
Accessed from the BP 54, as shown in FIG. 6, the search range 60. Table index (hereinafter abbreviated as table ID) 61. Group by Column Index C (abbreviated as Group by Column iD) 62. Number of sort information: 63. Function information 64. Input buffers 65 to 67, an interface information section 68, etc. are stored therein.

The search range 60 includes the identifier of the disk device to be searched among the plurality of disk devices 15 and the disk device 1.
It shows the range to be searched among the data stored in 5.

Also, table tD61. and group by column 1D62. are the search targets for table 3, respectively.
0 and the identifier of column 32 that is the target of grooving.

The number of sort information 63 indicates the number of sort information codes 80 to 8n stored in the sort information storage memory 56 in the sorted order, as shown in FIG.

5 Regarding the grouping of each row 31 that corresponds to codes 80 to 8n, for example, refer to Table 3 to be searched.
If the set of rows 31 whose group by column value of 0 is equal to code 80 belongs to the first group, and the set of rows 31 whose value is equal to code 81 belongs to the second group,
The DBP 54 uses the batched search engine 55 to check which group each row 31 included in one block belongs to, and stores the search results in the interface information section 68.

As shown in FIG. 7, the function information 64 includes the number of function columns 70.
Function column index (hereinafter abbreviated as function column iD)
7I and specified function 72 are stored, and the number of function columns is 70.
indicates the number of columns 32 to be subjected to the functional calculation, and function column tD71 is an identifier of the column 32 to be subjected to the functional calculation.

Further, the specified function 72 is a function specified for each column 32 on which the operation is performed, and for example, the specified function 72 is a function that is specified for each column 32 on which the operation is performed. The function column fD7L and the specified function 72 indicate the medium, maximum column value (maximum value), minimum column value (minimum value), total value, etc.
The number of pairs equal to the number of function columns 70 are manipulated.

The input buffers 65 to 67 receive one block of data read from the disk device 15, that is, one block of data read from the disk device 15.
The interface information section 68 is a transfer unit on the DIP 5 and stores data including a plurality of rows 31.
50. DBP54. Stores interface information between CIPs 51.

Regarding the processing of data for each block, first, the DIP 50 reads out the data from the disk device 15.
The data for each block is input to the input buffer 765. Input buffer 66. and are sequentially stored in the input buffer 67. In this way, when data is stored in the input buffer 66, the DBP 54 processes the data stored in the input buffer 65, and similarly, when data is stored in the input buffer 67, the DBP 54 processes the data stored in the input buffer 66. Process the data. The CIP 51 processes the data stored in the input buffer 65 based on the calculation result of the processing by the DBP 54 .

In this way, DIP50. of DB machine I3. DBP54
．． The CIP 51 can process data in units of blocks in a pipeline manner in synchronization with data transfer processing.

As shown in FIG. 9, the interface information section 68 includes a search start plug 90. Input completion plug 91. Grouping completion flag 92. Block inner entrance - number 93. and group information 94 are stored.

The search start flag 90 is turned ON when the CIP 51 requests the DIP 50 to start a search.

The input completion flag 91 is turned ON when the input of a new block to the input buffers 65 to 67 is completed, so that the DIP 50 notifies the DBP 54 of the completion of input.

The grouping completion flag 92 is set to 1 by the DBP 54.
Turns on every time a block of row 31 is grouped.
becomes.

Block inner entrance - number 93. and group information 94 is DB
The block internal number 93 indicates the number of rows 31 included in the block, and the group information 94 indicates which group each row 31 belongs to. Which code matched or the sort information storage memory 5
Indicates that the code does not match the code in 6. Therefore,
The group information 94 is the row 3 included in the block.
Only the number 1 is manipulated.

In this way, when the grouping of each row 31 is completed, C
A predetermined function process is performed on the column 32 designated by IP51, the result of the calculation is stored in the dedicated memory 53, and when the search process for all search ranges 60 is completed, the result of the calculation is sent to the channel 12. Send to.

The dedicated memory B53, as shown in FIG. 8, includes an area 1000 for storing functional calculation results.

The number of areas 1000 is equal to the product of the number of groupings, that is, the number of sort information, 63, and the number of columns 32 to be operated on, that is, the number of function columns, 70.

For example, if a function that calculates the total value is specified, CIP5
1 searches for area 1000 to store the calculation result, and adds the value of column 32, which is the calculation target, to the value of area 1000 in row 3°1 currently being processed.

Note that in this embodiment, the DB machine 13 uses the channel 12
Although it is installed independently between the main storage device 11 and the control device 14, it can also be installed in any device between the main storage device 11 and the disk device 15.

In the data processing system of this embodiment, as shown in FIG.
CPU10. And in the main storage device 11, an application program (hereinafter abbreviated as AP) 21. Relational database management system (hereinafter referred to as RDBMS) 20 and operating system
It includes software such as a system (hereinafter abbreviated as O8) 22 and the like.

The RDBMS 20 manages the RDB, and the AP 21
A DB processing request is issued to the RDBMS 20. Further, 0322 manages hardware, data processing systems, and the like.

When the AP 21 issues a DB operation request to the RDBMS 20, the RDBMS 20 receives the DB operation request from the AP 21.
The B calculation request is analyzed and divided into processing that should be executed by the DB machine 13 and processing that should be executed by the RDBMS 20. R
The DBMS 20 instructs the DB machine 13 to perform processing through 0822, and receives the processing results. In these processes, the data handled by the RDBMS 20 is a set of matrix-like tables 30 stored in the disk device 15, as shown in FIG. 4, and the table 30 is composed of rows 31 and columns 32. .

Note that if the RDBMS 20 has fewer management functions such as hardware devices, the OS22 is not necessary.

Furthermore, it is also possible to install the AP 21 in a separate device.

However, in this case, CPU10. Or main storage device 11
It is necessary to provide a communication management program with the AP 21 provided in the separate device on the side.

FIG. 10 is an explanatory diagram of group-by-column domain creation in an embodiment of the present invention, and FIG. 14 is a diagram illustrating the processing side of group pi operation in an embodiment of the present invention.
1 is an operational flowchart of C12 in an embodiment of the present invention, FIG. 12 is an operational flowchart of DIP in an embodiment of the present invention, FIG. 13 is an operational brooch yard of DBP in an embodiment of the present invention, and 15th The figure is a processing flowchart of group pi calculation in one embodiment of the present invention.

The group pie processing executed in the data processing system of this embodiment is performed using nine specified columns in each row 31 of the table 30, as shown in FIG.

That is, a set of rows 31 having the same group-by-column values is classified as one group, and a specified functional operation is performed between columns 32 to which the set of rows 31 classified into groups belongs.

This functional operation is performed on the number of rows within the group, such as maximum value, minimum value, etc. For example, as shown in FIG.
This is a process of summing the values of 3 to obtain a table 144 representing the total savings account balance for each branch. In addition, table 1
40 includes a row indicating personal account information, and in addition to a savings account balance column 143, an account number column 141, a branch column 142, and the like.

In the process of group-by-column processing in the data processing system of the present invention, as shown in FIG.
PU10. Alternatively, it is determined by the processing of the DB machine 13 (1501).

The method for determining this domain is shown in Figures 10, 2 and 14.
As shown in the figure, when calculating the domain 43 of the branch column I42 in order to calculate the total value for each branch in the savings account balance column 143, there are two methods: (1) searching all the items in the table 140; (2) searching the table 140 for all items; How to search all index 40 attached to branch column 142, (3) Table 1
Method for performing all search for table 41 other than table 40, (4)
A method for searching all the index 42 attached to the branch column of table 41, and (5)
There is a way to directly pass it to DBMS 20, (1)
Processing efficiency improves in the order from (5) to (5). Therefore, in this example, from (5) to (1) according to the target table.
) using the possible methods in priority order. Note that the indexes used in these methods usually store the value of the target column 32 and the identifier of the row 30 that has that value in a B-tre.
It is an e version.

Further, this process can be executed using any of these methods, and in particular, it can be executed more efficiently if it is other than the all-item search of the table 140 as in (1). .

Note that within the data processing system, for example, RDB
If the MS 20 holds information on the domain 43 in the branch column 142 in advance, this process is not necessary.

Thus, the group-by-column domain.

That is, once the domain 43 of the branch column 142 is obtained, the domain 43 is then sorted by CPULO or the DB machine 13 (1502).

Note that if the results of sorting the group-by-column domain are stored in advance in the data processing system, these processes are not necessary and the function calculation by the DB machine can be started immediately.

Finally, the DB machine 13 reads the table stored in the disk device 15 in synchronization with the data transfer of CPtJIO, extracts the value of the group by column of each row, and exchanges it with the sorted domain 43. Perform a binary search and group each row. After this, further calculations are performed between the values in the middle of the calculation of the corresponding group (1
503).

In the process of processing in this DB machine 13, CIP5
1. DIP50,8 and DBP54 (see Figure 2)
process in parallel and continuously.

If, CIP51 is channel 12 as shown in FIG.
When a DB processing request is received from the CPU 10, the result after sort processing is sent to the CPU 10 with the number of sort information 63 degrees and the code 80~
8n, and further includes a search range 60.8n to be searched. Table tD61゜Group by Column 1D
52. Receive function information 64 etc. from channel 12,
Each is stored in a predetermined memory. (1100).

Next, the DTP 50 is instructed to turn on the search start flag 9° and start a disk search request (
1101).

After issuing a search start request to the DIP 50 in this manner, the DBP 54 waits until the grouping process for one block of rows is completed, that is, until the grouping completion flag 92 is turned ON (1102).

When the grouping completion flag 92 is turned ON, the grouping completion flag 92 is turned OFF (1103), and the number of rows in the block is 93. Based on the search results of the DBP 54 such as the group information 94 and the function information 64, a specified operation is performed and the result is stored in the corresponding area 1 of the dedicated memory B53.
Store in 000 (1.104).

In this way, when the processing for all the rows for one block is completed, it is confirmed that the processing for all the search ranges is completed (1105), and when the completion is confirmed, the information stored in all the areas 1000 is transferred to the channel. 12 (1106).

When the DIP 50 receives a search start request from the CIP 51 (1101), the DIP 50 sets the search start flag 90 as shown in FIG.
Make sure it is ON (1200) and turn off the search start flag 90.
F, and issues a request to the control device 14 to locate the disk device 15 according to the information obtained from the search range 60 (1201), and waits for the completion of the location process (1201).
2).

When the positioning process is completed, the data transfer process for one block is performed (1, 203), and when this process is completed, the input completion plug 91 is turned on and the DBP 54 is notified of the completion of the data transfer process. (1204).

Furthermore, please confirm that the search for all search ranges has been completed (1205), and if it has not been completed, continue the transfer process (1205).
1203), and if completed, performs the next block transfer process (1200).

When the DIP 50 transfers data for one block (1203), the DBP 54 confirms that the input completion flag 91 is ON as shown in FIG. 13 (1300).
, turn off the input completion flag 91 (1301)
.

Next, the group-by-column values of each row 31 within one input block are extracted, and the batched search engine 55 stores these values and the sort information storage memory 56.
A binary search is performed between the information in the row 31 and the group to which each row 31 belongs, or whether there is a group to which the row 31 belongs is checked. When all the rows 31 have been grouped in this manner, the number of rows 31 included in the block inner opening-31 is stored, and group information 94 equal to that number is created (1303).

Furthermore, the grouping completion flag 91 is turned on, and the CI
P51 is notified that grouping within one block has been completed (1304), and the next block is grouped (1300).

〔Effect of the invention〕

According to the present invention, in a group-by-column operation in a relational database, especially when the domain of a group-by-column is determined by a method other than a full search of the table to be operated on, the specified operation can be performed using a small amount of memory. can be executed at high speed. Furthermore, if the group-by-column domain and its sorting results are stored in advance in the data processing system, only the processing by the DB machine is sufficient.

Moreover, this DB machine is located on the data transfer path from the disk device to the main storage device and executes calculations in synchronization with data transfer, so it is possible to perform processing only in the table transfer time.

[Brief explanation of drawings]

Figure 1 is a block diagram of a data processing system in an embodiment of the present invention, Figure 2 is a block diagram of a DB machine in an embodiment of the present invention, and Figure 3 is a block diagram of a data processing system in an embodiment of the present invention. 4 is an explanatory diagram of a table in an embodiment of the present invention; FIG. 5 is an explanatory diagram of a sort information storage memory in an embodiment of the present invention; 6th Wi is an explanatory diagram of a sort information storage memory in an embodiment of the present invention; An explanatory diagram of a common memory. FIG. 7 is an explanatory diagram of function information in an embodiment of the present invention;
FIG. 8 is an explanatory diagram of the dedicated memory B in one embodiment of the present invention, FIG. 9 is an explanatory diagram of the interface information section in one embodiment of the present invention, and FIG. 10 is a diagram of the group bypass in one embodiment of the present invention.・Explanatory diagram of column domain creation, 1st
FIG. 1 is an operation flowchart of CIP in an embodiment of the present invention. FIG. 12 is an operation flowchart of DIP in an embodiment of the present invention, and FIG. 13 is a DB operation flowchart in an embodiment of the present invention.
FIG. 14 is a processing side diagram of the group pi operation in an embodiment of the present invention, and FIG. 15 is a processing flow chart of the group pi operation in an embodiment of the present invention. 10: Central processing unit (CPU),
11: Main storage device, 12: Channel, 13: Database machine (DB machine), 14: Control device, 15:
Disk device, 20: Relational database
Management System (RDBMS). 21; Application program (AP). 22: Operating system (O8), 30: Table, 31: Row, 32: Column, 41°140: Table, 40, 42: Index, 43: Branch column domain, 50: Disk interface processor (DIP) , 51: channel interface processor (arp). 52: Dedicated memory A, 53: Dedicated memory B, 54: Database processor (DBP), 55: Batched search engine, 56: Sort information storage memory, 57:
Common memory, 58: Dedicated memory C260: Search range, 6
1: Table index (table ID), 52 Nigeloop pi column index (group by
Column iD). 63: Number of sort information, 64: Function information, 65 to 67: Input buffer, 68: Interface information section. 70: Number of function columns, 71: Function column index (
Function column! D), 72: Specified function, 80 to 8n: Code, 90: Search start flag, 91: Input completion flag, 9
2Nigeroop completion flag. 93 Number of rows in Ni block, 94 Nigel loop information. 140.144: Table, 141: Account number column,
142: Branch column, 143: Ordinary account balance column, 1
000: Area of dedicated memory B. The first part? Kuni No. 1, 5' Decoy Chrysanthemum / Figure 1 No. 4

Claims (1)

[Claims] 1. A main memory device and a channel that constitute a data transfer bus;
Equipped with a control unit, a disk unit, and a central processing unit,
Among tables that are stored in the disk device and are composed of multiple rows, a set of rows with the same value in a specific column is classified as one group, and the rows of one table are classified into one or more groups, and each In a data processing system that processes data by executing functional operations specified in each column for each group, means for processing data is provided within or between any devices on the data transfer bus. , First, as a first process, the column used to group the rows of the above table, that is, the group column.
After obtaining the bi-column domain, the second process is to sort the domain, and the third process is to use the data processing means to read each row of the table and sort the domain. Extract the column value of the group-by column, perform a binary search with the information from which duplicate values have been removed after the sorting process, determine which group each row belongs to, and transfer the data of the table. A data processing method characterized in that the specified functional operation is executed for each group in synchronization with the data processing method. 2. The data processing method according to claim 1, wherein the first process performs an entire search of the table to be subjected to the functional operation to obtain the domain. 3. The first process is characterized in that the domain of the group-by-column is obtained by performing a complete search of the index attached to the column value of the table that is the target of the functional operation. The data processing method described in Section 1. 4. The above first process performs an overall search for tables that have the same domain as the domain of the above group-by column among tables other than the table that is the target of the above-mentioned function operation, and - The data processing method according to claim 1, characterized in that a domain of a column is obtained. 5. The above first process targets a table that has the same domain as the group-by-column domain among tables other than the table that is the target of the above function operation, and calculates the index attached to the column. 2. The data processing method according to claim 1, wherein a domain of the group-by-column is obtained by performing an overall search. 6. The first process is characterized in that the group-by-column domain is received from the issuer who issued the group-by-column domain processing request. data processing method. 7. The above data processing system is capable of handling the above group by
2. The data processing method according to claim 1, wherein the domain of the column is held in the system in advance. 8. The above data processing system is capable of handling the above group by
2. The data processing method according to claim 1, wherein a result of sorting the domain of the column is held in the system in advance and transferred to the data processing means.