JP5112137B2 - Distributed index join method and system - Google Patents

Description

  The present invention relates to a distributed index joining method and system, and more particularly to a distributed index joining system, method and program for joining indexes of relational databases in a distributed system on a network. More specifically, the present invention relates to a distributed index combination method and system in a distributed system in which indexes of a relational database are connected to each other via a network that is slower than a bus transfer speed inside a computer.

  Conventionally, as an algorithm for performing a join operation between relational databases, a nested loop join in which records of two databases are sequentially matched and a result record is generated for those satisfying the join condition or two databases are used. Are sorted by join conditions, merge join that sequentially matches from the beginning and merge, or hash that uses a hash or index to find records that satisfy the join condition by expanding records from one database into a hash table Bonding and the like are known. An example of this is shown in FIG.

  Step 10) Initialization is performed so that the pointers pointing to the records of indexes A and B start.

  Step 11) The key value of the record pointed to by the pointer of index A is transmitted to machine B.

  Step 12) The index B is searched with the key value of the record pointed to by the pointer of the index A.

  Step 13) It is determined whether or not a record satisfying the search condition exists in the index B. If there is a record, the process proceeds to Step 14, and if not, the process proceeds to Step 15.

  Step 14) The records of indexes A and B are sent to the machine J, and both records are combined and output by the machine J.

  Step 15) The index B is searched for the minimum value that exceeds the key value of the record pointed to by the pointer of the index A.

  Step 16) It is determined whether or not a record satisfying the search condition exists in the index B. If there is a record, the process proceeds to Step 17, and if not, the process ends.

  Step 17) The index B pointer is advanced to the corresponding record.

  Step 18) The key value of the record pointed to by the pointer of index B is sent to machine A.

  Step 19) The index A is searched with the key value of the record pointed to by the pointer of the index B.

  Step 20) It is determined whether or not a record satisfying the search condition exists in the index A. If there is a record, the process proceeds to Step 21, and if not, the process proceeds to Step 22.

  Step 21) The records of indexes A and B are sent to machine J, and both records are combined and output by machine J.

  Step 22) The index A is searched for the minimum value that exceeds the key value of the record pointed to by the pointer of the index B.

  Step 23) It is determined whether or not there is a record satisfying the search condition in the index A. If there is a record, the process proceeds to Step 24, and if not, the process ends.

  Step 24) The pointer of index A is advanced to the corresponding record, and the process proceeds to step 11.

It also separates join operations into separate jobs and optimally schedules them across multiple processors, thereby allowing two database databases on a common field in a parallel relational database environment in the presence of data skew. There is a technology that merges relations. This technique can minimize the total execution time for the natural combination of two relations by scheduling the current set of jobs between processors using minimum make-span optimization techniques (eg, patents). Reference 1).
JP-A-3-126169

  However, if the algorithm shown in FIG. 9 is applied to a system distributed by a network as it is, there is a problem that the number of communications increases and performance does not appear.

  In addition, the merge-joining technology using the minimum make-span optimization technique assumes a multiprocessor system, and communication between distributed systems is premised on interconnects that are faster than the network. No consideration is given to a distributed system connected by a network that is slower than the bus transfer speed of the network.

  The present invention has been made in view of the above points. In a distributed system in which indexes are connected to each other via a network that is slower than the bus transfer speed inside the computer, the merge connection between the indexes is accelerated, and communication during processing is performed. It is an object of the present invention to provide a distributed index combining system, method and program capable of reducing the number of times.

  FIG. 1 is a diagram for explaining the principle of the present invention.

The present invention (claim 1), in the distributed network system in which a plurality of processors in the network are connected to a coupling method of distributed index merging index each other via the network,
Stores an index A composed of indexes of a plurality of records, a pointer list composed of N (N ≧ 2) pointers for scanning the index A, and a record list composed of key values of sorted records corresponding to the pointers Processor A having storage means A for
An index B composed of indexes of a plurality of records, a pointer list composed of N (N ≧ 2) pointers for scanning the index B, and a record list composed of key values of sorted records corresponding to the pointers are stored. Processor B having storage means B for
In a system in which the processor A that combines the record of the index A of the processor A and the record of the index B of the processor B is connected via a network,
In processor A,
An initial record transmission step of generating N record lists A in order from the top of all indexes A and transmitting them to the processor B is performed (step 1).
In processor B,
Index search step B for searching index B from storage means B based on record list A transmitted from processor A (step 2),
A combined record transmission step B for transmitting the record of the index B and the record of the index A that satisfy the search condition by the index search step B to the processor J (step 3);
In the index B, N record lists B are generated in order from the smallest value exceeding the Nth value (maximum value in the list) of the record list A (step 5) and transmitted to the processor A (step 6). Perform record list transmission step B ,
In processor A,
Index search step A (step 7) for searching index A from storage means A based on record list B transmitted from processor B ;
A combined record transmission step A for transmitting the record of the index A and the record of the index B that satisfy the search condition in the index retrieval step A to the processor J (step 8);
In the index A, N record lists A are generated in order from the smallest value exceeding the Nth value (maximum value in the list) of the record list B (step 10) and transmitted to the processor B (step 11). Record list transmission step A is performed.

  FIG. 2 is a principle configuration diagram of the present invention.

The present invention (claim 2), on a distributed network system in which a plurality of processors in the network are connected to a coupling system distributed index merging index each other via the network,
the system,
Stores an index A composed of indexes of a plurality of records, a pointer list composed of N (N ≧ 2) pointers for scanning the index A, and a record list composed of key values of sorted records corresponding to the pointers Processor A having storage means 130A for
An index B composed of indexes of a plurality of records, a pointer list composed of N (N ≧ 2) pointers for scanning the index B, and a record list composed of key values of sorted records corresponding to the pointers are stored. Processor B having storage means 130B for
A processor J coupling a record of the index B of the record and processor B Index A processor A, a,
Processor A
Initial record transmitting means A for generating N record lists A in order from the top of all indexes A and transmitting them to the processor B ;
Based on the record list B transmitted from the processor B, the index search means 110A for searching the index A from the storage means A ;
A combined record transmission unit 141A that transmits the record of index A and the record of index B that satisfy the search condition by the index search unit 110A to the processor J;
In the index A, record list transmission means 140A for generating N record lists A in order from the smallest value exceeding the Nth value (maximum value in the list) of the record list B and transmitting it to the processor B. Have
Processor B
Based on the record list A transmitted from the processor A, the index search means 110B for searching the index B from the storage means 130B;
Combined record transmission means 141B for transmitting the record of index B and the record of index A that satisfy the search condition by the index search means B to the processor J;
In the index B, record list transmission means 140B for generating N record lists B in order from the smallest value exceeding the Nth value (maximum value in the list) of the record list A and transmitting it to the processor A. Have.

  As described above, according to the present invention, each processor has N pointers for scanning an index, and records (records) corresponding to N pointers in a pointer list unit including N pointers between processors. By transmitting (list), the processing load is reduced. This is because the processing overhead associated with starting / stopping data transmission / reception is large over a network, so even if the total amount of data to be transmitted / received is the same, the processing time can be shortened by reducing the number of times required for the exchange. The

  Hereinafter, embodiments of the present invention will be described with reference to the drawings.

  FIG. 3 shows the configuration of a distributed index combining system according to an embodiment of the present invention.

  The distributed index combination system shown in FIG. 1 includes a processor A having an index A, a processor B having an index B, and a processor J different from the processors A and B that combine the records of the processors A and B. The

  The processor A and the processor B have the same configuration, and include an input / output unit 100, an index search unit 110, a record list processing unit 120, a record list storage unit 130, and a record list communication unit 140, respectively.

  As shown in FIG. 4, the record list storage unit 130 stores a pointer list and a record list. In the following description, the “pointer” is a pointer indicating where the index is searched, and a list of a plurality of pointers is referred to as a “pointer list”. The record of the index pointed to by the pointer list is a record list. In the example of FIG. 4, the top of the pointer list points to the top record of the index, and the key value “0034” of the record is set in the record list. The second pointer list points to the nth record in the index, and the key value “0305” of the record is set in the record list.

  The processor J is a processor that supervises merge join between indexes, which is different from the processor A having the index A and the processor B having the index B, and is a processor that triggers the entire processing represented by the following flowchart. is there. The processor J includes a merge processing unit 210 and a record combining unit 220. The merge processing unit 210 is connected to the search condition input device 310, and the record combining unit 220 is connected to the combining result output device 320.

  The merge processing unit 210 outputs the search condition input from the search condition input device 310 to the processors A and B to the processors A and B.

  The record combination unit 220 combines the corresponding records of the index search results transmitted from the record list communication unit 140 of the processor A and the processor B, and outputs the combined records to the combination result output device 320. Execution of this processing indicates that there is a record having a key value common to both indexes.

  The operation in the above configuration will be described below.

  FIG. 5 is a flowchart of merge join in one embodiment of the present invention.

  Step 110) When N pointer lists indicating records are input via the input / output unit 100B in the processor B, the record list processing unit 120B stores the record list so that the pointer points to the first record of the index B. The pointer list of N indexes B of the unit 130B is initialized.

  Step 116) When N pointers pointing to the records of the index A are input via the input / output unit 100A in the processor A, the record list processing unit 120A selects N records in order from the first record of the index A. N pointers are initialized so as to point, and a list of N pointers indicating the record of the index A that has been initialized is output.

  Step 120) In the record list processing unit 120A, the N pointer lists indicating the records of the index A are read from the record list storage unit 130A, and the key value of each record of the index A indicated by the N pointer lists is acquired and the list is acquired. The record list is stored in the record list storage unit 130A as a record list and transmitted to the processor B via the record list communication unit 140A.

  Step 125) When the record list is received by the record list communication unit 140B of the processor B, the record list processing unit 120B stores the record list in the record list area of the processor A in a memory (not shown). Then, the index search unit 110B searches the index B from the record list storage unit 130B using the key values of the records in the N record lists of the index A acquired from the processor A as the search key, and points N to the record of the index B Get the pointer list. Details of this step will be described later.

  Step 130) The record list processing unit 120B acquires the key value of each record of the index B indicated by the N pointer lists indicating the acquired records of the index B, stores the key value in the record list storage unit 130B as a record list, and The data is transmitted to the processor A via the record list communication unit 140B.

  Step 135) When the record list is acquired from the processor B via the record list communication unit 140 of the processor A, the record list processing unit 120 stores the record list in a memory (not shown). The index search unit 110A searches the index A from the record list storage unit 130A using the key values of the records of the N record lists of the index B acquired as search keys, and N pointers pointing to the records of the index B Get a list. Details will be described later.

  Next, the index B search process in step 125 will be described in detail.

  FIG. 6 is a flowchart of the index search process according to the embodiment of the present invention.

  Step 210) In the record list processing unit 120B of the processor B, a loop counter j is provided in the memory (not shown) for processing all N pieces and is initialized as j = 1.

  Step 215) The record list processing unit 120B extracts a record corresponding to the counter j (jth record in the record list of the index A) from the N record lists of the index A transmitted from the processor A.

  Step 225) The index search unit 110B searches the record list storage unit 130B for the index B using the key value of the jth record in the record list of the index A extracted in step 215. When the search result is “true”, a pointer indicating the record corresponding to the search result of index B is output.

  Step 230) It is determined whether or not a record satisfying the search condition exists in the index B. If the search result in step 225 is “true”, the process proceeds to step 235, and if “false”, the process proceeds to step 265.

  Step 235) The record list processing unit 120B obtains the record corresponding to the search result of the index B indicated by the pointer obtained in Step 225 from the record list storage unit 130B, and records the record list together with the jth record in the record list of the index A. The data is transmitted to the processor J via the communication unit 140B.

  The record combining unit 220 of the processor J combines the record corresponding to the search result of the index B transmitted from the processor B and the jth record in the record list of the index A and outputs the combined result to the combined result output device 320.

  Step 265) It is confirmed whether the counter j for the loop does not exceed N. If N is not exceeded, that is, if N loops are not completed, the process proceeds to step 270. If N is exceeded, that is, N loops are completed, the process proceeds to step 240.

  Step 270) The counter j is incremented by 1, and the process proceeds to Step 215.

  Step 240) The index search unit 110B uses the key value of the Nth record in the record list of the index A extracted in step 215 by the conditional branch of step 265, and starts from the index B of the record list storage unit 130B. , “Minimum value exceeding key value” is searched. If the search result is “true”, a pointer indicating the record corresponding to the search result of index B is output.

  Step 245) In Step 240, it is confirmed whether or not a record satisfying the search condition given by the processor J exists in the index B. If it exists, the process proceeds to Step 251. If not, the process ends.

  Step 251) The record list processing unit 120B records the index B search result in the pointer list indicating the corresponding record and the N pointer list indicating the record in the index B.

  FIG. 7 is a flowchart of the index A search process according to the embodiment of the present invention.

  Step 310) In the record list processing unit 120A of the processor A, a loop counter i is provided in a memory (not shown) for processing N pieces collectively, and i = 1 is set.

  Step 315) The record list processing unit 120A extracts a record corresponding to the counter i (i-th record in the record list of the index B) from the N record lists transmitted from the processor B.

  Step 326) When the i-th record in the record list of index B is input, the index search unit 110A searches the index A using the key value of the i-th record from the record list storage unit 130A. If the search result is “true”, a pointer indicating the corresponding record in the search result of index A is output.

  Step 331) With respect to the pointer of the record output in Step 326, it is confirmed whether or not a record satisfying the search condition exists in the index A. If it exists, the process proceeds to Step 335. If not, the process proceeds to Step 365. Transition.

  Step 335) The record list processing unit 120A obtains the record corresponding to the search result of the index A pointed to by the pointer obtained in Step 325 from the record list storage unit 130, and the processor together with the i-th record in the record list of the index B. Send to J. Here, the processor J is a processor that supervises the merge join between indexes, which is different from the processor A having the index A and the processor B having the index B, and a trigger for executing the entire processing represented by this flowchart. Is a processor.

  The record combining unit 220 of the processor J combines the index A and index B records transmitted from the processor A. This processing means that there is a record having a key value common to both indexes. The record combining unit 220 of the processor J outputs the i-th divided range search result corresponding record of the index A and the i-th record among the records of the index B to the result output device 320.

  Step 365) Check if the loop counter i does not exceed N. If N is not exceeded, that is, if N loops are not completed, the process proceeds to Step 370, where N is exceeded. If N loops have been completed, the process proceeds to step 341.

  Step 370) The counter i is incremented by 1, and the process proceeds to Step 315.

  Step 341) According to the conditional branch in Step 365, the “minimum value exceeding the key value” is searched from the index A using the key value of the Nth record among the records of the index B acquired in Step 315, and the index If the search result of A is “true”, a pointer indicating the record corresponding to the search result of index A is output.

  Step 346) In step 341, it is determined whether or not a record satisfying the search condition given by the processor J exists in the index A. If it exists, the process proceeds to step 351, and if not, the process ends.

  Step 351) The record list processing unit 120A records in the N pointer lists indicating the records of the index A so as to indicate the N records in order from the corresponding record of the search result of the index A.

  FIG. 8 shows comparison data when 5 million pieces of data are merged and joined by the method according to the present invention and the method according to the conventional technique. Due to the mock-up test, there is a difference in index reading processing and the like, and the index reading processing time cannot be compared fairly, but it was confirmed that the network transfer processing time was reduced by the present invention.

  Although the present invention has been described based on the configuration of the processor shown in FIG. 3, the present invention is not limited to this example, and the operation of each processor component is constructed as a program, installed in each processor (computer), and executed. Or can be distributed via a network.

  In addition, the constructed program can be stored in a portable storage medium such as a hard disk, a flexible disk, or a CD-ROM, and can be installed or distributed in a computer.

  The present invention is not limited to the above-described embodiment, and various modifications and applications can be made within the scope of the claims.

  The present invention is applicable to DBMS (Data Base Management System) and RDBMS (Relational Data Base Management System).

It is a figure for demonstrating the principle of this invention. It is a principle block diagram of this invention. It is a block diagram of the distributed index coupling | bonding system in one embodiment of this invention. It is a figure for demonstrating the record list memory | storage part in one embodiment of this invention. It is a flowchart of the merge connection in one embodiment of this invention. It is a flowchart of the index B search process in one embodiment of this invention. It is a flowchart of the index A search process in one embodiment of this invention. It is a comparison result of the method of the present invention and the conventional method. It is a flowchart of the conventional merge join.

Explanation of symbols

100 I / O Unit 110 Index Search Unit, Index Search Unit 120 Record List Processing Unit 130 Storage Unit, Record List Storage Unit 140 Record List Transmission Unit, Record List Communication Unit 141 Combined Record Transmission Unit 210 Merge Processing Unit 220 Combination Unit, Record Combination 310 Search condition input device 320 Search result output device

Claims (2)

In a distributed network system to network multiple processors are connected to a coupling method of distributed index merging index each other via the network,
Stores an index A composed of indexes of a plurality of records, a pointer list composed of N (N ≧ 2) pointers for scanning the index A, and a record list composed of key values of sorted records corresponding to the pointers Processor A having storage means A for
An index B composed of indexes of a plurality of records, a pointer list composed of N (N ≧ 2) pointers for scanning the index B, and a record list composed of key values of sorted records corresponding to the pointers are stored. Processor B having storage means B for
In the system in which the processor A that combines the record of the index A of the processor A and the record of the index B of the processor B is connected via the network,
In the processor A,
Performing an initial record transmission step of generating N record lists A in order from the top of all indexes A and transmitting them to the processor B;
In the processor B,
Based on the record list A transmitted from the processor A, an index search step B for searching the index B from the storage means B;
A combined record transmission step B for transmitting the index B record and the index A record that satisfy the search condition in the index search step B to the processor J;
A record list transmission step B for generating N record lists B in order from the smallest value exceeding the Nth value (maximum value in the list) of the record list A in the index B and transmitting it to the processor A And
In the processor A,
Based on the record list B transmitted from the processor B, an index search step A for searching the index A from the storage means A;
A combined record transmission step A for transmitting the index A record and the index B record satisfying the search condition in the index retrieval step A to the processor J;
A record list transmission step A for generating N record lists A in order from the smallest value exceeding the Nth value (maximum value in the list) of the record list B in the index A and transmitting it to the processor B And do,
A distributed index combining method characterized by the above. The network on a distributed network system in which a plurality of processors are connected to a coupling system distributed index merging index each other via the network,
The system
Stores an index A composed of indexes of a plurality of records, a pointer list composed of N (N ≧ 2) pointers for scanning the index A, and a record list composed of key values of sorted records corresponding to the pointers Processor A having storage means A for
An index B composed of indexes of a plurality of records, a pointer list composed of N (N ≧ 2) pointers for scanning the index B, and a record list composed of key values of sorted records corresponding to the pointers are stored. Processor B having storage means B for
A processor J coupling a record of the index B of the said a record index A processor B of the processor A, a,
The processor A is
Initial record transmitting means A for generating N record lists A in order from the top of all indexes A and transmitting them to the processor B ;
Based on the record list B transmitted from the processor B, the index search means A for searching the index A from the storage means A ;
Combined record transmission means A for transmitting the record of index A and the record of index B that satisfy the search condition by the index search means A to the processor J;
Record list transmission means A for generating N record lists A in order from the smallest value exceeding the Nth value (maximum value in the list) of the record list B in the index A and transmitting it to the processor B And having
The processor B
Before SL based on the record list A transmitted from the processor A, the index search unit B to find the index B from the storage unit B,
Combined record transmission means B for transmitting the record of index B and the record of index A that satisfy the search condition by the index search means B to the processor J;
Record list transmission means B for generating N record lists B in order from the smallest value exceeding the Nth value (maximum value in the list) of the record list A in the index B and transmitting it to the processor A When,
A distributed index combining system comprising:

Images