JP3584630B2 - Classification and aggregation processing method in database processing system - Google Patents

Description

[0001]
TECHNICAL FIELD OF THE INVENTION
The present invention relates to a parallel database processing system that performs database processing in cooperation with one or more nodes, and more particularly to a parallel database processing system that speeds up database classification and aggregation processing.
[0002]
[Prior art]
The logical structure of the database is in a table format (20) as shown in FIG. The horizontal direction of this table is called a record (21), and the vertical direction is called a column (22). The same column of each record stores data in the same format.
[0003]
Classification and aggregation processing means that records in a table are classified into several groups based on the values of one or more columns specified in advance, and one or more predetermined records are set for each set of records belonging to each group. This is the process of calculating the statistical value such as the total value and the average value of the values of the columns to be totaled.
[0004]
FIG. 3 shows an example of the classification and aggregation process. In this example, the relational database holds information on sales performance in the form of a table. Records in the table have column values for the product code, sales location, date of sale, and price (30). In this example, records having the same value in the column values of the product code, the sales place, and the sale date are set as one group (31), and the total value of the record prices is calculated for each group (31). 32). The result of the tallying process is obtained again in the form of a table (33).
[0005]
As a conventional classification and aggregation processing method, the first method is disclosed in the literature "Parallel Sorting Algorithms (by Selim G. Akl, published by Academic Press, Inc.)", page 48 to page 49 of Chapter 3. As a method and a second method, a simple hashing method disclosed in Chapter 9, pages 262 to 264 of the document "Relational Data Management (M. Papazoglow, written by W. Valder, published by PRENTICE HALL)" can be cited.
[0006]
First, a flowchart of a merge sort method as a first conventional method is shown in FIG. In the merge sort method, records constituting a database table are read in blocks from a secondary storage device (40), sorted on main storage (41), and then written back to the secondary storage device (42). . After this operation is repeated for all record blocks (43), merge sorting is performed between the record lists sorted for each block using the secondary storage device as a work area (44). After the above operation, the sorted record rows are sequentially read from the secondary storage device (45), and the records are totaled (46). However, in the merge sort method, a large amount of I / O occurs during the external sort processing of records, and parallel execution cannot be performed between the sort processing (corresponding to the classification processing) and the aggregation processing. Good classification and tallying cannot be performed.
[0007]
FIG. 5 shows a flowchart of a simple hash method as a second conventional method. In the simple hash method, when reading out a record stored in the secondary storage device (50), records are grouped using a hash function (51), and at the same time, the counting result in the counting result storage area is updated. (52) The classification process and the tabulation process are performed in parallel, and the classification and tabulation process is performed without creating a temporary file for grouping. However, in the simple hash method, since the tally result storage area is randomly accessed, if the tally result does not fit in the reserved area on the main memory (53), random I / O occurs to the secondary storage device. (54), the execution efficiency of the classification and aggregation process is reduced.
[0008]
[Problems to be solved by the invention]
In a conventional relational database system, merge sorting is performed via a secondary storage device when grouping records in a database in a classification and aggregation process. In this method, a large amount of I / O access is required for the merge sort process, and the classification process and the counting process cannot be performed in parallel, so that the efficiency of the classification and counting process has been greatly reduced.
[0009]
Alternatively, in a conventional relational database system, grouping using a hash function is performed at the time of record classification processing in group totaling. In this method, random access occurs to the tally result storage area during the tally processing, and if the tally result does not fit in the reserved area on the main memory, a large amount of random I / O is sent to the secondary storage device. Issued, causing a reduction in the efficiency of classification and aggregation processing.
[0010]
An object of the present invention is to enable a classification process and a tallying process to be executed in parallel in a database tallying process.
[0011]
Another object of the present invention is to prevent a random I / O from being generated for a secondary storage device even when a totaling result does not fit in a reserved area on a main storage.
[0012]
[Means for Solving the Problems]
The classification and aggregation processing method of the parallel database processing system according to the present invention includes a network 3 that connects one or more input / output servers 2, one or more aggregation processing servers 4, and one or more query processing servers 5. A method for performing a classification and aggregation process in a parallel database processing system, wherein a partial table 7 obtained by dividing a single table composed of a large number of records is divided and held by the plurality of input / output servers.
By using a hash mechanism to determine the storage location of the aggregation results and executing the aggregation process on a record-by-record basis, it is possible to execute the record classification process and aggregation process in parallel, and if the aggregation results do not fit in the reserved area on the main memory, Saves the partial totaling result in the auxiliary secondary storage device 17, integrates the partial totaling result saved after the completion of the totaling process, and prevents the random I / O to the auxiliary secondary storage device to efficiently perform the classification and totalizing process. Is a method for executing.
[0013]
Specifically, an inquiry processing server that processes an inquiry processing request to the database, an input / output server that reads out records of the database, and an aggregation processing server that performs aggregation processing of records are provided, and one or more of the records are provided. A column is designated as a grouping column, a group identifier is made to correspond to the grouping column value, a plurality of records having the same group identifier value are classified into one group, and one or more records of the record are classified. A first database processing system that designates a column as an aggregation target column and performs an aggregation process for each record classified into each group, wherein the input / output server is configured to perform the aggregation process according to a value of a grouping column of each record. Means for transferring the record to a tallying processing server that performs tallying of the record, A total processing server for allocating a totaling result storage area on a main memory provided in the totaling processing server, a unit for generating a group identifier from a value of a grouping column of a record received from the input / output server, and a value of the group identifier. A grouping means for uniquely determining the storage location of the aggregation result corresponding to the group identifier, a counting means for updating the aggregation result stored in the storage location based on the value of the aggregation target column of the record, Means for transferring the tally result to the inquiry processing server, wherein the inquiry processing server is realized by having means for integrating the interim tally result received from the tally processing server.
[0014]
Further, in the first database processing system, when the tally result does not fit in the tally result storage area secured on the main memory of the tally processing server, the tally result in the tally is grouped according to the tally result. After sorting the values of the identifiers on the main memory, the data is saved as an intermediate tally result in an auxiliary secondary storage device provided in the tallying server, and when the tallying process is completed, the tally result in the main memory and the auxiliary secondary storage device By performing a merge process with the zero or more sorted intermediate tabulation results saved in the above, the process is performed by performing a tabulation process on the records distributed to the respective tabulation processing servers.
[0015]
Further, in the first database processing system, when the tally result does not fit in the tally result storage area secured on the main memory of the tally processing server, of the tally results in the middle of tally, the most recently referred tally result. After leaving a part of the tally result and sorting the remaining tally results on the main memory with respect to the grouping identifier value corresponding to the tally result, the result is saved as an intermediate tally result in the auxiliary secondary storage device provided in the tally processing server. At the time when the totaling process is completed, each of the totalizing processes is performed by performing a merge process between the totaling result on the main memory and the zero or more sorted intermediate totaling results saved in the auxiliary secondary storage device. This is achieved by performing a tallying process on records distributed to the server.
[0016]
Further, in the first database processing system, when the totaling result cannot be stored in the totaling result storage area secured on the main memory of the totalizing processing server, records that cannot be totaled are collected by the totaling processing server. At the time when the data is saved in the auxiliary secondary storage device provided and the aggregation process is completed, the merging process is performed between the aggregation result in the main storage and the aggregation result of the zero or more records saved in the auxiliary secondary storage device. This is achieved by performing the tallying process on the records distributed to the respective tallying servers.
[0017]
Further, in the first database processing system, when the totaling result cannot be stored in the totaling result storage area secured on the main memory of the totalizing processing server, records that cannot be totaled are collected by the totaling processing server. When the ratio of records that can be tallied in the secondary storage device provided in the storage device and that can be tallied on the main storage falls below a certain value, the tallied result of the tallied is grouped by the grouping identifier corresponding to the tallied result. After sorting on the main memory for the value of, it is saved as an intermediate tabulation result in the auxiliary secondary storage device provided in the tabulation processing server, and when the tabulation process is completed, the tabulation result in the main memory and the auxiliary secondary storage device are saved. Performing a merge process between the saved total result of the zero or more records and the zero or more sorted intermediate total results saved in the auxiliary secondary storage device; Thus, realized by performing the aggregation process for records distributed to each of the aggregation processing server.
[0018]
Next, an inquiry processing server for processing an inquiry processing request to the database, an input / output / aggregation server for reading and totaling records of the database, an intermediate aggregation processing server for performing an aggregation processing of intermediate aggregation results, and the inquiry processing A server, the input / output / aggregation server, and an intermediate aggregation processing server, one or more columns of the record are designated as grouping columns, a group identifier is associated with the grouping column value, and the group identifier is assigned. Are classified into one group, and one or more columns of the record are designated as columns to be totalized, and a second grouping process is performed for each record classified into each group. In the database processing system, the input / output / totaling server stores a totaling result storage area. Means for securing the data in the main memory of the input / output / aggregation server, means for generating a group identifier from the value of the grouping column of each record, and a storage location for the aggregation result corresponding to the group identifier from the value of the group identifier Classifying means for uniquely determining the totaling result, a totaling means for updating the totaling result stored in the storage area based on the value of the totaling target column of the record, and a grouping identifier corresponding to the partial totaling result. Means for transferring the partial tabulation results to an intermediate tabulation server that performs integration processing of the partial tabulation results, wherein the intermediate tabulation server integrates the partial tabulation results received from the input / output / tabulation server and generates an intermediate result. Means for transferring the intermediate counting result to the inquiry processing server, wherein the inquiry processing server receives the result from the intermediate counting processing server. It realized by having a means for integrating the intermediate aggregate results.
[0019]
Further, in the second database processing system, when the tally result cannot be stored in the tally result storage area secured in the main memory of the input / output / tally server, the tally result in the middle of the tally in the main memory is entered. According to the corresponding rules of the output / aggregation server, the data is transferred to the intermediate aggregation server as a partial aggregation result, and the intermediate aggregation server performs classification and aggregation processing of the partial aggregation result, thereby totaling the records stored in the respective input / output / aggregation servers. It is realized by performing processing.
[0020]
Further, in the second database processing system, when the tally result does not fit in the tally result storage area secured on the main memory of the input / output / tally server, the tally result in the middle of the tally corresponds to the tally result. After sorting the values of the grouping identifiers on the main memory, the data is saved as an intermediate aggregation result in the auxiliary secondary storage device provided in the input / output aggregation server, and when the aggregation processing is completed, the aggregation result on the main memory and the auxiliary This is realized by performing a merging process with the zero or more sorted intermediate tabulation results saved in the next storage device, thereby performing a tabulation process on records distributed to the respective input / output / counting servers.
[0021]
Further, in the second database processing system, when the tally result does not fit in the tally result storage area secured on the main memory of the input / output / total server, the most recently referred to among the tally results in the middle of the tally. After a part of the calculated totaling result is left, the remaining totaling result is sorted on the main storage with respect to the value of the grouping identifier corresponding to the relevant totaling result, and then the intermediate totaling is performed on the auxiliary secondary storage device of the input / output and totaling server. As a result, at the time when the totaling process is completed, the merging process is performed between the totaling result in the main storage and the zero or more sorted intermediate totaling results saved in the auxiliary secondary storage device. This is realized by performing the tallying process on the records distributed to the input / output and tallying server.
[0022]
Further, in the second database processing system, when the totaling result cannot be stored in the totaling result storage area secured in the main memory of the input / output / totaling server, records that could not be totaled are input. When the data is saved in the auxiliary secondary storage device provided in the output / aggregation server and the aggregation process is completed, between the aggregation result in the main memory and the aggregation result of the zero or more records saved in the auxiliary secondary storage device. This is realized by performing the aggregation processing on the records distributed to the respective input / output / aggregation servers.
[0023]
Further, in the second database processing system, when the totaling result cannot be stored in the totaling result storage area secured in the main memory of the input / output / totaling server, records that could not be totaled are input. When the ratio of records that can be totaled in the main storage falls below a certain value, the totaling result in the middle of totaling corresponds to the totaling result when the data is saved in the auxiliary secondary storage device of the output and totaling server. After sorting on the main memory with respect to the value of the grouping identifier to be performed, it is saved as an intermediate aggregation result in the auxiliary secondary storage device provided in the input / output / aggregation server, and when the aggregation processing is completed, the aggregation result on the main memory is Merge processing between the aggregation result of the zero or more records saved in the auxiliary secondary storage device and the zero or more sorted intermediate aggregation results saved in the auxiliary secondary storage device By performing, realized by performing the aggregation process for records distributed to each input and output and tallying server.
[0024]
In addition, when the intermediate aggregation server performs the classification and aggregation processing of the partial aggregation results, if the aggregation results cannot be stored in the aggregation result storage area secured on the main memory of the intermediate aggregation server, the aggregation results in the middle of the aggregation are regarded as relevant. After sorting the values of the grouping identifiers corresponding to the aggregation results on the main memory, the data is saved as an intermediate aggregation process in an auxiliary secondary storage device provided in the intermediate aggregation server, and when the aggregation process is completed, the aggregation on the main memory is performed. By performing a merge process between the result and the zero or more sorted intermediate tabulation results saved in the auxiliary secondary storage device, performing a tabulation process regarding the partial tabulation results distributed to the respective intermediate tabulation servers. It is realized by.
[0025]
In addition, when the intermediate tabulation server performs the classification tabulation processing of the partial tabulation results, if the tabulation results cannot be stored in the tabulation result storage area secured in the main memory of the intermediate tabulation server, After leaving a part of the most recently referred tally result and sorting the remaining tally results on the main memory with respect to the grouping identifier value corresponding to the tally result, the auxiliary secondary storage device provided in the intermediate tally server At the time when the tallying process is completed, the merging process is performed between the tallying result in the main memory and the zero or more sorted intermediate tallying results saved in the auxiliary secondary storage device. This is realized by performing the tallying process on the partial tallying results distributed to the respective intermediate tallying servers.
[0026]
In addition, when the intermediate tabulation server performs classification and tabulation of partial tabulation results, if the tabulation results cannot be stored in the tabulation result storage area secured in the main memory of the intermediate tabulation server, the tabulation can be overflown. The records that did not exist are saved in the auxiliary secondary storage device provided in the intermediate aggregation server, and when the aggregation process is completed, the aggregation results in the main storage and the aggregation results of the zero or more records saved in the auxiliary secondary storage device This is realized by performing a merging process between and the totaling process regarding the partial totaling results distributed to the respective intermediate totaling servers.
[0027]
In addition, when the intermediate tabulation server performs classification and tabulation of partial tabulation results, if the tabulation results cannot be stored in the tabulation result storage area secured in the main memory of the intermediate tabulation server, the tabulation can be overflown. The records that did not exist are backed up to the auxiliary secondary storage device of the intermediate aggregation server, and when the ratio of records that could be aggregated in the main memory falls below a certain value, the aggregation results in the aggregation are counted. After sorting the values of the grouping identifiers corresponding to the results on the main memory, the results are saved in the auxiliary secondary storage device of the intermediate aggregation server as the intermediate aggregation results, and when the aggregation processing is completed, the aggregation results on the main memory are completed. Between the count result of the zero or more records saved in the auxiliary secondary storage device and the sorted intermediate count result of the zero or more records saved in the auxiliary secondary storage device. By performing the process, it is realized by performing the aggregation process for Partial counting result which is distributed to each of the intermediate aggregation server.
[0028]
Further, one or more columns of the records constituting the table stored in the database processing system are designated as grouping columns, and a group identifier is assigned one-to-one or many-to-one with respect to the designated grouping column value. Correspondingly, a plurality of records having the same value of the group identifier are classified into one group, and one or more columns of the records belonging to each of the classified groups are designated as the aggregation target columns, and the designated In a database processing system that performs a totaling process related to the values of the columns, the grouping column in the classification and totaling process is realized by having a format for specifying a range of values of the group identifier.
[0029]
Further, one or more columns of records constituting a table stored in the database processing system are designated as grouping columns, and a group identifier is assigned one-to-one or many-to-one with respect to the designated grouping column value. , And classify a plurality of records having the same group identifier value into one group, specify one or more columns of records belonging to each of the classified groups as a column to be aggregated, and specify In a database processing system that performs a totaling process related to the values of the columns that have been set, this is realized by having a format for specifying the upper limit of the number of groups obtained as a result of the grouping when specifying the grouping columns in the classification and totalizing process.
[0030]
Further, one or more columns of records constituting a table stored in the database processing system are designated as grouping columns, and a group identifier is assigned one-to-one or many-to-one with respect to the designated grouping column value. , And classify a plurality of records having the same group identifier value into one group, specify one or more columns of records belonging to each of the classified groups as a column to be aggregated, and specify Format for specifying a user-defined function to define the correspondence between grouping column values and group identifiers in the specification of grouping columns in classification and aggregation processing in a database processing system that performs aggregation processing on the values of columns that have been set It is realized by having.
[0031]
Further, in the above database processing system, the specification of the grouping column of the classification and aggregation processing has a format for specifying a range of the value of the group identifier, and whether or not the range of the value of the group identifier is specified for each grouping column In response to the above, an array method in which the storage result storage position is directly determined by calculation from the value of the grouping identifier and a hash method in which the aggregation result storage position is determined by comparing the grouping identifier are used separately for each grouping column.
[0032]
Further, in the above database processing system, the specification of the grouping column of the classification and aggregation processing has a format for specifying the upper limit of the number of groups obtained as a result of the grouping, and the range of the value of the group identifier for each grouping column , A group for a group identifier having a value that deviates from the upper limit or the lower limit of the specified range is prepared, and the counting is performed on the records classified into the group.
[0033]
Further, the database processing system has a format for specifying a user-defined function for defining a correspondence between a grouping column value and a group identifier, for specifying a grouping column for classification and aggregation processing, This is realized by using a user-defined function for defining the correspondence between the group identifier and the group identifier, and performing a totaling process by narrowing the range of the value of the group identifier.
[0034]
BEST MODE FOR CARRYING OUT THE INVENTION
(Example 1)
FIG. 1 shows a configuration of an embodiment of a parallel database processing system according to the present invention. In FIG. 1, 1 is a secondary storage device for storing a table, 2 is an input / output server for reading a partial table 7 from the secondary storage device, 4 is a totaling server for performing a classification and totalizing process of records, and 5 is a totalizing server. An inquiry processing server that integrates the tally results and creates a final tally result, 3 is a network for exchanging records and tally results between respective nodes, and 6 issues a classification tally processing request to the database. , And a terminal device for taking out the aggregation result from the inquiry processing server 5. As the network 3, an arbitrary inter-processor connection network such as a LAN, a WAN, and a dedicated hardware device can be used.
[0035]
The secondary storage device 1 stores records to be grouped. The record is divided and stored in a plurality of secondary storage devices based on a division method such as a hash division method or a key value division method. The input / output server 2 holds a data buffer 8 for temporarily storing records read from the secondary storage device 1, a record distribution unit 9 for determining a tallying server to which records are to be distributed, and a plurality of transmission buffers 10. The data buffer 8 is secured on a main memory provided in the input / output server.
[0036]
The partial table 7 held by the secondary storage device 1 is transferred to the data buffer 8 in block units. The record distributing means 9 sequentially retrieves the records in the data buffer 8 and determines the totaling processing server 4 to which the records should be totalized by referring to the values of the columns to be grouped for each record. The input / output server 2 extracts only the grouping columns and the aggregation target columns necessary for the aggregation processing of the record, and distributes them to the aggregation processing servers 4 from the transmission buffer 10 through the network 3.
[0037]
The tallying processing server 4 includes a tallying unit 12 for tallying the records transferred from the plurality of receiving buffers 11 and the input / output server 2, a tallying result storage area 13 for storing tallying results, and an auxiliary for saving tallying results during tallying. It holds the secondary storage device 17. The tally result storage area is secured on the main memory of the tally processing server 4. The tallying server performs the tallying process on the records transferred to the reception buffer 11 using the tallying means 12 and stores the intermediate tallying result in the tallying result storage area 13. If the intermediate tabulation result does not fit in the tabulation result storage area 13, the tabulation result in the middle of the tabulation is saved in the auxiliary secondary storage device 17 as a partial tabulation result. The aggregation processing server 4 transfers the intermediate aggregation result to the inquiry processing server 5 when the aggregation processing is completed for all the distributed records.
[0038]
The inquiry processing server 5 holds a plurality of receiving buffers 14, a totaling result integrating unit 15, and a final totaling result storage area 16. The inquiry processing server 5 receives the tallying process from the terminal device 6, determines a tallying process execution procedure, and issues an execution instruction to the input / output server 2 and the tallying processing server 4. The query processing server 5 collects the intermediate tabulation results created by the respective tabulation processing servers 4 through the network 3, performs an integration process between them, creates a final collection result, and stores the result in the final tabulation result storage area 16. I do. The final totaling result storage area 16 is held in the main storage or the secondary storage provided in the inquiry processing server 5. The terminal device 6 requests the inquiry processing server 5 to perform a totaling process, and as soon as the final totaling result is created on the inquiry processing server 5, sequentially extracts the final totaling result.
[0039]
FIG. 6 shows a processing flow of the entire classification and aggregation processing in this embodiment.
First, the terminal device 6 transfers the totalization processing execution request to the inquiry processing server 5 (processing request issuance 700), and waits for a response from the inquiry processing server. On the other hand, the query processing server 5 that has received the tally processing execution request creates an execution plan for performing the requested tally processing (processing request reception 701), and according to the plan, the tally processing program on the input / output server or the query processing server. Is started (program start 702). The query processing server 5 waits for a response from the tally processing server 4 after initializing the reception buffer 14, the final tally result storage area 16 and the like (area initialization 703).
[0040]
The input / output server 2 and the tallying server 4 having started the tallying processing program by the query processing server 5, according to the instruction from the query processing server 5, the data buffer 8, the transmission buffer 10, the reception buffer 11, the tallying result storage area 13, and the like. Are initialized (704, 705). After performing the area initialization, the input / output server 2 determines the transfer destination totaling processing server of the record based on the distribution processing means 9 for each record read from the secondary storage device 1 into the data buffer 8, and The record is transferred through (706). The input / output server 2 transmits an end notification to each tabulation processing server when all records have been distributed (707), and ends (708).
[0041]
On the other hand, in the tabulation processing server 4 to which the records have been distributed from the input / output server 2, as soon as the records are received, the tabulation processing of the records is performed and the creation of the intermediate tabulation result is started. If the intermediate tabulation result does not fit in the pre-allocated tabulation result storage area, the intermediate tabulation result is saved to the auxiliary secondary storage device 17 as a partial tabulation result (709). At the time of receiving the end notification from all the input / output servers (710), if the saving process of the intermediate counting result in the secondary storage device has been performed (711), each of the tallying servers has a partial An integration process of the aggregation results is performed (712).
[0042]
The intermediate tabulation results created by the above procedure are transferred from each tabulation processing server 4 to the inquiry processing server 5 in block units (713). When the transfer of all the intermediate tabulation results is completed, the tabulation processing server transmits an end notification to the inquiry processing server (714), and ends (715).
[0043]
The query processing server 5 that has received the intermediate totaling result sequentially stores the intermediate totaling result in the final totaling result storage area 16 and transfers the final totaling result to the terminal device 6 according to a request from the terminal device 6 (716). ). When the inquiry processing server receives the end notification from all the tallying servers (717), it sends the end notification to the terminal device (718) and ends (719). The terminal device 6 requests the final total result to the inquiry processing server 5 as necessary, and receives the final total result (720). Upon receiving the end notification from the inquiry processing server 5 (721) and confirming the end of the tallying process, the terminal device 6 ends its process (722).
[0044]
In the present embodiment, it is possible to execute in parallel on a record-by-record basis between processes adjacent to each other, except between the tallying process (709) and the intermediate tallying process transfer (713) in the tallying server.
[0045]
Hereinafter, the flowchart of the tallying processing program on each node will be described in more detail.
[0046]
FIG. 7 shows a flowchart of the classification and aggregation process in the input / output server 2. The input / output server 2 first initializes the data buffer 8 and the transmission buffer 10 according to the instruction from the inquiry processing server 5 (80). Next, the records stored in the secondary storage device 1 are transferred to the data buffer 8 in block units (81). Next, the input / output server 2 sequentially retrieves the records from the data buffer 8 (82), and determines the distribution destination aggregation processing server 4 for each record according to the record distribution means 9 (described later) (83). Each record is accumulated in units of pages for each distribution destination (84), and is transferred from the transmission buffer 10 to the corresponding tallying server 4 when the page is filled (85) (86). When all the records have been transferred, the input / output server 2 transmits an end notification to each of the tallying servers (87) and ends (88).
[0047]
FIG. 8 shows a flowchart of the classifying and totaling process in the totalizing server 4. The tallying processing server 4 first initializes the tallying result storage area 13 and the receiving buffer 11 according to the instruction from the inquiry processing server 5 (90). The tallying server 4 retrieves records one by one from the record page (91) transferred from the input / output server 2 to the reception buffer 11 (92), and applies a group classification unit (described later) to each record. Then, a group identifier for specifying the group to be classified is determined (93). Next, a totaling process is performed on the retrieved records (95), and the obtained totaling result is written into the totaling result storage area 13 (96). When the hash method is used as the aggregation result storage format, the aggregation result may not fit in the aggregation result storage area allocated in advance (97). In this case, the intermediate counting result is sorted as the partial counting result on the main storage with respect to the value of the corresponding grouping identifier, and then saved in the auxiliary secondary storage device 17 (98). Each tallying server obtains an intermediate tallying result by repeating the tallying process for the received records (99).
[0048]
When the tally processing server receives the end notification from all of the input / output servers (910), if the partial tally result is saved in the auxiliary secondary storage device 17 during tallying (911), the tally processing server at this time A merge sort process is performed between the evacuated partial tabulation results to create an intermediate tabulation result (912). Since the partial tabulation results saved in the secondary storage device are sorted, the merging process between the partial tabulation results can be executed efficiently. If the partial aggregation result is not saved, there is no need to perform any processing. After completing the tallying process of all records, each tallying server transfers the obtained intermediate tallying result to the query processing server (913), and when the transfer is completed, sends a termination notification to the query processing server. Transmit (914) and end (915).
[0049]
FIG. 9 shows a flowchart of the classification and aggregation process in the inquiry processing server 5.
[0050]
First, the inquiry processing server 5 receives a totalization processing execution request from the terminal device 6 (100), and starts a totalization processing program on the input / output server 2 or the totalization processing server 4 according to the content of the processing (101). Next, the inquiry processing server 5 initializes the reception buffer 14 and the final tally result storage area 16 (102), and waits for the reception of the intermediate tally result from the tally processing server 4. As soon as the query processing server 5 receives the intermediate tabulation results from each tabulation processing server 4 (103), the interim tabulation results are connected in order to obtain the final tabulation result (104). The query processing server 5 sequentially transfers the final tabulation results from the beginning in accordance with the request from the terminal device 6 (105). When the inquiry processing server 5 has finished transferring all the final total results (106), it sends an end notification to the terminal device 6 (107) and ends (108).
[0051]
FIG. 10 shows a flowchart of the process in the terminal device 6. The terminal device 6 issues a tallying execution request to the inquiry processing server 5 (110), and then waits for reception of the final tallying result from the inquiry processing server 5 (111). The terminal device 6 requests the inquiry processing server 5 to transfer the final total result one after another. When the terminal device 6 receives the end notification from the inquiry processing server 5 (112), the process ends (113).
[0052]
Hereinafter, the record distribution unit, the group classification unit, the tallying unit, the tallying result storage system, and the integration processing unit which have appeared in the above description will be described in further detail.
[0053]
The record distributing means in this embodiment is a method of redistributing a group of records divided and stored in advance in a plurality of secondary storage devices 1 to each totaling server 4 via the input / output server 2. In the present record distributing means, records classified into the same group in the tallying process must be distributed to the same tallying server 4 without fail. That is, in the distribution according to the present embodiment, when a set of records distributed to the tallying server Ni is Si and a set of records classified into the group Gj is Tj, there exists a certain i for all j, and Si 、 Gj and Sk (k ≠ i) ∩Gj = empty set. Since records classified into the same group are always tabulated by the same tabulation server, the query processing server 5 can easily obtain the final tabulation result simply by joining the sent intermediate tabulation results.
[0054]
As a specific example of the record distribution means, a method such as hash division, key value division, and round robin division can be applied. However, when the records divided and stored in the secondary storage are divided in advance so as to satisfy the above-described conditions in performing the aggregation processing, the input / output server directly performs the aggregation processing without distributing the data. , And may perform a totaling process.
[0055]
The group classification means according to the present embodiment means that a group identifier is assigned to records distributed to each totalization processing server 4 in accordance with the grouping indicated by the classification totalization process issued from the terminal device 6, and the records are classified based on the identifier. How to The correspondence between records and group identifiers will be described in detail below.
[0056]
The group identifier in the present embodiment is an identifier that specifies a group to which each record is to be classified by the group classification means. The group identifier is a discrete value such as an integer value or a character string that can be identified from each other. In the case of the multidimensional classification and aggregation processing for performing group classification based on the values of a plurality of columns, a final group identifier is generated by combining the group identifiers corresponding to the respective grouping columns. For example, when a record having a column {Ai} is grouped with respect to a grouping column {Gi} ({Ai}), a group identifier correspondence function gi is applied to each grouping column Gi, and a group identifier obtained as a result is obtained. Set <g1 (G1),. . , Gn (Gn)> as the group identifier of the record.
[0057]
In this embodiment, a user-defined grouping function can be used for association between the grouping column and the group identifier.
[0058]
In the group classification means, an appropriate group identifier is associated according to the value of the grouping column, and each record is associated with one of the groups. As a method of determining the value of the group identifier, an appropriate method can be selected from the following three methods.
[0059]
The first method uses the value of the column (grouping column) used for grouping as it is, the second method uses a value obtained by applying a built-in function to the grouping column value, A third method is to use a value obtained by applying a user-defined grouping function to a grouping column value.
[0060]
The user-defined grouping function needs to output discrete values that can be distinguished from each other. By using the user-defined group function, the domain of the grouping column value can be divided into several sections, and grouping can be performed for each section. For example, it is useful for a process of using the age as a grouping column value, grouping records by age, and obtaining an average value of income by age, and the like. In addition, if a possible value is known for a group column value having a distribution of discrete values whose section is not limited, a user-defined grouping function is applied to limit the distribution of the group identifier to the section. It can be converted into a continuous distribution, and an array system with high access efficiency can be used as a totaling result storage system. For example, when a product name is used as a grouping column value, if an appearing product name is known, a number is associated with each product name, and an array of the maximum number of the corresponding number is used instead of a hash as a totaling result storage method. Can be substituted.
[0061]
In the present embodiment, when the range and type of the distribution of the grouping column values are known in advance, designation (grouping designation) of the distribution of the group identifier can be performed for each grouping column value. There are the following three types of designation methods. The first method is a group identifier range designation, the second method is a group identifier upper limit designation, and the third method is a group identifier type upper limit designation.
[0062]
In the group identifier range specification, it is possible to specify an upper limit and a lower limit of possible values of a group identifier used for group classification. It can be used when the order can be defined for the group identifier.
[0063]
If there is a record having a grouping column value out of the range, a special group identifier indicating that the value is equal to or less than the lower limit and equal to or greater than the upper limit is prepared, and the tally process for the exceptional value is separately performed. By this means, in the aggregation result storage method, the upper limit value of the area required for the aggregation result is estimated for the grouping column, and for the grouping column, the array method is selected as the aggregation result storage position determination method, and the efficiency is improved. A storage location can be determined.
[0064]
Group identifier upper limit designation means that when the group identifier used for group classification is an integer, the lower limit is fixed at 0 in the group identifier range designation, and the upper limit can be designated. This makes it possible to specify a simple group identifier range.
[0065]
In the group identifier type upper limit designation, the upper limit value of the possible value types of the group identifier used for the group classification is specified. This makes it possible to estimate the upper limit value of the area required for the aggregation result for the grouping column.
[0066]
The aggregation processing means in the present embodiment is a method of sequentially calculating statistics while extracting records one by one from a given set of records. As a specific aggregation process, there are a total value, an average value, and the like. Tallying may be performed using auxiliary information such as the number of already-recorded records in addition to the record information.
[0067]
The aggregation result storage method in the present embodiment is a method for determining the storage method of the group aggregation result. In the present embodiment, one of the three methods, the array method, the hash method, and the hash / array combined method, is selectively used for each grouping column value by using a totaling result storage method selection unit described later.
[0068]
FIG. 11 shows an arrangement result storage format of the array method. This method is used when it is guaranteed that the tally result will fit in the reserved area on the main memory. This storage format is composed of one array 120 whose elements are aggregation results.
[0069]
In the array method, the group identifier is used as a subscript of the array, and the aggregation result is stored as an element of the array. The storage area for the array is secured in main memory. This method does not require a comparison operation to determine the storage location, so that the aggregation results can be stored at high speed. When the value of the grouping column deviates from the range of the upper limit and the lower limit, groups corresponding to the group identifiers that are equal to or more than the upper limit and are equal to or less than the lower limit are provided and separately counted.
[0070]
FIG. 12 shows a hash result totaling format. This method is used when there is a possibility that the result of the aggregation does not fit in the reserved area on the main memory. This storage format includes one hash table 130, a plurality of hash lists 131, a plurality of hash entries 132, and one tally result storage area 133. In this method, the hash table 130 holds a pointer to the hash list 131. The hash list 131 is a list of hash entries 132. The hash entry 132 holds a pointer to a record that stores the aggregation result and a pointer to another hash entry having the same hash value. The tally result storage area 133 is secured from the reserved area on the main memory. However, when the reserved area runs short, it will be dealt with by a method described later.
[0071]
In the hash method, a group identifier is applied to a hash function, an appropriate hash list is selected from a hash table, and a target storage location is determined from a hash entry. In this method, it is necessary to follow a hash list when deciding a storage location, but since only a necessary storage area is consumed, the storage efficiency of the main memory is good.
[0072]
FIG. 13 shows a tally result storage method of the combined hash / array method. In the multidimensional classification and aggregation processing, this method is used when the storage method of the array method and the storage method of the hash method are properly used for each grouping column. In the following, a group identifier {gi} used for multi-dimensional grouping of records is defined as a hash group identifier {hi} ({gi}) serving as a key of a hash method based on a tally result storage format selection method described later; An array group identifier {ci} ({gi}), which is a subscript of the array method, is divided exclusively ({hi} ci} = {gi}, {hi {ci} = empty set).
[0073]
This storage format includes one hash table 140, a plurality of hash lists 141, and a plurality of hash entries 142. In this method, the hash table 140 includes a pointer to the hash list 141. The hash list 141 is a list of hash entries 142. The hash entry 142 includes a pointer to another hash entry having the same hash value, a hash group identifier corresponding to the stored totaling result, and an array storing the totaling result having the same hash group identifier.
[0074]
In the hash / array combined method, first, an array in which the aggregation result is to be stored is found from the hash entry using the hash group identifier. If the target array is not registered in the hash entry, a new array is secured in the reserved area on the main memory and added to the hash entry. A method for coping with the case where the array cannot be secured in the reserved area will be described later. After the target sequence is determined, a location where the totaling result is to be finally stored is specified using the sequence group identifier according to the storage location determination method of the array method. In this method, according to the grouping of each grouping identifier, whether to use the array method or the hash method is used properly, so that the storage location can be determined quickly and the storage can be performed with high storage efficiency. I do.
[0075]
In the following, a description will be given of a method for coping with the case where the intermediate tabulation result does not fit in the reserved area on the main memory in this embodiment. The reason why the tallying result cannot be stored in the reserved area on the main memory in the tallying processing server 4 is that the hash method or the hash / array combined method is used as the tallying result storage method, and the hash entry has an array storage area larger than the reserved area. When trying to add. In the following, at the point in time when the tally result no longer fits in the reserved area on the main memory, the tally result in the tally accumulated in the tally processing server is referred to as a partial tally result.
[0076]
FIGS. 14 and 16 show a processing method and a flowchart in the case where the intermediate tabulation result in the present embodiment does not fit in the reserved area on the main memory.
[0077]
In the present embodiment, when the tally result does not fit in the reserved area on the main memory in the tally processing server 4 (170), the partial tally result is sorted on the main memory of the tally processing server (171). It is evacuated to the device 17 (150, 172). After saving the partial aggregation result, the aggregation result storage area on the main memory can be reused.
[0078]
When the tabulation of all the records is completed (173), the tabulation processing server 4 performs an N-way merge process between the plurality of partial tabulation results saved in the auxiliary secondary storage device 17 (151, 174). , And transfers the processing result to the inquiry processing server 5. In this method, since a random I / O to the auxiliary secondary storage device does not occur, it is possible to efficiently create a totaling result.
[0079]
Further, when the totaling result cannot be stored in the reserved area on the main storage, the partial totaling result on the main storage is not saved in the auxiliary secondary storage 17 but based on the LRU (Least Recently Used) method. Part of the partial summary result referred to in the main memory is retained in the main memory, the remaining partial summary result is saved in the auxiliary secondary storage, and when the aggregation of all records is completed, the partial summary result in the main memory is It is also possible to create an aggregation result by performing an N-way merge process with a plurality of partial aggregation results saved in the auxiliary secondary storage device 17. According to this method, the ratio of records counted in the main memory can be increased, and the counting process can be performed efficiently.
[0080]
Furthermore, when the totaling result cannot be stored in the reserved area on the main storage, the record that cannot be totaled on the main storage is not saved in the auxiliary secondary storage 17 instead of saving the partial totaling result on the main storage. Is saved in the auxiliary secondary storage device, and when all records have been counted, the N-way merge process is performed between the partial count result in the main storage and the record saved in the auxiliary secondary storage device 17. It is also possible to create a total result. In this method, when the number of records that could not be counted on the main memory is small, the counting process can be performed efficiently.
[0081]
In addition, in the above-described method, the ratio of records actually counted in the main memory among the records to be counted in the counting process is monitored, and when the ratio falls below a certain value, the ratio in the main memory is reduced. Are saved in the auxiliary secondary storage device, and when the totalization of all records is completed, the partial aggregation results in the main storage and the plurality of partial aggregation results saved in the auxiliary secondary storage device 17 are saved. It is also possible to perform an N-way merge process between the record and the record saved in the auxiliary secondary storage device 17 to create a total result. In this method, when the distribution of records to be tabulated is biased, tabulation processing can be performed efficiently.
[0082]
In this embodiment, the storage method of the tally result storage area is selected based on the grouping designation for each group identifier. Hereinafter, a method of selecting a storage method will be described.
[0083]
When “Group identifier range specification (including group identifier upper limit specification)” is specified for all group identifiers used for multidimensional classification and aggregation processing, the upper limit of the size of the total storage area from each specification The value is calculated, and it is determined whether or not the value can be stored in the reserved area on the main memory of each totalization processing server. If it fits, a total storage area is secured in main memory in the form of an array. If it does not fit, after switching to a hash method for some or all of the identifiers, an array method is adopted for the remaining identifiers.
[0084]
If “group identifier range designation (including group identifier upper limit designation) or group identifier type upper limit designation” is designated for all group identifiers used for multidimensional classification and aggregation processing, the group identifier is determined from each designation. Of the type, the upper limit of the size of the total storage area is calculated, and it is determined whether or not each total processing server can fit in the reserved area on the main memory. If it fits, a necessary area is reserved in the main memory in advance, and the necessary area is obtained from here. If it does not fit, the identifier for which “group identifier type upper limit is specified” is switched to the hash method, the remaining identifiers are arranged in an array method, and a total storage area is secured in the main memory for the array method part.
[0085]
In cases other than the above, a hash method is selected for all group identifiers.
[0086]
As described above, according to the characteristics of the respective grouping identifiers specified by the grouping specification in the grouping means, the storage result can be determined quickly and the storage efficiency is improved by selectively using the aggregation result storage method. The system can be realized.
[0087]
The integration processing means in this embodiment is a method of integrating a plurality of intermediate aggregation results transferred from the aggregation processing server 4 and creating a final aggregation result in the inquiry processing server 5. In the case of the present embodiment, the intermediate tabulation results on each tabulation processing server hold the tabulation results for groups that are independent of each other, so there is no need to recalculate between the respective intermediate tabulation results. By connecting the tally results, a final tally result can be obtained.
[0088]
The features of this embodiment are as follows.
[0089]
In this embodiment, in the classification and aggregation processing, the classification processing and the aggregation processing are executed in a record unit in parallel by using the aggregation result storage area determination unit that can uniquely determine the position of the aggregation result storage area based on the value of the record. To be able to
When the totaling result does not fit in the reserved area on the main memory, the partial totaling result is sorted on the main memory, saved to the auxiliary secondary storage device, and finally integrated by N-way merge to obtain the auxiliary secondary Suppress the issue of random I / O to the storage processing device,
By specifying information relating to the range of the grouping identifier value or the upper limit of the type for each grouping identifier, it is possible to determine a storage location at high speed and realize a storage method with high storage efficiency.
[0090]
In the following, an example of grouping designation in the present embodiment will be described with SQL in mind as a query language for the database.
[0091]
First, the syntaxes of group identifier range designation, group identifier upper limit designation, and group identifier type upper limit designation are shown according to BNF notation.
[0092]
<Group identifier range specification> :: = RANGE <Column name> [Lower limit,. . , Upper limit] [｛, <column name> [lower limit,. . ,upper limit]} . . . ];
<Group identifier upper limit specification> :: = MAX <column name> Upper limit value [{, <column name> upper limit value}. . . ];
<Group identifier type upper limit specification> :: = GROUP MAX <Column name> Upper limit value [{, <Column name> Upper limit value}. . . ];
However, the above designation method is one implementation example. The timing of the grouping specification may be at the time of defining the format of the table, at the time of issuing a query, or at the time of specifying the comment.
[0093]
An example of the designation of the group classification means through the SQL sentence will be described below using the above example.
[0094]
CREATE TABLE sales results (product code INT, sales location STRING (32), sales date INT, price INT);
GROUP MAX Sales record. Product code 1000, sales record. Sales area 100;
RANGE sales results. Sale date [1,. . , 31];
SELECT Product code, sales location, date of sale, SUM (price) FROM sales results GROUP BY Product code, sales location, date of sale;
The first SQL statement defines a sales performance table having columns of product code, sales location, sales date, and price. The second SQL statement specifies 1000 and 100 as the upper limit value of the product code and the type of the place of sale, respectively. The third SQL statement indicates that the range of the sale date value is 1 or more and 31 or less. The fourth SQL statement is a tabulation processing request statement. The records in the sales record table are grouped according to the product code, the sales location, and the sales date, and the total price is calculated for each group. Instructed.
[0095]
In the fourth SELECT statement, a hash method is selected for a product code and a sales place, and an array format is selected for a sales date. Assuming that the size of the record of the aggregation result for each group is 44 bytes, approximately 44 bytes × 1000 × 100 × 33 = 145.2 Mb is required as the aggregation result storage area. If such an area can be secured in the main memory, a necessary area is secured in the main memory before the execution of the search processing, and efficient aggregation processing without dynamic memory allocation can be performed. If an area of a required size cannot be initially secured, an area of 44 × 33 = 1452 bytes is dynamically allocated as an array area from the main storage as needed. When the assignment becomes impossible, the partial totaling result is saved in the secondary storage device based on the method described above.
[0096]
FIG. 18 shows an example of the classification and aggregation process in this embodiment. In this example, a case is described in which the above-described classification and aggregation processing is executed based on the present embodiment using two input / output servers, two aggregation processing servers, and one inquiry processing server. First, among the records separately stored in the two input / output servers (190), the record whose product code is HT44966 is distributed to the tallying server 1, and the record whose product code is PC15550 is distributed to the tallying server 2 ( 191). Next, in each tallying server, grouping is performed on the place of sale and the date of sale, and tallying is performed for each group (192). Upon completion of the tallying process for the distributed records, each tallying server transfers the intermediate tallying result to the inquiry processing server (193). The inquiry processing server combines the transferred intermediate tabulation results to create a final tabulation result (194).
[0097]
However, in the above embodiment, each server may operate on the same physical computer, or may operate on separate computers connected via a network.
[0098]
Also, in the classifying means of the input / output server in the actual example, instead of determining the classification destination according to the value of the grouping identifier, distribution is performed so that the number of distribution records becomes equal for each aggregation server, and load distribution is performed. For example, the query processing node may again perform the classification and aggregation processing of the intermediate aggregation result to complete the entire classification and aggregation processing.
[0099]
Further, in the above-described embodiment, two or more stages of the tallying server may be connected via the network, and the classification and tallying process may be performed hierarchically for a large number of records.
[0100]
(Example 2)
FIG. 20 shows the configuration of another embodiment of the parallel database processing system according to the present invention.
[0101]
20, reference numeral 1 denotes a secondary storage device for dividing and storing a table in units of partial tables 7, 18 an input / output / aggregation server for reading the partial table 7 from the secondary storage device and creating a partial aggregation process, and 19 a partial storage device. An interim aggregation server that performs another classification and aggregation process between the aggregation results and creates an intermediate aggregation result, a query processing server that integrates the aggregation results of each intermediate aggregation server, and creates a final aggregation result, 3 A network 6 for exchanging records and tally results between the respective nodes, and a terminal device 6 for issuing a classification tally processing request to the database and extracting the tally results from the query processing server. As the network 3, an arbitrary inter-processor connection network such as a LAN, a WAN, and a dedicated hardware device can be used.
[0102]
Hereinafter, the configuration and operation of the input / output / aggregation server 18 and the intermediate aggregation server 19 in the second embodiment will be described. Other parts of the second embodiment that are not described below are the same as those of the first embodiment.
[0103]
The input / output / aggregation server 18 is a data buffer 8 for temporarily storing records read from the secondary storage device 1, an aggregation processing means 12 for performing aggregation processing of records retrieved from the data buffer, and stores an aggregation result. It holds a partial tabulation result storage area 25, a record distribution unit 9 that determines a distribution destination of the tabulation result records, and a plurality of transmission buffers 10. The data buffer 8, the tally result storage area 13, and the transmission buffer 10 are secured on the main memory of the input / output / tally server 18. The input / output / aggregation server 18 transfers the partial table 7 held in the secondary storage device 1 to the data buffer 8 in block units. Next, the input / output / aggregation server 18 sequentially retrieves the records from the data buffer 8 and creates the aggregation result in the partial aggregation result storage area 25 by using the aggregation processing unit 12. As soon as the totaling result in the totaling result storage area reaches the capacity of the totaling result storage area, the intermediate distribution server 19 of the distribution destination is determined by using the record distribution means 9, and is transmitted as the partial totaling result via the transmission buffer 10. The data is transferred to the totaling server 19.
[0104]
The intermediate totaling server 19 includes an auxiliary secondary storage device 17 for saving intermediate results of the partial totaling results transferred from the plurality of receiving buffers 11 and the input / output / totaling server, and a classification and totaling process between the partial totaling results. It holds a tally result storage area 13 that provides a work area. The partial totaling result transferred from the input / output / totalizing server 18 is subjected to another classification and totaling process in the intermediate totaling server 19. If the tally result does not fit in the tally result storage area 13 during the tally process, the tally result in the tally is saved in the auxiliary secondary storage device. After receiving all the partial tabulation results from the input / output / tabulation server 18, the intermediate tabulation server 19 performs an N-way merge process between the partial tabulation results saved in the auxiliary secondary storage device 17. The merge result is transferred to the inquiry processing server 5 as an intermediate total result.
[0105]
FIG. 21 shows the state of the processing flow of the entire classification and aggregation processing in this embodiment. First, the terminal device 6 transfers the request for executing the tallying process to the inquiry processing server 5 (2600), and waits for a response from the inquiry processing server. On the other hand, the query processing server 5 that has received the request for executing the tallying process creates an execution plan for performing the requested tallying process (2601), and according to the plan, the tallying on the input / output / totaling server 18 and the intermediate tallying server 19. The processing program is started (2602).
[0106]
The query processing server 5 initializes the receiving buffer 14, the final totaling result storage area 16, and the like (2603), and then waits for a response from the intermediate totaling server 19. The input / output / aggregation server 18 and the intermediate aggregation server 19, which have started the aggregation processing program by the inquiry processing server 5, respond to the instruction from the inquiry processing server 5 to store the data buffer 8, the transmission buffer 10, the reception buffer 11, and the aggregation result storage area. 13. Initialize the partial totaling result storage area 25 and the like (2604, 2605).
[0107]
After initializing the area, the input / output / aggregation server 18 performs the aggregation processing for each record read from the secondary storage device 1 into the data buffer 8 based on the aggregation processing means 12 (2606). The input / output / aggregation server 18 determines the intermediate aggregation server to which the partial aggregation result is to be transferred, based on the distribution processing means 9 for each record, as soon as the aggregation result reaches the capacity of the partial aggregation result storage area 25. The record page is transferred through (2623). The input / output / aggregation server 18 transmits an end notification to each aggregation processing server when all the record pages have been distributed (2607), and terminates (2608).
[0108]
On the other hand, the intermediate tabulation server 19 to which the record pages have been distributed from the input / output / tabulation server 18 performs classification and tabulation of the transferred partial tabulation results as soon as the record page is received. If the tally result does not fit in the storage area, the tally result in the middle of tally is sorted with respect to the value of the grouping identifier and then saved in the auxiliary secondary storage device. (2609). At the time of receiving the end notification from all the input / output / aggregation servers (2610), if there is a partial aggregation result evacuated to the auxiliary secondary storage device 17 (2611), each intermediate aggregation server receives the end notification (2611). A merge sort process is performed (2612).
[0109]
The intermediate tabulation result created by the above procedure is transferred from each intermediate tabulation server to the query processing server in block units (2613). At the point in time when all the intermediate tabulation results have been transferred, the intermediate tabulation server sends an end notification to the inquiry processing server (2614) and ends (2615). The query processing server 5 that has received the intermediate tabulation results sequentially stores the intermediate tabulation results in the final tabulation result storage area 15 and transfers the final tabulation results to the terminal device 6 according to the request from the terminal device 6 (2616) ). When the inquiry processing server receives the end notification from all the tally processing servers (2617), it transmits the end notification to the terminal device (2618) and ends (2619). The terminal device 6 requests the final count result from the inquiry processing server 5 as necessary, and receives the final count result (2620). The terminal device 6 ends its processing when it receives the end notification from the inquiry processing server 5 (2621) and confirms the end of the aggregation processing (2622).
[0110]
In this embodiment, since the integration processing of the partial aggregation results is shared by a plurality of intermediate aggregation servers and performed independently, efficient processing can be performed when the number of groups of the aggregation results is large.
[0111]
Hereinafter, the flowcharts of the input / output / aggregation server and the intermediate aggregation server will be described in more detail.
[0112]
FIG. 22 shows a flowchart of the classification and aggregation process in the input / output / aggregation server. The input / output / aggregation server 18 first initializes the data buffer 8, the partial aggregation result storage area 25, and the transmission buffer 10 according to the instruction from the query processing server 5 (2700). Next, the records stored in the secondary storage device 1 are transferred to the data buffer 8 in block units (2701). Next, the input / output / aggregation server sequentially retrieves the records from the data buffer 8 (2702), and determines the group identifiers of the records to be classified (2703). The input / output / aggregation server performs the aggregation processing with reference to the record value (2704), determines the aggregation result storage area on the aggregation result storage area based on the group identifier, and writes the aggregation result (2705). At this time, if the totaling result does not fit in the totaling storage area (2706), the process of transferring the partial totaling result during the totaling to the intermediate totaling server is performed (2707). When the tallying process is completed for all the read block records (2708, 2709), the final tallying result is transferred (2710), and an end notification is transmitted to the intermediate tallying server (2711). The process ends (2712).
[0113]
FIG. 23 shows a flowchart of the classification and aggregation process in the intermediate aggregation server. The intermediate totaling server 19 first initializes the totaling result storage area 13 and the receiving buffer 11 according to the instruction from the inquiry processing server 5 (2800). The intermediate aggregation server extracts the aggregation results one by one from the partial aggregation results (2801) transferred from the input / output / aggregation server to the reception buffer 11 (2803), and performs classification processing on the values of the corresponding grouping identifiers (2804). ).
[0114]
Next, the totalized value is re-counted for each classified totaling result (2805), and the result is written to the totaling result storage area 13 (2806). If the tally result does not fit in the tally result storage area (2807), the tally result is sorted with respect to the value of the corresponding grouping identifier and stored in the auxiliary secondary storage device (2808). Each intermediate totaling server repeats the totaling result for the received totaling result (2809). When receiving the end notification from all of the input / output / aggregation servers (2810), if the aggregation result has been saved in the auxiliary secondary storage device (2811), the intermediate aggregation server saves the saved aggregation result. Are performed, and an intermediate tabulation result is created (2812). The intermediate totaling server transfers the intermediate totaling result to the inquiry processing server (2813), transmits an end notification (2814), and ends (2815).
[0115]
In the following, a description will be given of a method for coping with the case where the totaling result does not fit in the reserved area on the main storage in the input / output / totaling server 18 of the present embodiment. The reason that the I / O / aggregation server 18 cannot accumulate the aggregation result in the reserved area on the main memory is that the hash result or the combined use of the hash and the array is used as the aggregation result storage method, and the hash entry has an array larger than the reserved area. This is the case when an attempt is made to add a storage area. In the following, at the point in time when the tally result no longer fits in the reserved area on the main memory, the tally result in the middle of tally accumulated on the input / output / tally server is referred to as a partial tally result.
[0116]
FIGS. 15 and 17 show a processing method and a flowchart in the case where the intermediate tabulation result in the present embodiment does not fit in the reserved area on the main storage.
[0117]
In this embodiment, when the tally result does not fit in the reserved area on the main memory in the input / output / tally server 18, each tally result included in the partial tally result is replaced with the tally result for the same group by the same intermediate tally server 19. Are classified and transferred (160). After saving the partial aggregation result, the aggregation result storage area on the main memory can be reused. The intermediate tabulation server sorts the partial tabulation results on the main memory and saves them in the auxiliary secondary storage device 17 (161). At the point in time when all the tabulation results have been transferred (180), the intermediate tabulation server 19 performs an N-way merge process between the plurality of partial tabulation results saved in the auxiliary secondary storage device (162, 181). The processing result is transferred to the inquiry processing server 5. In this method, since a random I / O to the auxiliary secondary storage device does not occur, it is possible to efficiently create a totaling result.
[0118]
However, in the present embodiment, if the tally result does not fit in the reserved area on the main memory in the input / output / tally server, a part of the partial tally result is added to the auxiliary secondary data as in the case of the tally processing server in the first embodiment. Efficient tabulation processing is performed by evacuating to the storage device, evacuating only overflowing records to the auxiliary secondary storage device, and evacuating the partial tabulation results to the auxiliary secondary storage device according to the tabulation success rate on the main memory. It is also possible.
[0119]
FIG. 19 shows an example of the classification and aggregation process in this embodiment. In this example, a case is described in which the above-described classification and aggregation processing is executed based on the present embodiment using two input / output / output servers, two intermediate aggregation servers, and one query processing server. First of all, after each of the records stored separately in the two input / output / aggregation servers (200), the aggregation process is performed on each record with the product code HT44966 to the intermediate aggregation server 1. The tally result for the record whose product code is PC15550 is distributed to the tally processing server 2 (201). Next, in each intermediate tabulation server, a reclassification and tabulation process of the partial tabulation results is performed for each tabulation result regarding the same group (202). When the respective intermediate tabulation servers have completed the classification and tabulation processing for all the distributed partial tabulation results, they transfer the merged intermediate tabulation results to the query processing server (203). The query processing server combines the transferred intermediate tabulation results to create a final tabulation result (204).
[0120]
However, in the above embodiment, each server may operate on the same physical computer, or may operate on separate computers connected via a network.
[0121]
Also, in the classifying means of the input / output server in the actual example, instead of determining the classification destination according to the value of the grouping identifier, distribution is performed so that the number of distribution records becomes equal for each aggregation server, and load distribution is performed. For example, the query processing node may again perform the classification and aggregation processing of the intermediate aggregation result to complete the entire classification and aggregation processing.
[0122]
Further, in the above-described embodiment, two or more stages of the tallying server may be connected via the network, and the classification and tallying process may be performed hierarchically for a large number of records.
[0123]
According to the present invention, the most efficient method can be adopted from the above two embodiments according to the characteristics of the data to be subjected to classification and aggregation. For example, when the number of tallying servers can be sufficiently secured, the first embodiment is adopted, and the records stored in the secondary storage device are distributed to as many tallying servers as possible, thereby completing the classification and tallying process in a short time. Can be. Further, when the amount of data of the tabulation result is sufficiently smaller than the amount of data of the record to be tabulated, by adopting the second embodiment, the amount of data transferred on the network can be reduced.
[0124]
【The invention's effect】
In the parallel database processing system according to the present invention, the classifying process and the counting process are executed in parallel in units of records, and the efficiency is improved by preventing the occurrence of random I / O when the counting result does not fit in the reserved area on the main memory. Grouping and tallying process can be performed.
[Brief description of the drawings]
FIG. 1 is a block diagram of a first embodiment of a parallel database processing system according to the present invention.
FIG. 2 is a diagram showing a logical structure of a database.
FIG. 3 is a diagram illustrating an outline of a classification and aggregation process.
FIG. 4 is a diagram showing a flowchart of a merge sort method.
FIG. 5 is a diagram showing a flowchart of a simple hash method.
FIG. 6 is a diagram illustrating an outline of a processing flow of the entire classification and aggregation processing in the first embodiment.
FIG. 7 is a diagram illustrating a flowchart of processing of the input / output server according to the first embodiment.
FIG. 8 is a diagram illustrating a flowchart of processing of a tallying server according to the first embodiment.
FIG. 9 is a diagram illustrating a flowchart of processing of an inquiry processing server.
FIG. 10 is a diagram illustrating a flowchart of processing of a terminal device.
FIG. 11 is a diagram showing a totaling result storage format based on an array method.
FIG. 12 is a diagram showing a tally result storage format based on a hash method.
FIG. 13 is a diagram showing a tally result storage format based on a combined hash / array method.
FIG. 14 is a diagram illustrating a countermeasure in a case where the aggregation result does not fit in the reservation area in the first embodiment.
FIG. 15 is a diagram illustrating a countermeasure in a case where the aggregation result does not fit in the reserved area in the second embodiment.
FIG. 16 is a diagram illustrating a flowchart of a process when a totaling result does not fit in a reserved area in the first embodiment.
FIG. 17 is a diagram illustrating a flowchart of a process when a totaling result does not fit in a reservation area in the second embodiment.
FIG. 18 is a diagram illustrating an example in which the classification and aggregation processing is performed in the first example.
FIG. 19 is a diagram illustrating an example in which a classification and aggregation process is performed in the second example.
FIG. 20 is a block diagram of a parallel database processing system according to a second embodiment of the present invention.
FIG. 21 is a diagram illustrating an outline of a processing flow of the entire classification and aggregation processing according to the second embodiment.
FIG. 22 is a diagram illustrating a flowchart of processing of the input / output / aggregation server in the second embodiment.
FIG. 23 is a diagram illustrating a flowchart of processing of an intermediate totaling server in the second embodiment.
[Explanation of symbols]
1: Secondary storage device, 2: Input / output server, 3: Network, 4: Aggregation processing server, 5: Query processing server, 6: Terminal device, 7: Partial table, 8: Data buffer, 9: Record distribution means, 10: transmission buffer, 11: reception buffer, 12: totaling processing means, 13: totaling result storage area, 14: reception buffer, 15: totaling result integration means, 16: final totaling result storage area, 17: auxiliary secondary storage device , 18: input / output / aggregation server, 19: intermediate aggregation node.

Claims (6)

A query processing server for processing a query processing request for the database, an input / output server for reading records of the database, and a tabulation server for tabulating records; and a grouping column for at least one column of the record. , And associate a group identifier with the grouping column value, classify a plurality of records with the same group identifier value into one group, and assign one or more columns of the record to the aggregation target column. A database processing system that performs a totaling process for each record classified into each group,
The input / output server has means for transferring to a tally processing server that performs tally processing of the record according to the value of the grouping column of each record,
A means for allocating a tally result storage area on a main memory provided in the tally processing server; a means for generating a group identifier from a value of a grouping column of a record received from the input / output server; A classifying unit that uniquely determines a storage location of the aggregation result corresponding to the group identifier from the value, a counting unit that updates the aggregation result stored in the storage location based on the value of the aggregation target column of the record, Means for transferring the intermediate aggregation result to the inquiry processing server,
The inquiry processing server, in the classification and aggregation processing method of the database processing system having means for integrating the intermediate aggregation results received from the aggregation processing server,
In the case where the tally result does not fit in the tally result storage area secured on the main memory of the tally processing server, a part of the tally result most recently referred to among the tally results in the tally is left and the remaining After sorting the tally result on the main memory with respect to the value of the grouping identifier corresponding to the tally result, save the result as an intermediate tally result in the auxiliary secondary storage device provided in the tally processing server,
At the end of the tallying process, by performing a merging process between the tallying result in the main memory and the zero or more sorted intermediate tallying results saved in the auxiliary secondary storage device, the respective tallying server A totaling process for the records distributed to the groups. A query processing server for processing a query processing request for the database, an input / output server for reading records of the database, and a tabulation server for tabulating records; and a grouping column for at least one column of the record. , And associate a group identifier with the grouping column value, classify a plurality of records with the same group identifier value into one group, and assign one or more columns of the record to the aggregation target column. A database processing system that performs a totaling process for each record classified into each group,
The input / output server has means for transferring to a tally processing server that performs tally processing of the record according to the value of the grouping column of each record,
A means for allocating a tally result storage area on a main memory provided in the tally processing server; a means for generating a group identifier from a value of a grouping column of a record received from the input / output server; A classifying unit that uniquely determines a storage location of the aggregation result corresponding to the group identifier from the value, a counting unit that updates the aggregation result stored in the storage location based on the value of the aggregation target column of the record, Means for transferring the intermediate aggregation result to the inquiry processing server,
The inquiry processing server, in the classification and aggregation processing method of the database processing system having means for integrating the intermediate aggregation results received from the aggregation processing server,
When the totaling result does not fit in the totaling result storage area secured on the main memory, the totaling server saves records that could not be totaled in the auxiliary secondary storage device of the totalizing server. When the ratio of records that can be counted in the main memory falls below a certain value, the counting result in the middle of counting is sorted in the main memory with respect to the value of the grouping identifier corresponding to the counting result. After that, the data is saved as an intermediate counting result in the auxiliary secondary storage device of the counting server,
At the end of the tallying process, the tallying result in the main memory, the tallying result of the zero or more records saved in the auxiliary secondary storage device, and the zero or more sorted intermediates saved in the auxiliary secondary storage device A classifying and summarizing method characterized by performing a summarizing process on records distributed to respective summarizing servers by performing a merge process with a summing result. A query processing server for processing a query processing request to the database; an input / output / total server for reading and counting the records of the database; and an intermediate counting server for counting the intermediate counting results. One or more columns are designated as grouping columns, a group identifier is made to correspond to the grouping column value, a plurality of records having the same group identifier value are classified into one group, and A database processing system that specifies one or more columns as columns to be totalized and performs a totaling process for each record classified into each group,
The input / output / aggregation server has means for allocating an aggregation result storage area on a main memory provided in the input / output / aggregation server; means for generating a group identifier from a value of a grouping column of each record; A classifying means for uniquely determining the storage location of the aggregation result corresponding to the group identifier, a counting means for updating the aggregation result stored in the storage area based on the value of the aggregation target column of the record, Means for transferring to the intermediate aggregation server that performs the integration processing of the partial aggregation result according to the value of the grouping identifier corresponding to the aggregation result,
The intermediate aggregation server has means for integrating the partial aggregation results received from the input / output / aggregation server, generating an intermediate result, and transferring the intermediate aggregation result to the inquiry processing server,
The query processing server, in the classification and aggregation processing method in the database processing system having a unit that integrates the intermediate aggregation result received from the intermediate aggregation processing server,
The input / output / aggregation server, when the aggregation result does not fit in the aggregation result storage area secured on the main memory, leaves a part of the aggregation result most recently referred to among the aggregation results in the middle of aggregation. After sorting the remaining tabulation results on the main memory with respect to the value of the grouping identifier corresponding to the tabulation result, save the result as an intermediate tabulation result in the auxiliary secondary storage device of the input / output / counting server,
At the end of the tallying process, the merging process is performed between the tallying result in the main memory and the zero or more sorted intermediate tallying results saved in the auxiliary secondary storage device, so that the respective A classifying and summarizing method, which performs a summarizing process on records distributed to a server. A query processing server for processing a query processing request for the database, an input / output / total server for reading and counting records of the database, and an intermediate counting server for counting the results of the intermediate counting; The above columns are designated as grouping columns, a group identifier is associated with the grouping column value, a plurality of records having the same group identifier value are classified into one group, and one of the records A database processing system that specifies the above columns as columns to be totaled and performs a totaling process for each record classified into each group,
The input / output / aggregation server has means for allocating an aggregation result storage area on a main memory provided in the input / output / aggregation server; means for generating a group identifier from a value of a grouping column of each record; A classifying means for uniquely determining the storage location of the aggregation result corresponding to the group identifier, a counting means for updating the aggregation result stored in the storage area based on the value of the aggregation target column of the record, Means for transferring to the intermediate aggregation server that performs the integration processing of the partial aggregation result according to the value of the grouping identifier corresponding to the aggregation result,
The intermediate aggregation server has means for integrating the partial aggregation results received from the input / output / aggregation server, generating an intermediate result, and transferring the intermediate aggregation result to the inquiry processing server,
The query processing server, in the classification and aggregation processing method in the database processing system having a unit that integrates the intermediate aggregation result received from the intermediate aggregation processing server,
The input / output / aggregation server, when the aggregation result cannot be accommodated in the aggregation result storage area secured on the main memory, overflows the record that could not be aggregated to an auxiliary secondary provided in the input / output / aggregation server. When the ratio of records that can be evacuated to the storage device and tallied on the main memory falls below a certain value, the tallied aggregation result is stored in the main storage with respect to the value of the grouping identifier corresponding to the aggregation result. After sorting on the above, save it to the auxiliary secondary storage device of the input / output / aggregation server as an intermediate aggregation result,
At the end of the tallying process, the tallying result in the main memory, the tallying result of the zero or more records saved in the auxiliary secondary storage device, and the zero or more sorted intermediates saved in the auxiliary secondary storage device A classification / aggregation processing method characterized by performing aggregation processing on records distributed to respective input / output / aggregation servers by performing merge processing with the aggregation result. A query processing server for processing a query processing request for the database, an input / output / total server for reading and counting records of the database, and an intermediate counting server for counting the results of the intermediate counting; The above columns are designated as grouping columns, a group identifier is associated with the grouping column value, a plurality of records having the same group identifier value are classified into one group, and one of the records A database processing system that specifies the above columns as columns to be totaled and performs a totaling process for each record classified into each group,
The input / output / aggregation server has means for allocating an aggregation result storage area on a main memory provided in the input / output / aggregation server; means for generating a group identifier from a value of a grouping column of each record; A classifying means for uniquely determining the storage location of the aggregation result corresponding to the group identifier, a counting means for updating the aggregation result stored in the storage area based on the value of the aggregation target column of the record, Means for transferring to the intermediate aggregation server that performs the integration processing of the partial aggregation result according to the value of the grouping identifier corresponding to the aggregation result,
The intermediate aggregation server has means for integrating the partial aggregation results received from the input / output / aggregation server, generating an intermediate result, and transferring the intermediate aggregation result to the inquiry processing server,
The query processing server, in the classification and aggregation processing method in the database processing system having a unit that integrates the intermediate aggregation result received from the intermediate aggregation processing server,
The input / output / aggregation server, when the aggregation result does not fit in the aggregation result storage area secured on the main memory, according to a corresponding rule provided in the input / output / aggregation server, the aggregation result in the middle of the aggregation on the main memory. Transferred as a partial aggregation result to the intermediate aggregation server,
In performing the classification and aggregation processing of the transferred partial aggregation results, the intermediate aggregation server, when the aggregation results cannot be stored in the aggregation result storage area secured on the main memory of the intermediate aggregation server, the aggregation is being performed. Among the aggregation results, a part of the aggregation result most recently referred to is left, and the remaining aggregation results are sorted on the main memory with respect to the grouping identifier value corresponding to the aggregation result. Saved to the secondary storage device as an interim aggregation result,
At the end of the tallying process, by performing a merge process between the tallying result in the main memory and the zero or more sorted intermediate tallying results saved in the auxiliary secondary storage device, the respective intermediate tallying servers And performing a totaling process on the partial totaling results distributed to the groups. A query processing server for processing a query processing request for the database, an input / output / total server for reading and counting records of the database, and an intermediate counting server for counting the results of the intermediate counting; The above columns are designated as grouping columns, a group identifier is associated with the grouping column value, a plurality of records having the same group identifier value are classified into one group, and one of the records A database processing system that specifies the above columns as columns to be totaled and performs a totaling process for each record classified into each group,
The input / output / aggregation server has means for allocating an aggregation result storage area on a main memory provided in the input / output / aggregation server; means for generating a group identifier from a value of a grouping column of each record; A classifying means for uniquely determining the storage location of the aggregation result corresponding to the group identifier, a counting means for updating the aggregation result stored in the storage area based on the value of the aggregation target column of the record, Means for transferring to the intermediate aggregation server that performs the integration processing of the partial aggregation result according to the value of the grouping identifier corresponding to the aggregation result,
The intermediate aggregation server has means for integrating the partial aggregation results received from the input / output / aggregation server, generating an intermediate result, and transferring the intermediate aggregation result to the inquiry processing server,
The query processing server, in the classification and aggregation processing method in the database processing system having a unit that integrates the intermediate aggregation result received from the intermediate aggregation processing server,
The input / output / aggregation server, when the aggregation result does not fit in the aggregation result storage area secured on the main memory, according to a corresponding rule provided in the input / output / aggregation server, the aggregation result in the middle of the aggregation on the main memory. Transferred as a partial aggregation result to the intermediate aggregation server,
In performing the classification and tallying process of the transferred partial tally results, the intermediate tally server overflows the tally results when the tally results no longer fit in the tally result storage area secured on the main memory of the intermediate tally server. The records that could not be collected are saved to the secondary storage device provided in the interim totaling server, and when the ratio of records that can be totaled in the main memory falls below a certain value, the totaling in the middle of the totaling is performed. After sorting the result on the main memory with respect to the value of the grouping identifier corresponding to the aggregation result, the result is saved as an intermediate aggregation result in the auxiliary secondary storage device provided in the intermediate aggregation server,
At the end of the counting process, the counting result in the main memory, the counting result of the zero or more records saved in the auxiliary secondary storage device, and the zero or more sorted intermediates saved in the auxiliary secondary storage device. A classifying and summarizing method, wherein a merging process is performed with a summarizing result to perform a summarizing process on partial summarizing results distributed to respective intermediate summarizing servers.

Images