Method for sorting duplicate values of inequality joins in a database and storage medium

Abstract

The application provides a repeated value sorting method for inequality connection in a database and a computer readable storage medium. The repeated value sorting method for inequality connection in the database comprises the following steps: receiving a query request containing multiple inequality connection conditions in a database, determining connection columns corresponding to each connection condition and a comparison operation type; setting a sorting rule according to the comparison operation type, sorting and merging left table and right table connection columns participating in connection to construct a first sorting array with a repeated value flag bit and a table identification bit; constructing a second sorting array and a permutation array based on the first sorting array, initializing a Bloom filter corresponding to a data block of a bit array; double-pointer traversing the second sorting array to match tuples satisfying the connection condition, setting corresponding positions of the bit array and the Bloom filter as valid identification values; and coarsely filtering the bit array through the Bloom filter and scanning effective data blocks, combining the table identification bit to map the matched tuples back to original tables to obtain an inequality connection result of the database.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method for sorting duplicate values ​​using inequality joins in a database and a computer-readable storage medium. Background Technology

[0002] Join operations are one of the core operations in databases. Traditional join algorithms are mainly optimized for equi-joins, such as hash joins and merge sort joins. However, in practical applications, databases often need to handle join queries that involve inequality conditions, such as range queries and time interval overlap checks. These types of queries are very common in scenarios such as financial analysis, time series data processing, and event correlation analysis. Therefore, optimizing inequality join algorithms to improve database efficiency and reduce resource consumption is of practical significance.

[0003] Currently, for two or more inequality join conditions, referred to as the first and second join conditions in the order of the constraints, an inequality join algorithm based on three lightweight data structures: sorted arrays, permuted arrays, and bit arrays is used. This algorithm mainly utilizes the transitivity of sorting and comparison, and is divided into an initialization phase, a traversal and marking phase, and a filtering and scanning phase.

[0004] However, the current inequality join algorithm has the following two main drawbacks when dealing with multiple inequality join conditions:

[0005] First, during the initialization phase, when constructing sorted array 1 based on the sorted left and right tables, no processing is performed for duplicate values. When the inequality join condition is greater than or equal to or less than or equal to and there are duplicate values, equal values ​​should be matched. However, if the sorting order is incorrect, the algorithm may miss some matches, thus outputting incorrect join results.

[0006] Second, during the filtering and scanning phase, when searching for valid bits in the bit array, the algorithm needs to scan the entire bit array from the index position to the right to find all right data table tuples that satisfy the second join condition. However, the bit array may be very sparse, meaning that most bits are 0, and scanning bit by bit would waste a lot of time on blank areas. Summary of the Invention

[0007] One object of the present invention is to provide a method for sorting duplicate values ​​in inequality joins in a database that can solve any of the above problems.

[0008] A further objective of this invention is to address the problem that existing algorithms, when processing multiple inequality joins, result in incorrect join outcomes due to the failure to handle duplicate values.

[0009] Another further objective of this invention is to address the problem of low efficiency and resource waste caused by scanning the sparse bit array bit by bit in the screening and scanning stage of existing algorithms.

[0010] Specifically, this invention provides a method for sorting duplicate values ​​in inequality joins in a database, comprising: receiving a query request from a database containing multiple inequality join conditions; determining the join columns and comparison operation types corresponding to each join condition; setting sorting rules according to the comparison operation types; sorting and merging the join columns of the left and right tables involved in the join; constructing a first sorting array with a duplicate value flag and a table identifier; constructing a second sorting array and a permutation array based on the first sorting array; initializing a Bloom filter corresponding to the bit array data blocks; traversing the second sorting array with two pointers to match tuples that satisfy the join conditions; setting the corresponding positions of the bit array and Bloom filter to valid identifier values; and coarsely filtering the bit array through the Bloom filter and scanning the valid data blocks, mapping the matched tuples back to the original table in conjunction with the table identifier to obtain the database inequality join result.

[0011] Optionally, the multiple inequality join condition consists of two inequality join conditions, including a first join condition and a second join condition, and the step of setting the sorting rules according to the comparison operation type includes: for the join column of the first join condition, if the comparison operation type is greater than or greater than or equal to, sort in descending order; if it is less than or less than or equal to, sort in ascending order; for the join column of the second join condition, if the comparison operation type is greater than or greater than or equal to, sort in ascending order; if it is less than or less than or equal to, sort in descending order.

[0012] Optionally, the duplicate value flag is used to distinguish between data in the left and right tables. It takes a Boolean value, where true indicates that the tuple comes from the left table and false indicates that the tuple comes from the right table. The table identifier is used to map the tuple to the position in the original table. Positive values ​​map to the position in the left table and negative values ​​map to the position in the right table.

[0013] Optionally, the structure of the first sorted array is [first join column value, second join column value, table identifier, duplicate value flag]; when constructing the first sorted array, duplicate values ​​are sorted according to the following rules: if the first join condition is a non-strict inequality, the duplicate values ​​of the left table are placed before the duplicate values ​​of the right table; if it is a strict inequality, the duplicate values ​​of the left table are placed after the duplicate values ​​of the right table.

[0014] Optionally, the steps of constructing a second sorted array and a permutation array based on the first sorted array include: extracting the connection column corresponding to the second connection condition from the first sorted array, constructing the second sorted array according to the sorting rules of the second connection condition, and the permutation array recording the index position of the second sorted array tuple in the first sorted array. The structure of the second sorted array is [second connection column value, permutation array].

[0015] Optionally, the step of initializing the Bloom filter corresponding to the bit array data block includes: initializing a bit array with the same length as the first sorted array, initializing all bits to invalid flag values; and dividing the first sorted array into blocks according to a preset block size, initializing a Bloom filter with the same number of bits as the number of blocks, initializing all bits to invalid flag values, and each bit of the Bloom filter corresponding to a data block of the bit array is used to coarsely filter the blank areas of the bit array.

[0016] Optionally, the step of using two pointers to traverse the second sorted array to match tuples that satisfy the join condition includes: initializing both the first and second pointers to traverse from the beginning of the second sorted array, with the first pointer dedicated to processing data in the left table and the second pointer dedicated to processing data in the right table, and always ensuring that the traversal position of the second pointer does not exceed that of the first pointer; and after moving the first pointer to find data in the left table, moving the second pointer to find data in the right table, and when a tuple that satisfies the second join condition is matched, locating the corresponding position in the first sorted array through the permutation array, and setting the corresponding positions in the bit array and the Bloom filter to valid flag values.

[0017] Optionally, the step of coarsely filtering the bit array using a Bloom filter and scanning valid data blocks includes: locating the position of the left table tuple in the first sorted array by arranging the array, and determining its corresponding block position in the bit array; checking the bit corresponding to the block position in the Bloom filter, skipping the data block if it is an invalid identifier value, and scanning the bit array to the right from the starting position of the data block if it is a valid identifier value; and when a valid identifier value is scanned, determining that the corresponding right table tuple and the left table tuple satisfy the multiple inequality join condition, and mapping the matching tuple back to the left and right tables of the original table through the table identifier bit.

[0018] Optionally, a valid identifier value is a non-zero integer, and an invalid identifier value is 0.

[0019] According to another aspect of the present invention, a computer-readable storage medium is also provided, on which a computer program is stored, wherein the computer program, when executed by a processor, implements the steps of the above-described database inequality join duplicate value sorting method.

[0020] The duplicate value sorting method for inequality joins in the database of this invention effectively solves the problem of missing or incorrect matching caused by improper sorting order when existing inequality join algorithms process duplicate values ​​by adding a duplicate value flag bit to the first sorting array and formulating strict duplicate value sorting rules. For non-strict and strict inequality join scenarios, sorting logic of duplicate values ​​in the left table first and then last is set respectively to ensure accurate matching of join conditions in duplicate value scenarios, so that the algorithm can correctly process multiple inequality join queries containing duplicate values, and ensure the accuracy and completeness of the join results.

[0021] Furthermore, the duplicate value sorting method for inequality joins in the database of the present invention introduces a Bloom filter in conjunction with a bit array to perform coarse filtering of bit arrays during the filtering and scanning phase. The Bloom filter quickly determines whether there are valid matching tuples in the corresponding data blocks of the bit array, directly skipping blank data blocks without valid bits. This avoids full bit-by-bit scanning of sparse bit arrays, significantly reduces invalid scanning operations, significantly improves the filtering and scanning efficiency of the inequality join algorithm, reduces the system's computational resource consumption, and effectively optimizes the overall processing performance of multi-inequality join queries.

[0022] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0023] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings:

[0024] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings:

[0025] Figure 1 This is a schematic diagram of an inequality join algorithm in the prior art;

[0026] Figure 2 This is a schematic flowchart of a method for sorting duplicate values ​​using inequality joins in a database according to an embodiment of the present invention.

[0027] Figure 3 This is a detailed flowchart of a method for sorting duplicate values ​​using inequality joins in a database according to an embodiment of the present invention;

[0028] Figure 4 This is a schematic diagram of the sorted left and right tables in a database inequality join duplicate value sorting method according to an embodiment of the present invention;

[0029] Figure 5 This is a schematic diagram of the first sorting array H1 in a database inequality join method for sorting duplicate values ​​according to an embodiment of the present invention;

[0030] Figure 6 This is a schematic diagram of the second sorting array L2 in a database inequality join repeated value sorting method according to an embodiment of the present invention;

[0031] Figure 7 This is a schematic diagram of a Bloom filter and bit array B in a duplicate value sorting method for inequality joins in a database according to an embodiment of the present invention.

[0032] Figure 8 This is a schematic diagram of a computer program product according to an embodiment of the present invention;

[0033] Figure 9 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and

[0034] Figure 10 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0035] Those skilled in the art should understand that the embodiments described below are merely a part of the embodiments of the present invention, and not all of the embodiments of the present invention. These partial embodiments are intended to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by those skilled in the art without creative effort should still fall within the scope of protection of the present invention.

[0036] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-included system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0037] Figure 1 This is a schematic diagram of an inequality connection algorithm in the prior art. Figure 1 The execution flow of the existing multiple inequality join algorithm is shown, which is divided into three core stages: initialization stage, traversal and marking stage, and scanning and filtering stage.

[0038] During the initialization phase, using the sorted left and right tables as input, sorted array 1 and sorted array 2 are constructed sequentially to complete data preprocessing and structure alignment, preparing for subsequent matching. Sorted array 1 is an ordered table formed by merging the data in the first join column of the left and right tables, sorting it according to the corresponding sorting rules, adding a duplicate value flag column, and the permutation array rid. The duplicate value flag is used to determine the data's ownership; different join conditions have different sorting rules. The permutation array rid is used to map elements in sorted array 1 back to the original sorted left and right tables.

[0039] During the traversal and marking phase, a two-pointer strategy is used to traverse the sorted array 2 to find data that meets the join condition, and the matching positions are marked in the bit array to record the position information of valid matches. Sorted array 2 refers to the ordered list formed by taking the data from the second join column of the right table in sorted array 1 and adding permutation array P. Permutation array P is used to map the elements in sorted array 2 back to sorted array 1. The bit array marks the right table data in sorted array 1 that satisfies the second join condition.

[0040] During the scanning and filtering phase, valid bits are searched for bit by bit in the bit array. If no valid bits are found, the process returns to the traversal phase to continue matching. The final connection result is output after all data has been processed.

[0041] As mentioned earlier, Figure 1 The core flaws of the existing inequality join algorithm are as follows: First, during the initialization phase, when constructing sorted array 1 based on the sorted left and right tables, duplicate values ​​are not handled. When the inequality join condition is greater than or equal to or less than or equal to and duplicate values ​​exist, equal values ​​should be matched. However, if the sorting order is incorrect, the algorithm may miss some matches, resulting in an incorrect join result. Second, during the filtering and scanning phase, when searching for valid bits in the bit array, the algorithm needs to scan the entire bit array from the index position to the right to find all right table tuples that satisfy the second join condition. However, the bit array may be very sparse, meaning most bits are 0, and scanning bit by bit wastes a significant amount of time on blank areas.

[0042] This embodiment provides a method for sorting duplicate values ​​in inequality joins in a database. By introducing a duplicate value flag and targeted sorting rules, it accurately handles the duplicate value problem in multiple inequality join scenarios, avoiding matching omissions or errors, and ensuring the accuracy and completeness of the join results. Combined with a Bloom filter to implement coarse filtering of the bit array, it skips blank data blocks without valid matches, greatly reduces invalid scan operations, significantly improves the execution efficiency of multiple inequality join queries, and reduces system resource consumption. Figure 2 This is a schematic flowchart of a method for sorting duplicate values ​​using inequality joins in a database according to an embodiment of the present invention, as shown below. Figure 2 As shown, the method for sorting duplicate values ​​using inequality joins in a database generally includes the following steps:

[0043] Step S202: Receive a query request from the database containing multiple inequality join conditions, and determine the join columns and comparison operation types corresponding to each join condition.

[0044] Step S204: Set the sorting rules according to the comparison operation type, sort and merge the left and right table join columns involved in the join, and construct the first sorted array with duplicate value flag and table identifier.

[0045] Step S206: Construct a second sorting array and a permutation array based on the first sorting array, and initialize the Bloom filter corresponding to the bit array data block.

[0046] Step S208: Use two pointers to traverse the second sorted array to match tuples that meet the join condition, and set the corresponding positions of the bit array and Bloom filter to valid identifier values.

[0047] Step S210: The array of bits is coarsely filtered by a Bloom filter and the valid data blocks are scanned. The matching tuples are mapped back to the original table by combining the table identifier bits to obtain the database inequality join result.

[0048] Specifically, the multiple inequality join condition in step S202 consists of two inequality join conditions, including a first join condition and a second join condition. Furthermore, the sorting rule set according to the comparison operation type in step S204 may include: for the join columns of the first join condition, sorting in descending order if the comparison operation type is greater than or equal to, and sorting in ascending order if it is less than or equal to; for the join columns of the second join condition, sorting in ascending order if the comparison operation type is greater than or equal to, and sorting in descending order if it is less than or equal to.

[0049] In step S204, the duplicate value flag is used to distinguish between data in the left and right tables. It takes a Boolean value, where true indicates that the tuple comes from the left table and false indicates that the tuple comes from the right table. The table identifier is used to map the tuple to the position in the original table. Positive values ​​map to the position in the left table and negative values ​​map to the position in the right table.

[0050] In step S204, the structure of the first sorted array is [first join column value, second join column value, table identifier, duplicate value flag]. When constructing the first sorted array, duplicate values ​​are sorted according to the following rules: if the first join condition is a non-strict inequality, duplicate values ​​from the left table are placed before those from the right table; if it is a strict inequality, duplicate values ​​from the left table are placed after those from the right table. The first join column value refers to the specific numerical value extracted from the first join column of the database table, and the second join column value is similarly calculated.

[0051] Specifically, step S206, which involves constructing a second sorted array and a permutation array based on a first sorted array, may include: extracting the connection column corresponding to the second connection condition from the first sorted array, constructing a second sorted array according to the sorting rules of the second connection condition, and the permutation array recording the index position of the second sorted array tuple in the first sorted array. The structure of the second sorted array is [second connection column value, permutation array].

[0052] Specifically, the initialization of the Bloom filter corresponding to the bit array data block in step S206 may include: initializing a bit array with the same length as the first sorted array, initializing all bits to invalid flag values; and dividing the first sorted array into blocks according to a preset block size, initializing a Bloom filter with the same number of bits as the number of blocks, initializing all bits to invalid flag values, with each bit of the Bloom filter corresponding to a data block of the bit array, used for coarse filtering of blank areas in the bit array. A Bloom filter is a coarse filter that can quickly filter blank areas in a bit array.

[0053] Specifically, step S208, which involves using two pointers to traverse the second sorted array to match tuples that meet the join condition, may include: initializing both the first and second pointers to traverse from the beginning of the second sorted array, with the first pointer dedicated to processing data in the left table and the second pointer dedicated to processing data in the right table, while always ensuring that the traversal position of the second pointer does not exceed that of the first pointer; and after moving the first pointer to find data in the left table, moving the second pointer to find data in the right table, and when a tuple that meets the second join condition is matched, locating the corresponding position in the first sorted array using the permutation array, and setting the corresponding positions in the bit array and the Bloom filter to valid identifier values.

[0054] Specifically, step S210, which involves coarsely filtering the bit array using a Bloom filter and scanning the valid data blocks, may include: locating the position of the left table tuple in the first sorted array by arranging the array, and determining its corresponding block position in the bit array; checking the bit corresponding to the block position in the Bloom filter, skipping the data block if it is an invalid identifier value, and scanning the bit array to the right from the start of the data block if it is a valid identifier value; and when a valid identifier value is found, determining that the corresponding right table tuple and the left table tuple satisfy the multiple inequality join condition, and mapping the matching tuple back to the left and right tables of the original table using the table identifier bit.

[0055] Specifically, a valid identifier value is a non-zero integer, and an invalid identifier value is 0. Preferably, a valid identifier value can be 1.

[0056] The following describes a specific embodiment:

[0057] The data table used is the employees table. Table 1 contains the employee's ID, salary, and tax.

[0058] Table 1

[0059] id salary tax 1 5000 50 2 5500 60 3 6000 55 4 5000 52 5 7000 65 6 5000 48

[0060] Use a self-join to find salary anomalies, i.e., lower salaries but higher taxes.

[0061] SELECT e1.id, e2.id

[0062] FROM

[0063] employees e1, employees e2

[0064] WHERE

[0065] e1.salary ≤ e2.salary

[0066] AND

[0067] e1.tax > e2.tax;

[0068] This can be expressed in a more general form:

[0069] SELECT t1.a, t2.b

[0070] FROM

[0071] T t1 ,T't2

[0072] WHERE

[0073] t1.X op1 t2.X'

[0074] AND

[0075] t1.Y op2 t2.Y';

[0076] Where T is the left table, T' is the right table, op1 is the first join condition, and op2 is the second join condition. X / X' is the first join column value, and Y / Y' is the second join column value.

[0077] The duplicate value sorting method of the database inequality join in this embodiment improves the structure of the first sorting array by adding a duplicate value flag to handle duplicate values ​​and introducing a Bloom filter to perform a coarse filter in the screening and scanning stage to quickly filter out blank areas. Figure 3 This is a detailed flowchart of a method for sorting duplicate values ​​using inequality joins in a database according to an embodiment of the present invention. Figure 4 This is a schematic diagram of the sorted left and right tables in a database inequality join duplicate value sorting method according to an embodiment of the present invention. Figure 5 This is a schematic diagram of the first sorted array H1 in a database inequality join method for sorting duplicate values ​​according to an embodiment of the present invention. Figure 6 This is a schematic diagram of the second sorting array L2 in a database inequality join method for sorting duplicate values ​​according to an embodiment of the present invention. Figure 7 This is a schematic diagram of a Bloom filter and bit array B in a database inequality join duplicate value sorting method according to an embodiment of the present invention. Figure 3The method for sorting duplicate values ​​using inequality joins in a database can be divided into three phases: an initialization phase, a traversal and marking phase, and a scan and filtering phase.

[0078] During the initialization phase, the sorted array, permutation array, Bloom filter, and bit array are prepared. Specifically,

[0079] First, determine the sorting rules. For the first joined column, sort in descending order if op1 ∈ {>, ≥}, and in ascending order if op1 ∈ {<, ≤}. For the second joined column, sort in ascending order if op2 ∈ {>, ≥}, and in descending order if op2 ∈ {<, ≤}.

[0080] Second, sort the left and right tables according to the sorting rules of the first join condition. Here, the first join condition is "less than or equal to," so sort the salary column in ascending order, as follows: Figure 4 As shown. Then, from the pre-sorted left and right tables, extract the join columns corresponding to the two join conditions, and perform a cross-table join to ensure that all tuples involved in the join are included in the processing scope.

[0081] Third, sort the merged table according to the sorting rules of the first join condition. Since the first join condition is less than or equal to, the salary column is sorted in ascending order. A duplicate value flag (FL) is introduced to specify the position of duplicate values. Because this is a non-strict inequality join, data from the left table is placed before data from the right table, where true indicates data from the left table and false indicates data from the right table. A table identifier (rid) is also introduced to map the position of tuples to the original sorted table; positive values ​​represent the left table, and negative values ​​represent the right table. The first sorted array H1 is constructed as follows: Figure 5 As shown, the final first sorted array H1 has the array structure [X / X', Y / Y', rid, FL]. Here, X / X' is the first join column value, Y / Y' is the second join column value, rid is the table identifier, and FL is the duplicate value flag.

[0082] Fourth, extract the join column for the second join condition from H1, and sort it according to the sorting rules of the second join condition. Here, the second join condition is greater than, so the `tax` column is sorted in ascending order to form the second sorted array L2. Simultaneously, calculate the permutation array P, where each element records the index position of the tuple in L2 within H1. Figure 6 As shown, the final second sorted array L2 has the array structure [Y / Y', P]. Here, P is the permutation array.

[0083] Fifth, initialize the bit array B based on the length of H1, setting all positions to 0. This is used to mark whether the right table tuples in H1 satisfy the second connection condition. Simultaneously, initialize the Bloom filter, dividing H1 into several blocks. Each bit of the Bloom filter corresponds to a block in H1 and is initialized to 0. This means each bit is responsible for judging non-zero bits within a certain range in the bit array B, such as... Figure 7 As shown, in this example, one bit of the Bloom filter is responsible for judging four bits in the bit array B.

[0084] During the traversal and marking phase, two pointers are used for positioning, and a bit array and Bloom filter are used for marking. Specifically, firstly, the first pointer i and the second pointer j are initialized, both starting from the beginning of L2. The first pointer i is dedicated to processing data in the left table, and the second pointer j is dedicated to processing data in the right table. The second pointer j always tracks the position of the first pointer i, i.e., j ≤ i.

[0085] Second, first move the first pointer i to find data in the left table, then move the second pointer j to find data in the right table. When L2[i] and L2[j] meet the second connection condition, locate the position of the right table tuple L2[j] in H1 using the permutation array P[j], and set the corresponding positions of the bit array B and the Bloom filter to 1. If the second pointer j reaches the position of the first pointer i and still does not find a tuple that meets the condition, then continue moving the first pointer i to the next position in the left table, and the second pointer j continues to track until the sorted array L2 is processed.

[0086] During the scanning and filtering phase, a Bloom filter performs coarse filtering, while a bit array performs fine filtering. Specifically, firstly, for tuples L2[i] and L2[j] that meet the second connection condition, after marking the position of L2[j] in H1 using P[j], the permutation array P[i] is used to locate the position index of the left table tuple in H1. The position of the block is determined first, and the corresponding bit in the Bloom filter is checked. Then, the scan proceeds to the right. If the result is 0, it means that the bit array B of the block is all 0, indicating that there is no right table tuple that meets the condition in this block, and the scan of the block is skipped directly. If the result is 1, the block is entered for fine scanning.

[0087] Second, during the fine scan of the bit array B, the scan proceeds from the beginning of the block to the right. If there is a valid bit 1, it means that the current valid bit satisfies the inequality connection condition with the left table tuple H1[P[i]]. The left and right tables are then mapped back to the original sorted left and right tables through the rid column in H1.

[0088] It is important to emphasize that the duplicate value sorting method using inequality joins in this embodiment employs a novel sorting array H1. This array includes an additional column of duplicate value flags to handle cases where duplicate values ​​exist in both tables. The purpose is to ensure that when value[i] and value[j] satisfy the first join condition, i from the left table is less than j from the right table, thus preventing data omissions during the scanning and filtering phase. Specifically, duplicate values ​​in H1 are sorted according to the following sorting rules:

[0089] When the first join condition is a non-strict inequality, duplicate values ​​from the left table are placed before those from the right table. This is done by using a duplicate value flag (FL), where true indicates the left table and false indicates the right table, thus preventing the right table from appearing first due to random sorting and causing lost matching results. When the first join condition is a strict inequality, duplicate values ​​from the left table are placed after those from the right table, thus preventing duplicate matches.

[0090] The duplicate value sorting method of inequality join in this embodiment of the database designs a coarse filter, namely the Bloom filter, which maps all the data in a block of size 1024 in the bit array to its own bit. In the scanning and filtering stage, the Bloom filter is scanned first. By using the 0 or 1 of the bit, it can be determined more quickly whether there are matching tuples in the block range, thereby increasing the scanning efficiency of the algorithm.

[0091] After applying the duplicate value flag in the database inequality join sorting method of this embodiment, the inequality join algorithm can correctly handle all cases, including those containing duplicate values. For example:

[0092] For example, if the data in the left table is L[1,1,2] and the data in the right table is R[1,1,3], the first join condition is less than or equal to.

[0093] If a duplicate value flag is not added when constructing the first sorted array, the result may be:

[0094] 1 1 1 1 2 3 R L R L L R

[0095] If, after the traversal and marking phases, the resulting bit array B is as follows:

[0096] In the subsequent scanning and filtering stage, the first digit of the digit group will never be scanned, and the matching result will therefore be incorrect.

[0097] Similarly, when the first join condition is less than, if the result of random sorting is:

[0098] 1 1 1 1 2 3 L R L R L R

[0099] After the traversal and marking phases, the resulting bit array B is shown below:

[0100] In the subsequent scanning and filtering phase, incorrect matching results may be encountered. If the scan starts from index 2, 1 (L) and 1 (R) will be matched, but this does not meet the first connection condition of less than.

[0101] The duplicate value flag sorting rule designed according to the duplicate value sorting method of the database inequality join in this embodiment can always obtain the correct order. When the first join condition is less than or equal to, the resulting first sorted array is:

[0102] 1 1 1 1 2 3 L L R R L R

[0103] The resulting bit array B is:

[0104] This also satisfies the condition that when value[i] and value[j] satisfy the first join condition, i from the left table can be less than j from the right table.

[0105] Furthermore, the Bloom filter designed in the database inequality join duplicate value sorting method of this embodiment can quickly filter out blank areas of bit arrays, avoiding scanning large blocks of invalid bits in the bit array and improving scanning efficiency.

[0106] In summary, the duplicate value sorting method for inequality joins in this embodiment effectively solves the problem of missed or incorrect matching caused by improper sorting order when processing duplicate values ​​in existing inequality join algorithms by adding a duplicate value flag in the first sorting array and formulating strict duplicate value sorting rules. For non-strict and strict inequality join scenarios, sorting logic is set to prioritize duplicate values ​​in the left table, ensuring accurate matching of join conditions in duplicate value scenarios. This allows the algorithm to correctly handle multi-inequality join queries containing duplicate values, ensuring the accuracy and completeness of the join results.

[0107] Furthermore, the duplicate value sorting method for inequality joins in this embodiment introduces a Bloom filter in conjunction with a bit array. During the filtering and scanning phase, it performs coarse filtering of bit arrays. The Bloom filter quickly determines whether there are valid matching tuples in the corresponding data blocks of the bit array, directly skipping blank data blocks without valid bits. This avoids full bit-by-bit scanning of sparse bit arrays, significantly reduces invalid scanning operations, significantly improves the filtering and scanning efficiency of the inequality join algorithm, reduces the system's computational resource consumption, and effectively optimizes the overall processing performance of multi-inequality join queries.

[0108] The flowcharts provided in the above embodiments are not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in all every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the methods in the above embodiments, additional variations can be made to the above methods.

[0109] This embodiment also provides a computer program product, a computer-readable storage medium, and a computer device. Figure 8 This is a schematic diagram of a computer program product 500 according to an embodiment of the present invention. Figure 9 This is a schematic diagram of a computer-readable storage medium 300 according to an embodiment of the present invention. Figure 10 This is a schematic diagram of a computer device 400 according to an embodiment of the present invention.

[0110] Computer program product 500 includes computer program 310, which, when executed by processor 410, implements the steps of the database inequality join duplicate value sorting method described above. Computer-readable storage medium 300 stores the computer program 310 thereon, which, when executed by processor 410, implements the steps of the database inequality join duplicate value sorting method described above. Computer device 400 may include memory 420, processor 410, and computer program 310 stored in memory 420 and running on processor 410.

[0111] The computer program 310 used to perform the operations of the present invention may be assembly instructions, instruction set architecture (ISA) instructions, machine instructions, machine-related instructions, microcode, firmware instructions, status setting data, integrated circuit configuration data, or source code or object code written in any combination of one or more programming languages ​​and procedural programming languages.

[0112] Computer program 310 may execute entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0113] In some embodiments, in order to perform aspects of the present invention, electronic circuits including, for example, programmable logic circuits, field-programmable gate arrays (FPGAs) or programmable logic arrays (PLAs) can execute computer-readable program instructions to personalize the electronic circuits by utilizing state information of computer-readable program instructions.

[0114] For the purposes of this embodiment, computer program product 500 is a related product containing computer program 310. For the purposes of this embodiment, computer-readable storage medium 300 is a tangible device capable of holding and storing computer program 310, and can be any device capable of containing, storing, communicating, propagating or transmitting computer program 310 for use by or in conjunction with an instruction execution system, apparatus or device.

[0115] More specific examples (a non-exhaustive list) of computer-readable storage media 300 include the following: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable optical disc read-only memory (CD-ROM), digital multifunction disc (DVD), memory sticks, floppy disks, mechanical encoding devices, and any suitable combination of the foregoing.

[0116] Computer device 400 may include memory 420, processor 410, and computer program 310 stored on memory 420 and running on processor 410. When processor 410 executes computer program 310, it implements the steps of the database inequality join duplicate value sorting method of any of the above embodiments.

[0117] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any machine-readable storage medium for use by, or in conjunction with, instruction execution systems, apparatuses or devices (such as computer-based systems, processor-based systems or other systems that can fetch and execute instructions from, or instruction execution systems, apparatuses or devices).

[0118] It should be understood that various parts of the present invention can be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0119] Computer device 400 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 400 can be a cloud computing node. Computer device 400 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, program modules can include routines, programs, object programs, components, logic, data structures, etc., that perform specific tasks or implement specific abstract data types. Computer device 400 can be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices linked through a communication network. In a distributed cloud computing environment, program modules can reside on local or remote computing system storage media, including storage devices.

[0120] Computer device 400 may include a processor 410 adapted to execute stored instructions and a memory 420 that provides temporary storage space for the operation of instructions during operation. The processor 410 may be a single-core processor, a multi-core processor, a computing cluster, or any other configuration. The memory 420 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0121] The processor 410 can be connected via a system interconnect (e.g., PCI, PCI-Express, etc.) to an I / O interface (input / output interface) suitable for connecting the computer device 400 to one or more I / O devices (input / output devices). I / O devices may include, for example, a keyboard and indicating devices, where indicating devices may include a touchpad or touchscreen, etc. I / O devices may be built into the computer device 400 or may be external devices connected to the computing device.

[0122] The processor 410 may also be linked via a system interconnect to a display interface suitable for connecting the computer device 400 to a display device. The display device may include a display screen as a built-in component of the computer device 400. The display device may also include an external computer monitor, television, or projector connected to the computer device 400. Furthermore, a network interface controller (NIC) may be adapted to connect the computer device 400 to a network via a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an Internet Minicomputer System Interface) to transmit data. The network may be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, etc. Remote devices may connect to the computing device via the network.

[0123] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. A method for sorting duplicate values ​​using inequality joins in a database, comprising: Receive a query request from the database containing multiple inequality join conditions, and determine the join columns and comparison operation types corresponding to each join condition; Based on the comparison operation type, the sorting rules are set, and the join columns of the left and right tables involved in the join are sorted and merged to construct a first sorted array with duplicate value flags and table identifiers. Construct a second sorted array and a permutation array based on the first sorted array, and initialize the Bloom filter corresponding to the bit array data block; Two pointers traverse the second sorted array to match tuples that satisfy the connection condition, and set the corresponding positions of the bit array and Bloom filter to valid identifier values; as well as The bit array is coarsely filtered by the Bloom filter and valid data blocks are scanned. The matching tuples are then mapped back to the original table using the table identifier bit to obtain the database inequality join result.

2. The method according to claim 1, wherein, The multiple inequality connection condition consists of two inequality connection conditions, including a first connection condition and a second connection condition, and The steps for setting sorting rules based on the comparison operation type include: For the join columns of the first join condition, if the comparison operation type is greater than or greater than or equal to, they are sorted in descending order; if it is less than or less than or equal to, they are sorted in ascending order. For the columns to be joined under the second join condition, if the comparison operation type is greater than or greater than or equal to, they are sorted in ascending order; if it is less than or less than or equal to, they are sorted in descending order.

3. The method according to claim 1, wherein, The duplicate value flag is used to distinguish between data from the left and right tables. It takes a Boolean value: true indicates that the tuple comes from the left table, and false indicates that the tuple comes from the right table. The table identifier is used to map tuples to their positions in the original table; positive values ​​map to the left table position, and negative values ​​map to the right table position.

4. The method according to claim 2, wherein, The structure of the first sorted array is [first join column value, second join column value, table identifier bit, duplicate value flag bit]; When constructing the first sorted array, duplicate values ​​are sorted according to the following rules: if the first join condition is a non-strict inequality, the duplicate values ​​in the left table are placed before those in the right table; if it is a strict inequality, the duplicate values ​​in the left table are placed after those in the right table.

5. The method according to claim 2, wherein the step of constructing a second sorted array and a permutation array based on the first sorted array includes: Extract the connection column corresponding to the second connection condition from the first sorted array, construct the second sorted array according to the sorting rule of the second connection condition, and the permutation array records the index position of the second sorted array tuple in the first sorted array. The structure of the second sorted array is [second connection column value, permutation array].

6. The method of claim 1, wherein the step of initializing the Bloom filter corresponding to the bit array data block comprises: Initialize a bit array with the same length as the first sorted array, and initialize all bits to invalid flag values; as well as The first sorted array is divided into blocks according to a preset block size. A Bloom filter with the same number of bits as the number of blocks is initialized. All bits are initialized to the invalid identifier value. Each bit of the Bloom filter corresponds to a data block of the bit array and is used to coarsely filter the blank areas of the bit array.

7. The method according to claim 2, wherein the step of using two pointers to traverse the second sorted array to match tuples that satisfy the join condition includes: The first and second pointers are initialized to traverse from the beginning of the second sorted array. The first pointer is dedicated to processing the data in the left table, and the second pointer is dedicated to processing the data in the right table, and the traversal position of the second pointer is always kept within the range of the first pointer. as well as After moving the first pointer to find data in the left table, the second pointer is moved to find data in the right table. When a tuple that satisfies the second connection condition is matched, the corresponding position in the first sorted array is located through the permutation array, and the corresponding position in the bit array and the Bloom filter is set to the valid identifier value.

8. The method of claim 1, wherein the steps of coarsely filtering the bit array through the Bloom filter and scanning valid data blocks include: The position of the left table tuple in the first sorted array is located by the permutation array, and its corresponding block position in the bit array is determined. The Bloom filter is checked to examine the bit corresponding to the block position. If it is an invalid flag value, the data block is skipped. If it is a valid flag value, the bit array is scanned to the right from the start of the data block. When the valid identifier value is detected, it is determined that the corresponding right table tuple and left table tuple satisfy the multiple inequality join condition, and the matching tuple is mapped back to the left and right tables of the original table through the table identifier bit.

9. The method according to any one of claims 1 to 8, wherein, The valid identifier value is a non-zero integer, and the invalid identifier value is 0.

10. A computer-readable storage medium having a computer program stored thereon, the computer program being executed by a processor to implement the steps of the database inequality join sorting method according to any one of claims 1 to 9.

Images