Multi-table join query method, device, storage medium and program product

Abstract

The present disclosure relates to the technical field of database, and particularly relates to a multi-table association query method, device, storage medium and program product. The method determines the record position of the matching data of the connection field when performing a merge connection on the corresponding data table based on each group of equivalent connection relationships, instead of directly generating and transferring complete intermediate data; and subsequently, the target data is read and the final query result is generated according to the located record position, without landing, reading and writing disk temporary files throughout the process, so that the read-write amplification problem caused by multiple disk interactions is avoided from the root, the system I / O resource consumption is reduced, and the overall execution efficiency of the multi-table association query is effectively improved.

Description

Technical Field

[0001] This disclosure relates to the field of database technology, and in particular to a method, device, storage medium, and program product for multi-table join queries. Background Technology

[0002] When performing multi-table join queries in a database, it is necessary to connect and match multiple data tables based on the same key fields to integrate the related data scattered across different tables. Merge join is a commonly used join method for large-scale joint queries. It relies on comparing ordered data row by row to complete the matching. In big data scenarios, it has relatively better query efficiency and is widely used in scenarios involving continuous multi-table chain queries.

[0003] When the existing merge join is executed, after merging and matching the two tables according to the related fields and generating intermediate results, all intermediate results are stored in a temporary file on disk. When it is time to continue the join with the next data table, the data in the temporary file is read to complete the merge and matching with the next data table. This process is repeated until all tables are joined. The multiple reads and writes of intermediate results during the join process cause read / write amplification and low efficiency. Summary of the Invention

[0004] This disclosure is made in view of the above-mentioned problems. This disclosure provides a method, device, storage medium, and program product for multi-table join queries.

[0005] According to the first aspect of this disclosure, a multi-table join query method is provided, including: Receive a query request, the query request carrying multiple sets of equi-join relationships, each set of equi-join relationships is configured between two data tables to be queried, and is used to indicate that the join fields of the two data tables to be queried constitute an equi-match; Perform a merge join on the two data tables within each equi-join relationship to determine the record position of the matching data in the data tables for the join field; Read the data corresponding to the record position from the corresponding data table, and generate the final query result based on the data.

[0006] Furthermore, according to the multi-table join query method of the first aspect of this disclosure, before performing a merge join on the two data tables within each equi-join relationship, the method further includes: Divide each data table within each group of equi-join relationships into N sub-tables; The N sub-tables are sorted in parallel according to the connection fields in each group of equi-join relationships.

[0007] Furthermore, according to the multi-table join query method of the first aspect of this disclosure, before performing a merge join on the two data tables within each equi-join relationship, the method further includes: For each set of equi-join relationships, the two data tables are sorted in parallel according to the join field of each set of equi-join relationships.

[0008] Furthermore, the multi-table join query method according to the first aspect of this disclosure also includes: During the merge join operation on the two tables in the first equi-join relationship, the two tables in the second equi-join relationship are sorted according to the join field in the second equi-join relationship. Among them, the first group of equi-connection relationships and the second group of equi-connection relationships are any one of the multiple groups of equi-connection relationships.

[0009] Furthermore, according to the multi-table join query method of the first aspect of this disclosure, a merge join is performed on the two data tables within each equi-join relationship, including: Set the first table in each group of equi-join relationships as the left table and the second table as the right table, where the first table and the second table are any table in each group of equi-join relationships; When the related field in the current row of the left table is the same as the related field in the current row of the right table, record the position of the current row of the left table and the position of the current row of the right table; When the associated field in the current row of the left table is different from the associated field in the current row of the right table, update the row number of the target row with the smaller row number in the current row of the left table and the current row of the right table to the row number + 1, until the associated field in the current row of the left table is the same as the associated field in the current row of the right table, and then return to the step of recording the position of the current row of the left table and the position of the current row of the right table.

[0010] Furthermore, according to the multi-table join query method of the first aspect of this disclosure, before performing a merge join on the two data tables within each equi-join relationship, the method further includes: For all data tables within the multiple sets of equi-join relationships, sort them in parallel according to the join fields of each data table.

[0011] According to a second aspect of this disclosure, a multi-table join query apparatus is provided, comprising the following steps: The receiving module is used to receive query requests, which carry multiple sets of equi-join relationships. Each set of equi-join relationships is configured between two data tables to be queried and is used to indicate that the join fields of the two data tables to be queried constitute an equi-match. The determination module is used to perform merge joins on the two data tables within each set of equi-join relationships, and determine the record positions of the matching data in the data tables corresponding to the join field; The generation module is used to read the data corresponding to the record position from the corresponding data table and generate the final query result based on the data.

[0012] According to a third aspect of this disclosure, an electronic device is provided, including a memory, a processor, and a computer program stored in the memory, the processor executing the computer program to implement the steps of the method described in the first aspect. According to a fourth aspect of this disclosure, a computer-readable storage medium is provided that stores a computer program / instructions thereon, which, when executed by a processor, implement the steps of the method described in the first aspect. According to a fifth aspect of this disclosure, a computer program product is provided, including a computer program / instructions that, when executed by a processor, implement the steps of the method described in the first aspect.

[0013] As will be described in detail below, the multi-table join query method according to the embodiments of this disclosure determines the record position of the data matching the join field when performing a merge join on the corresponding data tables based on the equi-join relationships of each group, instead of directly generating and transferring complete intermediate data; subsequently, the target data is read directly based on the located record position and the final query result is generated, without the need to write to the disk or read and write temporary disk files throughout the process, thus avoiding the read / write amplification problem caused by multiple disk interactions from the root, reducing system I / O resource consumption, and effectively improving the overall execution efficiency of multi-table join queries.

[0014] It should be understood that both the foregoing general description and the following detailed description are exemplary and intended to provide further illustration of the claimed technology. Attached Figure Description

[0015] The above and other objects, features, and advantages of this disclosure will become more apparent from the more detailed description of the embodiments thereof in conjunction with the accompanying drawings. The drawings are provided to further illustrate the embodiments of this disclosure and form part of the specification. They are used together with the embodiments of this disclosure to explain the disclosure and do not constitute a limitation thereof. In the drawings, the same reference numerals generally represent the same components or steps.

[0016] Figure 1 This is a flowchart illustrating a multi-table association query method according to an embodiment of the present disclosure.

[0017] Figure 2 This is a schematic diagram illustrating a multi-table association query device according to an embodiment of the present disclosure.

[0018] Figure 3 This is a flowchart illustrating another method for multi-table association query according to an embodiment of the present disclosure.

[0019] Figure 4 This is another schematic diagram illustrating a multi-table association query device according to an embodiment of the present disclosure.

[0020] Figure 5 This is a hardware block diagram illustrating an electronic device according to an embodiment of the present disclosure.

[0021] Figure 6 This is a schematic diagram illustrating a computer program product according to an embodiment of the present disclosure. Detailed Implementation

[0022] The technical methods of the embodiments of the present invention will now be clearly and completely described with reference to the accompanying drawings.

[0023] To alleviate the technical problems existing in related technologies, embodiments of this disclosure provide a method, device, storage medium, and program product for multi-table join queries. This method...

[0024] To facilitate understanding of this embodiment, a multi-table join query method disclosed in this disclosure will first be described in detail. The execution subject of the multi-table join query method provided in this disclosure is generally an electronic device with a certain computing power, such as a terminal device, a server, or other processing device. In some possible implementations, the multi-table join query method can be implemented by a processor calling computer-readable instructions stored in memory.

[0025] See Figure 1 The diagram shows a flowchart of a multi-table join query method provided in this embodiment of the present disclosure. The method includes the following steps: Step 101: Receive a query request. The query request carries multiple sets of equi-join relationships. Each set of equi-join relationships is configured between two data tables to be queried and is used to indicate that the join fields of the two data tables to be queried constitute an equi-match.

[0026] In this embodiment, query requests can take various forms, including standard structured query statements, as well as structured data such as interface parameters, configuration structures, and visual configuration data. These types of data predefine the data table to be queried, the query fields, and the inter-table association rules. Among these, equi-join relationships refer to association rules established between two data tables based on the equality of corresponding field values. The general expression is Table1.FieldA = Table2.FieldB. Multiple equi-join relationships can achieve chained matching of multiple data tables based on the same value, thus satisfying chained equi-join logic.

[0027] In one example, three data tables are defined: table T1, table T2, and table T3. The query contains two sets of equi-join relationships: the first set is T1.A = T2.B, and the second set is T2.B = T3.C, which together form a chained equi-match of T1.A = T2.B = T3.C.

[0028] The corresponding structured query statement is as follows: .

[0029] Step 102: Perform a merge join on the two data tables within each equi-join relationship to determine the record position of the matching data in the data tables for the join field.

[0030] In this embodiment, the record location corresponding to the matching data includes, but is not limited to, row number, data offset address, record pointer, physical storage location, etc., which are used to uniquely identify the storage and logical location of a single record in the data table.

[0031] In one example, we continue with the chained equi-join scenario of T1.A = T2.B = T3.C: Assume the data in the data table is as follows: T1: Line number / connection field 1 1001 2 1002 T2: line number / B 1 1001 2 1003 T3: Line number / connection field 1 1001 2 1004 1. Perform a merge join on the first equijoin T1.A = T2.B, with a matching value of 1001, and locate the positions of the matching records: row 1 of T1 and row 1 of T2; 2. Perform a merge join on the second set of equijoins T2.B = T3.C. The matching value is 1001, and the positions of the matching records are located: row 1 of T2 and row 1 of T3.

[0032] If line numbers are not used, record pointers and physical offset addresses can also be used as record locations. For example, the record pointer ptr_001 with a value of 1001 in T1, the record pointer ptr_010 in T2, and the record pointer ptr_101 in T3 can be used as the positioning basis to complete the location confirmation of the matching record.

[0033] In this embodiment, performing a merge join on the two data tables within each equi-join relationship may include the following steps: Set the first table in each equijoin relationship as the left table and the second table as the right table. The first table and the second table can be any table in each equijoin relationship. If the related field in the current row of the left table is the same as the related field in the current row of the right table, record the position of the current row of the left table and the position of the current row of the right table. If the related field in the current row of the left table is different from the related field in the current row of the right table, update the row number of the target row with the smaller row number in the current row of the left table and the current row of the right table to the row number + 1, until the related field in the current row of the left table is the same as the related field in the current row of the right table, then return to the steps of executing the current row position of the left table and the current row position of the right table.

[0034] In this embodiment, the current row of the left table and the current row of the right table are both any row in the data table.

[0035] In this embodiment, the related field in the current row of the left table is different from the related field in the current row of the right table, indicating that the value of the related field in the current row of the left table is greater than or less than the value of the related field in the current row of the right table.

[0036] In one example, suppose the left table contains rows 1 (id=1), 2 (id=2), 3 (id=4), and 4 (id=5), and the right table contains rows 1 (id=2), 2 (id=3), 3 (id=4), and 4 (id=6). Both tables are sorted by id as the join field, and the initial row pointers of both tables point to row 1. First, a comparison reveals that the join field values ​​of row 1 in the left table and row 1 in the right table are not equal. Since the row number in the left table is smaller, the pointer in the left table is incremented by 1 to point to row 2. At this point, the values ​​of the related fields in the two tables are consistent, so the positions of the second row in the left table and the first row in the right table are recorded. Then, the two pointers move forward synchronously, with the left table pointing to the third row and the right table pointing to the second row. The field values ​​are no longer equal, and the row number in the right table is smaller, so the pointer in the right table is incremented by 1 to the third row. After the field values ​​match, the positions of the third row in the left table and the third row in the right table are recorded. The pointers continue to move forward to the fourth row in the left table and the fourth row in the right table. The field values ​​are no longer equal, and the row number in the left table is smaller. After incrementing the pointer, the traversal of the left table is completed, and this round of merge join ends.

[0037] In one or more alternative embodiments, before performing a merge join on the two data tables within each set of equi-join relationships, the method further includes: Divide each data table within each set of equi-join relationships into N sub-tables; Sort the N sub-tables in parallel according to the join fields in each equi-join relationship.

[0038] In this embodiment, before performing merge join, a single data table is split into N sub-tables and parallel sorting is performed based on the join field. This can make full use of multi-threaded computing power, significantly shorten the overall sorting time, and improve the overall execution efficiency of equi-join and merge join.

[0039] In this embodiment, the method of splitting the data table into N sub-tables supports flexible configuration. It can be split in an even distribution of data volume or in an unequal distribution according to the actual business scenario. All sub-tables are sorted according to a preset sorting rule (ascending or descending order). After the sorting is completed, all sub-tables are merged to obtain an overall ordered data table.

[0040] In this embodiment, parallel sorting is accomplished by a sorting thread pool, which includes two types of threads: dispatch threads and worker threads. The dispatch threads distribute the split sub-tables to different worker threads, and each worker thread independently completes the sorting process of its corresponding sub-table, thereby achieving synchronous sorting of multiple sub-tables.

[0041] In one or more alternative embodiments, before performing a merge join on the two data tables within each set of equi-join relationships, the method further includes: For each set of equi-join relationships, the two data tables are sorted in parallel according to the join field of each set of equi-join relationships.

[0042] In this embodiment, before performing a merge join on the two data tables corresponding to each set of equi-join relationships, the two data tables are pre-sorted in parallel based on the join field. This fully leverages the concurrent processing capabilities of multi-threading, synchronously completes the sorting operation of the two data tables, effectively reduces the overall preprocessing time, improves the execution efficiency of the data preparation stage, and thus optimizes the overall performance of the subsequent merge join task.

[0043] In this embodiment, separate threads can be allocated to the two data tables respectively, and sorting processes can be carried out synchronously with the help of a dual-thread concurrency mechanism.

[0044] In another implementation, the sorting of the two data tables can also be performed in a serial execution mode, that is, the sorting of the first data table and the second data table are completed in sequence. This method is simple in logic, has low resource consumption, and is suitable for operating scenarios with limited hardware computing power.

[0045] In one or more alternative embodiments, the method further includes: During the merge join operation on the two tables in the first equi-join relationship, the two tables in the second equi-join relationship are sorted according to the join field in the second equi-join relationship. Among them, the first group of equi-connection relationships and the second group of equi-connection relationships are any one of the multiple groups of equi-connection relationships.

[0046] In this embodiment, while performing a merge join on the two data tables of the first group of equi-joins, sorting is simultaneously performed on the data tables of the second group of equi-joins based on their corresponding join fields. This method executes the data processing tasks of different groups in parallel, making full use of the system's idle computing power and avoiding resource idleness caused by serial waiting of a single task, thus significantly reducing the overall total time consumption of multiple groups of equi-joins tasks. At the same time, it allows the two different operation logics of merge join and data sorting to proceed simultaneously, improving the overall throughput and running efficiency of the task, and can adapt to complex data processing scenarios with multiple table associations and multiple groups of joins coexisting.

[0047] In this embodiment, during the merge join process, the second set of data tables is sorted in parallel during the running phase after the merge join operation is initiated on the two data tables of the first group of equi-join relationships and before the entire merge join process of that group is completed.

[0048] In this embodiment, for the two data tables in the second set of equi-join relationships, parallel sorting or serial sorting modes can be flexibly selected; and when performing sorting operations on a single data table, the data table can also be partitioned to complete the data sorting in a parallel sorting manner.

[0049] In one or more alternative embodiments, before performing a merge join on the two data tables within each set of equi-join relationships, the method further includes: For all data tables within multiple equi-join relationships, perform parallel sorting based on the join fields of each data table.

[0050] In this embodiment, before the merge join is executed, all data tables of multiple equi-joins are uniformly sorted in parallel based on the join field. This can fully utilize the computing power of multi-threaded systems to synchronously complete the sorting preprocessing of massive amounts of data, significantly shortening the overall data preparation time.

[0051] Step 103: Read the data corresponding to the record position from the corresponding data table, and generate the final query result based on the data.

[0052] The multi-table join query method provided in this embodiment determines the record position of the data that matches the join field when performing merge join on the corresponding data tables based on the equi-join relationships of each group, instead of directly generating and transferring complete intermediate data. Subsequently, the target data is read directly based on the located record position to generate the final query result. The entire process does not require writing to the disk or reading and writing temporary disk files, which avoids the read / write amplification problem caused by multiple disk interactions from the root, reduces system I / O resource consumption, and effectively improves the overall execution efficiency of multi-table join queries.

[0053] This disclosure also provides a multi-table join query apparatus for executing the multi-table join query method provided in any of the above embodiments. For example... Figure 2 As shown, the device includes: Optimizer and executor.

[0054] The optimizer supports sort-merge join paths when generating execution plans, and the executor supports sort-parallel join operators.

[0055] The optimizer identifies scenarios where a sort-merge join only requires a one-time sort and generates an enhanced merge join. An enhanced merge join means that when joining two tables, matching rows are directly written to the final result set without being written to temporary results, thus reducing read / write amplification. The executor sorts the results of each table in parallel, and then performs an enhanced merge join on all tables after sorting.

[0056] The optimizer identifies scenarios where enhanced merge joins are not feasible and generates a normal sorted merge join path. The executor sorts the results of each table in parallel. After sorting, the executor performs pairwise joins in the join order, writing the matching rows generated during the join process into intermediate results. After each intermediate join, the executor sorts the intermediate results according to the new join field, and then uses the sorted intermediate results to perform a merge join with the next table; this join process is repeated until all tables are joined.

[0057] The following sections will introduce enhanced merge join and ordinary sorted merge join respectively.

[0058] Enhanced sort merge join: Enhanced sorted merge join is suitable for scenarios where the join fields belong to the same equi-chain. In this case, all intermediate results produced by the join remain ordered with respect to the join fields of the next table. That is, all tables only need to be sorted once to complete the merge join, without the need for secondary sorting. For example, the SQL statement... The intermediate results produced by "t1 JOIN t2" remain ordered in the field "t2.a". This statement is suitable for enhanced sorted merge join. Another example is the SQL statement... It also applies to enhanced merge sort joins.

[0059] Enhanced sort-merge-join execution framework such as Figure 2As shown. The optimizer identifies that all intermediate results from table joins in the SQL statement are still ordered with respect to the join fields of the next table, and generates an enhanced merge join execution plan. The executor sorts all tables in parallel according to the join fields. After all tables are sorted, the executor performs an enhanced merge join on all tables. For example, for tables where the join fields do not contain duplicate values, the following join procedure can be used, but is not limited to, to avoid generating intermediate join results: 1. Initially, set the left table as the first table and the right table as the second table.

[0060] 2. Compare the current row of the left table with the current row of the right table. If they match, proceed to step 3; otherwise, proceed to step 4.

[0061] 3. If the current comparison is of the last table (left or right table), write the current matching result into the final result set; if there are still tables that have not been compared, return to step 2 to continue execution.

[0062] 4. If the value of the join field in the left table is greater than that in the right table, move the current row of the right table down one row, and then return to step 1 to continue execution; otherwise, set the left table as the first table, move the current row of the left table down one row, and then return to step 2 to continue execution. If all the data in either table is retrieved during this process, jump to step 5 to end the join process.

[0063] 5. End.

[0064] like Figure 3 As shown, sort merge join may include the following steps: 1. After the SQL command execution module generates the sort-merge-join plan, it determines whether the execution plan is a normal Sort-Merge-Join or an enhanced Sort-Merge-Join. If it is an enhanced Sort-Merge-Join, the enhanced merge sort join operation is executed; if it is a normal merge sort join, the following join operation is executed.

[0065] 2. Instruct the parallel sorting module to sort each table based on the join field, prioritizing the first two tables.

[0066] 3. After the sorting module has sorted the first two tables, it notifies the SQL execution module that a merge join can be performed.

[0067] 4. The SQL execution module performs a normal merge join on the two sorted tables (which can also be described as two result sets, since the left table may be an intermediate result set generated by the join) and writes the join result to a temporary result set. If all tables have been joined, it jumps to step 6 to end the execution. If the temporary result set needs to be re-sorted according to subsequent fields, it notifies the parallel sorting module to sort the intermediate result set.

[0068] 5. After the temporary result set generated by the first two table joins and the next table to be joined all meet the sorting requirements, the SQL execution module jumps to step 5 to continue executing the merge join until all table joins are completed.

[0069] 6. End.

[0070] In this embodiment, the parallel sorting module is responsible for parallel sorting across multiple tables (or result sets) and parallel sorting within a single table (result set). Parallel sorting across multiple tables allows joins and sorting to be performed in parallel, improving the efficiency of Sort-Merge-join. Parallel sorting within a single table improves the efficiency of single-table sorting, thereby improving the efficiency of Sort-Merge-join.

[0071] The parallel sorting module consists of a listening thread / process and worker threads. The listening thread is responsible for listening for multiple sorting tasks sent from other threads, and then distributing these tasks from the task queue to the worker threads. After distribution, it continues listening. The worker threads, upon receiving a sorting task, perform the sorting. If the result of a single sort is large, it can perform parallel sorting on a single large result set. After sorting is complete, it notifies other threads that the sorting has ended.

[0072] This solution has the following advantages: First, in the traditional Sort-Merge-Join process, the join and sorting operations are performed sequentially, which cannot reuse computing resources and is very inefficient. For example, the execution process of t1 JOIN t2 JOIN t3 is as follows: First, table t1 is sorted according to the join field, then table t2 is sorted, then t1 and t2 are merged and joined to generate a temporary result set t1_2, then t1_2 is sorted according to the new join field, then t3 is sorted according to the new join field, and finally t1_2 and t3 are merged and joined to generate the final result.

[0073] The Sort-Merge-Join parallel execution framework proposed in this invention parallelizes merge join and sorting operations, improving execution efficiency. For example, the execution process of t1 JOIN t2 JOIN t3 is as follows: Tables t1, t2, and t3 are sorted simultaneously according to the join field. Once t1 and t2 are sorted (without waiting for t3 to finish sorting), a merge join is performed to generate a temporary result set t1_2. Then, t1_2 is sorted according to the new join field. After table t3 finishes sorting (which may have already finished sorting), a merge join is performed on t1_2 and t3 to generate the final result.

[0074] Secondly, traditional parallel sorting can only perform parallel sorting on a single table; the sorting between tables is done serially. The parallel sorting module proposed in this invention can achieve both parallel sorting between multiple tables and sorting within a single table.

[0075] For example, the existing Sort-Merge-Join execution framework cannot simultaneously sort t1, t2, and t3 according to the join field in the "t1 JOIN t2 JOIN t3" operation; it can only sort t1, t2, and t3 sequentially, resulting in very low execution efficiency. However, the Sort-Merge-Join execution framework of this invention allows simultaneous sorting of t1, t2, and t3, significantly improving execution efficiency.

[0076] Finally, traditional merge join algorithms produce intermediate result sets when joining more than two tables, leading to severe read / write amplification. Enhanced merge join algorithms, on the other hand, join all tables in one operation, producing no intermediate results and avoiding read / write amplification.

[0077] This disclosure also provides a multi-table join query apparatus for executing the multi-table join query method provided in any of the above embodiments. For example... Figure 4 As shown, the device includes: The receiving module 41 is used to receive a query request, which carries multiple sets of equi-join relationships. Each set of equi-join relationships is configured between two data tables to be queried and is used to indicate that the join fields of the two data tables to be queried constitute an equi-join match. The determination module 42 is used to perform merge join on the two data tables in each group of equi-join relationships to determine the record position of the matching data in the data tables corresponding to the join field; The generation module 43 is used to read the data corresponding to the record position from the corresponding data table and generate the final query result based on the data.

[0078] In one or more embodiments, the device is also used for: Before performing a merge join on the two data tables within each group of equi-join relationships, each data table within each group of equi-join relationships is divided into N sub-tables. The N sub-tables are sorted in parallel according to the connection fields in each group of equi-join relationships.

[0079] In one or more embodiments, the device is also used for: Before performing a merge join on the two data tables within each equi-join relationship, the two data tables within each equi-join relationship are sorted in parallel according to the join field within each equi-join relationship.

[0080] In one or more embodiments, the device is also used for: During the merge join operation on the two tables in the first equi-join relationship, the two tables in the second equi-join relationship are sorted according to the join field in the second equi-join relationship. Among them, the first group of equi-connection relationships and the second group of equi-connection relationships are any one of the multiple groups of equi-connection relationships.

[0081] In one or more embodiments, the determining module 42 is used to: Set the first table in each group of equi-join relationships as the left table and the second table as the right table, where the first table and the second table are any table in each group of equi-join relationships; When the related field in the current row of the left table is the same as the related field in the current row of the right table, record the position of the current row of the left table and the position of the current row of the right table; When the associated field in the current row of the left table is different from the associated field in the current row of the right table, update the row number of the target row with the smaller row number in the current row of the left table and the current row of the right table to the row number + 1, until the associated field in the current row of the left table is the same as the associated field in the current row of the right table, and then return to the step of recording the position of the current row of the left table and the position of the current row of the right table.

[0082] In one or more embodiments, the device is also used for: Before performing a merge join on the two tables within each equi-join relationship, For all data tables within the multiple sets of equi-join relationships, sort them in parallel according to the join fields of each data table.

[0083] The multi-table association query device and the multi-table association query method provided in this disclosure are based on the same inventive concept and have the same beneficial effects as the methods they adopt, operate or implement.

[0084] This disclosure also provides an electronic device for performing the above-described multi-table join query method. Please refer to... Figure 5 It illustrates a schematic diagram of an electronic device provided by some embodiments of this disclosure. For example... Figure 5 As shown, the electronic device 5 includes: a processor 500, a memory 501, a bus 502, and a communication interface 503. The processor 500, the communication interface 503, and the memory 501 are connected via the bus 502. The memory 501 stores a computer program that can run on the processor 500. When the processor 500 runs the computer program, it executes the multi-table association query method provided in any of the foregoing embodiments of this disclosure.

[0085] The memory 501 may include high-speed random access memory (RAM) or non-volatile memory, such as at least one disk storage device. Communication between this device network element and at least one other network element is achieved through at least one communication interface 503 (which can be wired or wireless), such as the Internet, wide area network, local area network, metropolitan area network, etc.

[0086] Bus 502 can be an ISA bus, PCI bus, or EISA bus, etc. The bus can be divided into an address bus, a data bus, a control bus, etc. The memory 501 is used to store programs. After receiving an execution instruction, the processor 500 executes the program. The multi-table association query method disclosed in any of the foregoing embodiments of this disclosure can be applied to the processor 500, or implemented by the processor 500.

[0087] The processor 500 may be an integrated circuit chip with signal processing capabilities. In implementation, each step of the above method can be completed by the integrated logic circuitry in the hardware of the processor 500 or by instructions in software form. The processor 500 may be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it may also be a digital signal processor (DSP), an application-specific integrated circuit (ASIC), an off-the-shelf programmable gate array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. It can implement or execute the methods, steps, and logic block diagrams disclosed in the embodiments of this disclosure. The general-purpose processor may be a microprocessor or any conventional processor. The steps of the methods disclosed in the embodiments of this disclosure can be directly embodied in the execution of a hardware decoding processor, or executed by a combination of hardware and software modules in the decoding processor. The software modules may reside in random access memory, flash memory, read-only memory, programmable read-only memory, electrically erasable programmable memory, registers, or other mature storage media in the art. The storage medium is located in memory 501. The processor 500 reads the information in memory 501 and, in conjunction with its hardware, completes the steps of the above method.

[0088] The electronic device provided in this disclosure and the multi-table association query method provided in this disclosure are based on the same inventive concept and have the same beneficial effects as the methods they adopt, operate or implement.

[0089] This disclosure also provides a computer-readable storage medium corresponding to the multi-table association query method provided in the foregoing embodiments. The computer-readable storage medium is an optical disc, on which a computer program (i.e., a computer program product) is stored. When the computer program is run by a processor, it executes the multi-table association query method provided in any of the foregoing embodiments.

[0090] It should be noted that examples of the computer-readable storage medium may also include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other optical and magnetic storage media, which will not be elaborated here.

[0091] The computer-readable storage medium provided in the above embodiments of this disclosure and the multi-table association query method provided in the embodiments of this disclosure are based on the same inventive concept and have the same beneficial effects as the methods adopted, run or implemented by the applications stored therein.

[0092] This disclosure also provides a computer program product; please refer to [reference needed]. Figure 6 The computer program product 600 carries program code, namely computer program 601. The instructions included in the computer program 601 can be used to execute the steps of the multi-table association query method described in the above method embodiments. For details, please refer to the above method embodiments, which will not be repeated here.

[0093] The aforementioned computer program product can be implemented through hardware, software, or a combination thereof. In one optional embodiment, the computer program product is specifically embodied in a computer storage medium; in another optional embodiment, the computer program product is specifically embodied in a software product, such as a software development kit (SDK), etc.

[0094] The basic principles of this disclosure have been described above with reference to specific embodiments. However, it should be noted that the advantages, benefits, and effects mentioned in this disclosure are merely examples and not limitations, and should not be considered as essential features of each embodiment of this disclosure. Furthermore, the specific details disclosed above are for illustrative and facilitative purposes only, and are not limitations. These details do not limit the scope of this disclosure to the necessity of employing the aforementioned specific details for implementation.

[0095] The block diagrams of devices, apparatuses, devices, and systems disclosed herein are merely illustrative examples and are not intended to require or imply that they must be connected, arranged, or configured in the manner shown in the block diagrams. As those skilled in the art will recognize, these devices, apparatuses, devices, and systems can be connected, arranged, and configured in any manner. Words such as “comprising,” “including,” “having,” etc., are open-ended terms meaning “including but not limited to,” and are used interchangeably with them. The terms “or” and “and” as used herein refer to the terms “and / or,” and are used interchangeably with them unless the context clearly indicates otherwise. The term “such as” as used herein refers to the phrase “such as but not limited to,” and is used interchangeably with it.

[0096] Additionally, as used herein, the "or" used in a list of items beginning with "at least one" indicates a separate list, such that a list of, for example, "at least one of A, B, or C" means A or B or C, or AB or AC or BC, or ABC (i.e., A and B and C). Furthermore, the word "exemplary" does not imply that the described example is preferred or better than other examples.

[0097] It should also be noted that in the systems and methods of this disclosure, the components or steps can be decomposed and / or recombined. These decompositions and / or recombinations should be considered as equivalent solutions to this disclosure.

[0098] Various changes, substitutions, and modifications can be made to the technology described herein without departing from the teachings defined by the appended claims. Furthermore, the scope of the claims of this disclosure is not limited to the specific aspects of the processes, machines, manufactures, events, means, methods, and actions described above. Currently existing or later-developed processes, machines, manufactures, events, means, methods, or actions that perform substantially the same function or achieve substantially the same result as the corresponding aspects described herein can be utilized. Therefore, the appended claims include such processes, machines, manufactures, events, means, methods, or actions within their scope.

[0099] The above description of the disclosed aspects is provided to enable any person skilled in the art to make or use this disclosure. Various modifications to these aspects will be readily apparent to those skilled in the art, and the general principles defined herein may be applied to other aspects without departing from the scope of this disclosure. Therefore, this disclosure is not intended to be limited to the aspects shown herein, but rather to be carried out within the widest scope consistent with the principles and novel features disclosed herein.

[0100] The above description has been given for purposes of illustration and description. Furthermore, this description is not intended to limit the embodiments of this disclosure to the forms disclosed herein. Although numerous exemplary aspects and embodiments have been discussed above, those skilled in the art will recognize certain variations, modifications, alterations, additions, and sub-combinations thereof.

Claims

1. A multi-table join query method, characterized in that, Includes the following steps: Receive a query request, the query request carrying multiple sets of equi-join relationships, each set of equi-join relationships is configured between two data tables to be queried, and is used to indicate that the join fields of the two data tables to be queried constitute an equi-match; Perform a merge join on the two data tables within each equi-join relationship to determine the record position of the matching data in the data tables for the join field; Read the data corresponding to the record position from the corresponding data table, and generate the final query result based on the data.

2. The method according to claim 1, characterized in that, Before performing a merge join on each of the two tables within each equi-join relationship, the following steps are also included: Divide each data table within each group of equi-join relationships into N sub-tables; The N sub-tables are sorted in parallel according to the connection fields in each group of equi-join relationships.

3. The method according to claim 1, characterized in that, Before performing a merge join on each of the two tables within each equi-join relationship, the following steps are also included: For each set of equi-join relationships, the two data tables are sorted in parallel according to the join field of each set of equi-join relationships.

4. The method according to claim 1, characterized in that, Also includes: During the merge join operation on the two tables in the first equi-join relationship, the two tables in the second equi-join relationship are sorted according to the join field in the second equi-join relationship. Among them, the first group of equi-connection relationships and the second group of equi-connection relationships are any one of the multiple groups of equi-connection relationships.

5. The method according to claim 1, characterized in that, Perform a merge join on each of the two tables within each equijoin relationship, including: Set the first table in each group of equi-join relationships as the left table and the second table as the right table, where the first table and the second table are any table in each group of equi-join relationships; When the related field in the current row of the left table is the same as the related field in the current row of the right table, record the position of the current row of the left table and the position of the current row of the right table; When the associated field in the current row of the left table is different from the associated field in the current row of the right table, update the row number of the target row with the smaller row number in the current row of the left table and the current row of the right table to the row number + 1, until the associated field in the current row of the left table is the same as the associated field in the current row of the right table, and then return to the step of recording the position of the current row of the left table and the position of the current row of the right table.

6. The method according to claim 1, characterized in that, Before performing a merge join on each of the two tables within each equi-join relationship, the following steps are also included: For all data tables within the multiple sets of equi-join relationships, sort them in parallel according to the join fields of each data table.

7. A multi-table join query device, characterized in that, Includes the following steps: The receiving module is used to receive query requests, which carry multiple sets of equi-join relationships. Each set of equi-join relationships is configured between two data tables to be queried and is used to indicate that the join fields of the two data tables to be queried constitute an equi-match. The determination module is used to perform merge joins on the two data tables within each set of equi-join relationships, and determine the record positions of the matching data in the data tables corresponding to the join field; The generation module is used to read the data corresponding to the record position from the corresponding data table and generate the final query result based on the data.

8. An electronic device comprising a memory, a processor, and a computer program stored in the memory, characterized in that, The processor executes the computer program to implement the steps of the method according to any one of claims 1-6.

9. A computer-readable storage medium having a computer program / instructions stored thereon, characterized in that, When the computer program / instructions are executed by the processor, they implement the steps of the method according to any one of claims 1-6.

10. A computer program product, comprising a computer program / instructions, characterized in that, When the computer program / instructions are executed by the processor, they implement the steps of the method according to any one of claims 1-6.

Images