A method, apparatus and device for anti-join processing in a database

Abstract

Embodiments of the present specification disclose a method, device and equipment for anti-join processing in a database. The method comprises: obtaining an anti-join to be executed; determining, in a right table of the anti-join, a data column related to a join condition of the anti-join as a target data column; determining a plurality of data blocks into which the target data column is divided, each of the data blocks corresponding to one or more data rows in the target data column; determining NULL statistical information recorded for the data blocks; if the NULL statistical information of the data blocks reflects that the number of NULLs contained in the data blocks is not 0, adjusting the position of the data block in the target data column upwards; and executing the anti-join according to the adjusted target data column, and ending the execution process in advance when NULL in the adjusted target data column is scanned.

Description

Technical Field

[0001] This specification relates to the field of database technology, and in particular to a method, apparatus, and device for anti-join processing in a database. Background Technology

[0002] In databases, a semi-join refers to a join where only one table is output from the joined tables, while the other table is used to calculate the output condition. A subclass of semi-join is the anti-join, whose expressions for calculating the output condition include NOT IN and <> ALL.

[0003] In practical applications, anti-connections are being used more and more widely. Based on this, there is a need for solutions that can improve the performance of anti-connection execution in order to improve database performance. Summary of the Invention

[0004] This specification provides one or more embodiments of a method, apparatus, device, and storage medium for processing anti-connections in a database, in order to solve the following technical problem: the need for a solution that can improve the performance of anti-connection execution in order to improve database performance.

[0005] To solve the above-mentioned technical problems, one or more embodiments of this specification are implemented as follows:

[0006] This specification provides one or more embodiments of a method for processing anti-joins in a database, including:

[0007] Obtain the anti-connection to be executed;

[0008] In the right table of the anti-join, determine the data column involved in the join condition of the anti-join as the target data column;

[0009] The target data column is divided into multiple data blocks, each data block corresponding to one or more data rows in the target data column;

[0010] The data block was identified as having NULL statistics.

[0011] If the NULL statistics of the data block indicate that the number of NULLs contained in the data block is not 0, then the position of the data block in the target data column is adjusted upwards.

[0012] Based on the adjusted target data column, the reverse join is performed. During the execution process, if a NULL value is detected in the adjusted target data column, the execution process is terminated prematurely.

[0013] This specification provides one or more embodiments of a database anti-join processing apparatus, comprising:

[0014] The anti-connection acquisition module acquires the anti-connections to be executed.

[0015] The data column determination module determines the data column involved in the join condition of the anti-join in the right table of the anti-join, and uses it as the target data column;

[0016] The data block determination module determines multiple data blocks into which the target data column is divided, and each data block corresponds to one or more data rows in the target data column;

[0017] The statistics information determination module determines the NULL statistics information recorded in the data block.

[0018] If the NULL statistics of the data block indicate that the number of NULLs contained in the data block is not 0, the data block adjustment module will adjust the position of the data block upward in the target data column.

[0019] The anti-join execution module performs the anti-join based on the adjusted target data column. During the execution process, if a NULL value is detected in the adjusted target data column, the execution process is terminated prematurely.

[0020] This specification provides one or more embodiments of a database anti-connection processing device, comprising:

[0021] At least one processor; and,

[0022] A memory communicatively connected to the at least one processor; wherein,

[0023] The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to:

[0024] Obtain the anti-connection to be executed;

[0025] In the right table of the anti-join, determine the data column involved in the join condition of the anti-join as the target data column;

[0026] The target data column is divided into multiple data blocks, each data block corresponding to one or more data rows in the target data column;

[0027] The data block was identified as having NULL statistics.

[0028] If the NULL statistics of the data block indicate that the number of NULLs contained in the data block is not 0, then the position of the data block in the target data column is adjusted upwards.

[0029] Based on the adjusted target data column, the reverse join is performed. During the execution process, if a NULL value is detected in the adjusted target data column, the execution process is terminated prematurely.

[0030] This specification provides one or more embodiments of a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured as follows:

[0031] Obtain the anti-connection to be executed;

[0032] In the right table of the anti-join, determine the data column involved in the join condition of the anti-join as the target data column;

[0033] The target data column is divided into multiple data blocks, each data block corresponding to one or more data rows in the target data column;

[0034] The data block was identified as having NULL statistics.

[0035] If the NULL statistics of the data block indicate that the number of NULLs contained in the data block is not 0, then the position of the data block in the target data column is adjusted upwards.

[0036] Based on the adjusted target data column, the reverse join is performed. During the execution process, if a NULL value is detected in the adjusted target data column, the execution process is terminated prematurely.

[0037] The above-described at least one technical solution adopted in one or more embodiments of this specification can achieve the following beneficial effects: For anti-joins, NULL and non-NULL values ​​are treated separately. For NULL values, the execution process is considered to end early, which helps to correctly implement the semantics of anti-joins, especially anti-joins sensitive to NULL values. For non-NULL values, processing methods such as hash joins can be used to improve performance. Moreover, the data table is stored in blocks and NULL information is counted, so the number of NULL values ​​in a data block can be determined without scanning the data table. The position of data blocks containing non-zero NULL values ​​is actively adjusted upwards, so that NULL values ​​can be scanned earlier when performing anti-joins, thereby causing the execution process to end earlier. Therefore, the execution performance of anti-joins is effectively improved, which helps to improve database performance. Attached Figure Description

[0038] To more clearly illustrate the technical solutions in the embodiments or prior art of this specification, the drawings used in the description of the embodiments or prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this specification. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0039] Figure 1 A flowchart illustrating a method for processing anti-joins in a database, provided for one or more embodiments of this specification;

[0040] Figure 2 This diagram illustrates the impact of NULL positions on the time complexity of anti-join execution, provided for one or more embodiments of this specification.

[0041] Figure 3 In one practical application scenario provided by one or more embodiments of this specification, Figure 1 A schematic diagram of one implementation scheme of the Chinese method;

[0042] Figure 4 A schematic diagram of data blocks before reordering in a practical application scenario provided by one or more embodiments of this specification;

[0043] Figure 5 A schematic diagram of reordered data blocks in a practical application scenario provided by one or more embodiments of this specification;

[0044] Figure 6 A schematic diagram of a database anti-connection processing device provided for one or more embodiments of this specification;

[0045] Figure 7 This is a schematic diagram of the structure of a reverse connection processing device in a database provided for one or more embodiments of this specification. Detailed Implementation

[0046] This specification provides an embodiment of a method, apparatus, device, and storage medium for anti-join processing in a database.

[0047] To enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this specification will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this application.

[0048] Currently, reverse joins are generally handled using Cartesian products based on Nested Loops, but this approach is inefficient. Therefore, the inventors considered using hash joins to improve efficiency, but this also presents problems because some expressions used to calculate output conditions in reverse joins are sensitive to NULL, such as expressions based on NOT IN or <> ALL, which hash joins struggle to handle. To address this, the inventors adopted a differentiated handling scheme for NULL and non-NULL values. Non-NULL values ​​can be handled using hash joins, while NULL values ​​are handled specially, potentially ending the execution process earlier and improving efficiency. In this special handling, especially while avoiding immediate full table scans, NULL values ​​are proactively moved to earlier positions based on data chunking, further improving efficiency. This scheme is particularly effective in scenarios involving large datasets (e.g., tables with a large amount of data containing a small number of NULL values), significantly optimizing the performance of NULL-sensitive reverse joins.

[0049] Based on this line of thinking, we will continue to explain in detail below.

[0050] Figure 1 This is a flowchart illustrating a reverse join processing method in a database, provided for one or more embodiments of this specification. This process can be executed on database-related devices, such as distributed database servers and database optimization servers.

[0051] Figure 1 The process includes the following steps:

[0052] S102: Obtain the anti-connection to be executed.

[0053] In one or more embodiments of this specification, the anti-join is a NULL-sensitive anti-join, which is implemented based on at least one of the following types of expressions: NOT IN, <> ALL.

[0054] S104: In the right table of the anti-join, determine the data column involving the join condition of the anti-join as the target data column.

[0055] In an anti-join, the left table is used for output, and the right table is used to calculate the output conditions. The target data column directly relates to the output conditions. However, if NULL values ​​exist in the target data column, it becomes difficult to determine whether the output conditions are met. This is because NULL has a special semantic meaning; it is currently uncertain and may become any value, leading to uncertainty about whether the output conditions are met. Therefore, to be on the safe side, it is assumed that the output conditions are not met, and thus the query results from the left table are not output, allowing the execution of the query directly involved in the anti-join to end prematurely.

[0056] S106: Determine multiple data blocks into which the target data column is divided, each data block corresponding to one or more data rows in the target data column.

[0057] In one or more embodiments of this specification, the data columns in the right table are pre-divided into data blocks for storage.

[0058] Specifically, in the storage implementation of a database, data can be pre-divided into blocks. There are various ways to do this, such as dividing based on the primary key, or dividing each column vertically. One purpose of this division is to perform pre-analytical calculations on the divided data blocks according to actual needs, so that when statistical data is needed later, it can be directly read without having to scan the original data again, thus improving efficiency.

[0059] It should be noted that if the pre-divided data blocks directly meet the requirements (for example, by dividing each data column vertically to obtain data blocks), then when executing S106, the data block corresponding to the target data column can be directly selected from these pre-divided data blocks. However, if the requirements are not directly met (for example, by mixing the target data column with non-target data columns to divide the data blocks), then when executing S106, the data blocks may need to be reprocessed before use. In this case, the data blocks in S106 may not be the original pre-divided data blocks. Of course, even so, based on pre-division and statistical operations, it is still possible to avoid rescanning data to a certain extent, which can improve the efficiency of subsequent anti-join execution performance.

[0060] For the purposes of this application, the statistics here include at least statistics on information related to NULL in the data block, and this portion of statistical information is referred to as NULL statistical information.

[0061] In one or more embodiments of this specification, one or more target data columns are vertically divided into multiple data blocks. For multiple target data columns, the division can be performed as a whole, or each column can be divided separately and then combined. The size of the data block is determined according to actual needs. Assuming the division is based on data volume, for example, dividing into 2MB blocks, for this application, the data blocks can be relatively large (this helps improve the efficiency of data block reordering; however, they should not be too large, otherwise, if NULL values ​​are located at the bottom of the data block, it will affect the efficiency improvement), thus further improving efficiency.

[0062] S108: Determine the NULL statistics recorded for the data block (at least one of the multiple data blocks into which the target data column is divided).

[0063] In one or more embodiments of this specification, NULL statistics can directly or indirectly reflect whether the number of NULLs contained in the corresponding data block (i.e., the data block may contain NULLs, and how many NULLs it contains) is 0 (0 indicates that the data block does not contain NULLs). NULL statistics may specifically include, for example, the total number of NULLs contained in the data block, a flag indicating whether the number of NULLs is 0 (assuming a Boolean bit is used, for example, a value of 1 indicates that the number of NULLs is not 0, and a value of 0 indicates that the number of NULLs is 0), the number of NULLs contained in each column of the data block, and so on.

[0064] Furthermore, NULL statistics can include more information, such as which data block contains the largest or smallest number of NULLs.

[0065] In one or more embodiments of this specification, after calculating the corresponding NULL statistics for each data block, the data is recorded in the metadata (e.g., the metadata of the corresponding storage layer or data table). Then, by accessing the metadata, the NULL statistics recorded for the data block can be obtained without accessing the specific table data, thus reducing data reading overhead.

[0066] S110: If the NULL statistics of the data block indicate that the number of NULLs contained in the data block is not 0, then the position of the data block in the target data column is adjusted upward.

[0067] In one or more embodiments of this specification, if the NULL statistics of a data block reflect that the number of NULLs contained in the data block is not 0, it indicates that there are NULLs in the target data column (or it is very likely that there are NULLs, depending on the data block partitioning strategy). As mentioned above, the presence of NULLs brings uncertainty to the judgment of the anti-join output conditions. Therefore, in order to improve the anti-join execution performance, this application actively adjusts the position of the corresponding data block upward (for example, adjusts it to the top position of the target data column) so that NULLs can be encountered earlier during execution.

[0068] In practical applications, there may be multiple data blocks corresponding to the target data column that contain a non-zero number of NULL values. In this case, at least one of the data blocks can be moved up in position.

[0069] S112: Perform the reverse join according to the adjusted target data column. During the execution, if NULL is found in the adjusted target data column, the execution process is terminated early.

[0070] In one or more embodiments of this specification, adjustments to the target data column can be temporary, for example, only used to perform an anti-join, after which the state before the adjustment can be restored. This adjustment does not necessarily involve operations on the original data of the target data column, but rather operations on the scanning order of the target data column, thus resulting in lower cost and higher efficiency.

[0071] In one or more embodiments of this specification, the process of prematurely terminating execution may further include: not returning any data rows as the corresponding execution result. This conforms to the semantics of NULL-sensitive anti-joins and helps to avoid giving unreliable execution results.

[0072] When a non-NULL value is encountered in the adjusted target data column, it can be processed using methods such as hash join or Nested Loops. Compared to Nested Loops, hash join reduces the computation time complexity from O(n*n) to O(n). Some of the following embodiments assume that hash join is used to process non-NULL values.

[0073] Regarding NULL, this solution increases the NULL value, potentially allowing the anti-join execution to terminate earlier. This could further reduce the time complexity from O(n) to O(n), possibly even reducing it to O(n). Whether the time complexity can be reduced from O(n) to O(1) depends on when the first NULL is read from the target data column during the scan. If the NULL is read from a very low position, the time complexity is O(n); if the NULL is read from a very high position in the right table, the time complexity is O(1).

[0074] For a more intuitive view, see Figure 2 , Figure 2 This diagram illustrates the impact of a NULL position on the time complexity of anti-join execution, as provided in one or more embodiments of this specification.

[0075] exist Figure 2 The example shows a target data column, denoted as c1, assuming that c1 contains 1T rows of data, as shown by the row numbers. Figure 2 If NULL appears very low in c1, then we need to wait until all the preceding data has been processed before processing NULL, resulting in a time complexity of O(n). Figure 2 On the right side of c1, NULL appears quite high up. It is processed when handling the third row of data, resulting in a time complexity of O(1). This demonstrates that proactively controlling the reading of NULL values ​​as early as possible significantly improves the performance of anti-joins in scenarios with large data volumes and few NULL values.

[0076] pass Figure 1 This method, for anti-joins, distinguishes between NULL and non-NULL values. For NULL values, it considers terminating the execution process early, thus helping to correctly implement the semantics of anti-joins, especially those sensitive to NULL values. For non-NULL values, it employs processing methods such as hash joins to improve efficiency. Furthermore, it performs block storage and NULL information statistics on the data table, thereby determining the number of NULL values ​​in a data block without scanning the entire table. It also proactively moves the position of data blocks containing non-zero NULL values ​​upwards, allowing NULL values ​​to be detected earlier during anti-join execution, thus terminating the process earlier. Therefore, it effectively improves the execution efficiency of anti-joins and contributes to improving database performance.

[0077] based on Figure 1 In addition to the method described herein, this specification also provides some specific implementation schemes and extension schemes of this method, which will be further explained below.

[0078] In one or more embodiments of this specification, for multiple data blocks corresponding to a target data column, there may be a situation where more than one data block (referred to as a target data block) contains a non-zero number of NULL values. The distribution and specific position of NULL values ​​in different target data blocks are uncertain. Adjusting any target data block upwards may result in encountering NULL values ​​earlier. However, to further increase the probability of encountering NULL values ​​earlier, different target data blocks can be compared. If a target data block contains a larger number of NULL values, the probability of having NULL values ​​at a higher position within that target data block will also increase accordingly. Based on this, the target data block containing the most NULL values ​​can be adjusted to the highest position. Therefore, this application exemplarily employs a method of reordering data blocks to achieve this purpose.

[0079] For example, when pre-counting NULL information, the number of NULL values ​​is specifically counted. Therefore, the aforementioned NULL statistics at least include the number of NULL values ​​contained in the corresponding data block. Subsequently, during execution S110, multiple data blocks (specifically, a subset of data blocks can be operated on to reduce sorting overhead, such as target data blocks with a non-zero NULL value) are reordered in descending order of NULL value. This yields the adjusted target data column. The first M reordered data blocks contain a non-zero NULL value, and M is not less than 1. Therefore, the first data block contains the most NULL values ​​and is positioned at the top of the target data column after adjustment. In scenarios involving large datasets with a small number of NULL values, the value of M may be very small, which helps improve the speed of reordering. Moreover, compared to the unadjusted case, it significantly improves the speed of encountering NULL values, thus highlighting the advantages of this solution.

[0080] In one or more embodiments of this specification, during the preliminary statistics, if the processing capacity is sufficient, for data blocks containing NULL, the top position of NULL within that data block can be counted and recorded. Subsequently, during adjustment, the target data blocks whose top position is relatively higher can be adjusted upwards accordingly, while the positions of other target data blocks do not need to be adjusted.

[0081] The above describes this solution. More intuitively, one or more embodiments of this specification also provide a practical application scenario. Figure 1 A schematic diagram of one implementation scheme of the Chinese method, such as Figure 3 As shown, combined with Figure 4 and Figure 5 Let's explain together.

[0082] Figure 3 The proposed solution includes the following steps:

[0083] S302: In the storage implementation of the database, the data is pre-divided into data blocks, and statistical information such as the number of NULLs (represented by the Null Count field) is recorded for each data block in the metadata.

[0084] S304: In the SQL computation layer implementation of the database, a data block iterator is pre-built to be specifically responsible for the sequential arrangement of data blocks to control the scanning order.

[0085] S306: For the NULL-sensitive anti-join to be executed, determine each data block in the corresponding target data column of the right table, and read the number of NULLs contained in each data block by accessing the metadata. See also Figure 4 , Figure 4This diagram illustrates the data blocks before reordering in a practical application scenario provided by one or more embodiments of this specification. As can be seen, the target data column is divided into n data blocks (i.e., n vertical segments of data, denoted as Block 1 to n respectively, in top-to-bottom order). Block 3 contains 1 NULL, Block m contains 3 NULLs, and the remaining Block m contains 0 NULLs.

[0086] S308: Before performing the anti-join and starting the data scan, reorder the data blocks using the data block iterator, in descending order of the number of NULL values. See also Figure 5 , Figure 5 This diagram illustrates the reordered data blocks in a practical application scenario provided by one or more embodiments of this specification. As can be seen, after reordering, Block m contains the largest number of NULL values, therefore its position is moved upwards to the top position. Block 3 contains the second largest number of NULL values ​​and is thus moved to the second-highest position.

[0087] In addition, the number of blocks may be large. In order to reduce the overhead of sorting all blocks based on the number of NULLs, we can skip the full sort and only iterate through the number of NULLs contained in these blocks. After iterating through a set number (the set number is not less than 1, for example, 3) of data blocks containing a non-zero number of NULLs, we stop the traversal and adjust the position of the set number of data blocks in the target data column upward so that they can be scanned first.

[0088] S310: Based on the reordered data blocks, data is scanned sequentially to perform a reverse join. Execution ends early when the first NULL is encountered. Therefore, the first scanned block is Block m. Since execution can end early when the first NULL is encountered in Block m, subsequent blocks 3, 1, ..., n do not need to be scanned, saving computational overhead and improving execution performance.

[0089] Based on the same idea, one or more embodiments of this specification also provide apparatus and devices corresponding to the above methods, such as... Figure 6 , Figure 7 As shown. The apparatus and equipment are capable of performing the above methods and related alternatives accordingly.

[0090] Figure 6 This specification provides a schematic diagram of the structure of a database anti-join processing apparatus according to one or more embodiments. The apparatus includes:

[0091] The anti-connection acquisition module 602 acquires the anti-connections to be executed;

[0092] The data column determination module 604 determines the data column involved in the connection condition of the anti-join in the right table of the anti-join as the target data column;

[0093] The data block determination module 606 determines multiple data blocks into which the target data column is divided, and each data block corresponds to one or more data rows in the target data column;

[0094] The statistics information determination module 608 determines the NULL statistics information recorded in the data block.

[0095] The data block adjustment module 610 adjusts the position of the data block upwards in the target data column if the NULL statistics of the data block indicate that the number of NULLs contained in the data block is not 0.

[0096] The anti-join execution module 612 performs the anti-join based on the adjusted target data column. During the execution process, if a NULL value is detected in the adjusted target data column, the execution process is terminated prematurely.

[0097] Optionally, it also includes:

[0098] The data block partitioning module 614 pre-divides the data columns in the right table into data blocks for storage before obtaining the reverse join to be executed; and the NULL statistics module 616 collects corresponding NULL statistics for each data block and records them in the metadata.

[0099] The statistical information determination module 608 obtains the NULL statistical information recorded for the data block by accessing the metadata.

[0100] Optionally, the NULL statistics include the number of NULLs contained in the corresponding data block;

[0101] The data block adjustment module 610 reorders the multiple data blocks in descending order of the number of NULLs, thereby obtaining the adjusted target data column.

[0102] Wherein, the number of NULLs in the first M data blocks after reordering is not 0, and M is not less than 1.

[0103] Optionally, the NULL statistics include the number of NULLs contained in the corresponding data block;

[0104] The data block adjustment module 610 iterates through the number of NULLs contained in the plurality of data blocks. After obtaining a set number of data blocks containing a non-zero number of NULLs, the iteration stops. The set number is not less than 1.

[0105] The positions of the set number of data blocks in the target data column are adjusted upwards.

[0106] Optionally, during the execution process, when the anti-connection execution module 612 detects a non-NULL value in the adjusted target data column, it processes the non-NULL value through a hash connection.

[0107] Optionally, the anti-connection execution module 612 may terminate the execution process prematurely by not returning any data rows as the corresponding execution result.

[0108] Optionally, the anti-join is a NULL-sensitive anti-join.

[0109] Optionally, the anti-connection is implemented based on at least one of the following types of expressions: NOT IN, <> ALL.

[0110] Figure 7 This specification provides a schematic diagram of the structure of a reverse connection processing device in a database, according to one or more embodiments. The device includes:

[0111] At least one processor; and,

[0112] A memory communicatively connected to the at least one processor; wherein,

[0113] The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to:

[0114] Obtain the anti-connection to be executed;

[0115] In the right table of the anti-join, determine the data column involved in the join condition of the anti-join as the target data column;

[0116] The target data column is divided into multiple data blocks, each data block corresponding to one or more data rows in the target data column;

[0117] The data block was identified as having NULL statistics.

[0118] If the NULL statistics of the data block indicate that the number of NULLs contained in the data block is not 0, then the position of the data block in the target data column is adjusted upwards.

[0119] Based on the adjusted target data column, the reverse join is performed. During the execution process, if a NULL value is detected in the adjusted target data column, the execution process is terminated prematurely.

[0120] Based on the same idea, one or more embodiments of this specification also provide a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured as follows:

[0121] Obtain the anti-connection to be executed;

[0122] In the right table of the anti-join, determine the data column involved in the join condition of the anti-join as the target data column;

[0123] The target data column is divided into multiple data blocks, each data block corresponding to one or more data rows in the target data column;

[0124] The data block was identified as having NULL statistics.

[0125] If the NULL statistics of the data block indicate that the number of NULLs contained in the data block is not 0, then the position of the data block in the target data column is adjusted upwards.

[0126] Based on the adjusted target data column, the reverse join is performed. During the execution process, if a NULL value is detected in the adjusted target data column, the execution process is terminated prematurely.

[0127] In the 1990s, improvements to a technology could be clearly distinguished as either hardware improvements (e.g., improvements to the circuit structure of diodes, transistors, switches, etc.) or software improvements (improvements to the methodology). However, with technological advancements, many methodological improvements today can be considered direct improvements to the hardware circuit structure. Designers almost always obtain the corresponding hardware circuit structure by programming the improved methodology into the hardware circuit. Therefore, it cannot be said that a methodological improvement cannot be implemented using hardware physical modules. For example, a Programmable Logic Device (PLD) (such as a Field Programmable Gate Array (FPGA)) is such an integrated circuit whose logic function is determined by the user programming the device. Designers can program and "integrate" a digital system onto a PLD themselves, without needing chip manufacturers to design and manufacture dedicated integrated circuit chips. Furthermore, nowadays, instead of manually manufacturing integrated circuit chips, this programming is mostly implemented using "logic compiler" software. Similar to the software compiler used in program development, the original code before compilation must be written in a specific programming language, called a Hardware Description Language (HDL). There are many HDLs, such as ABEL (Advanced Boolean Expression Language), AHDL (Altera Hardware Description Language), Confluence, CUPL (Cornell University Programming Language), HDCal, JHDL (Java Hardware Description Language), Lava, Lola, MyHDL, PALASM, and RHDL (Ruby Hardware Description Language). Currently, the most commonly used are VHDL (Very-High-Speed ​​Integrated Circuit Hardware Description Language) and Verilog. Those skilled in the art should understand that by simply performing some logic programming on the method flow using one of these hardware description languages ​​and programming it into an integrated circuit, the hardware circuit implementing the logical method flow can be easily obtained.

[0128] The controller can be implemented in any suitable manner. For example, it can take the form of a microprocessor or processor and a computer-readable medium storing computer-readable program code (e.g., software or firmware) executable by the (micro)processor, logic gates, switches, application-specific integrated circuits (ASICs), programmable logic controllers, and embedded microcontrollers. Examples of controllers include, but are not limited to, the following microcontrollers: ARC625D, Atmel AT91SAM, Microchip PIC18F26K20, and Silicon Labs C8051F320. A memory controller can also be implemented as part of the control logic of the memory. Those skilled in the art will also recognize that, in addition to implementing the controller in purely computer-readable program code form, the same functionality can be achieved by logically programming the method steps to make the controller take the form of logic gates, switches, ASICs, programmable logic controllers, and embedded microcontrollers. Therefore, such a controller can be considered a hardware component, and the means included therein for implementing various functions can also be considered as structures within the hardware component. Alternatively, the means for implementing various functions can be considered as both software modules implementing the method and structures within the hardware component.

[0129] The systems, devices, modules, or units described in the above embodiments can be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer. Specifically, a computer can be, for example, a personal computer, laptop computer, cellular phone, camera phone, smartphone, personal digital assistant, media player, navigation device, email device, game console, tablet computer, wearable device, or any combination of these devices.

[0130] For ease of description, the above devices are described in terms of function, divided into various units. Of course, in implementing this specification, the functions of each unit can be implemented in one or more software and / or hardware components.

[0131] Those skilled in the art will understand that the embodiments of this specification can be provided as methods, systems, or computer program products. Therefore, the embodiments of this specification can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the embodiments of this specification can take the form of a computer program product implemented on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0132] This specification is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this specification. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create a machine for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0133] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0134] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0135] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0136] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0137] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media 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 memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0138] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0139] This specification can be described in the general context of computer-executable instructions that are executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc., that perform a specific task or implement a specific abstract data type. This specification can also be practiced in distributed computing environments, where tasks are performed by remote processing devices connected via a communication network. In distributed computing environments, program modules can reside in local and remote computer storage media, including storage devices.

[0140] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the embodiments of apparatus, devices, and non-volatile computer storage media are basically similar to the method embodiments, so the descriptions are relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0141] The foregoing has described specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired result. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired result. In some embodiments, multitasking and parallel processing are possible or may be advantageous.

[0142] The above description is merely one or more embodiments of this specification and is not intended to limit this specification. Various modifications and variations can be made to the one or more embodiments of this specification by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principle of one or more embodiments of this specification should be included within the scope of the claims of this specification.

Claims

1. A method for handling unjoins in a database, comprising: Obtain the anti-connection to be executed; In the right table of the anti-join, determine the data column involved in the join condition of the anti-join as the target data column; The target data column is divided into multiple data blocks, each data block corresponding to one or more data rows in the target data column; The data block was identified as having NULL statistics. If the NULL statistics of the data block indicate that the number of NULLs contained in the data block is not 0, then the position of the data block in the target data column is adjusted upwards. The adjustment includes: moving the data block before the data blocks that do not contain NULLs. Based on the adjusted target data column, the reverse join is performed. During the execution process, if a NULL value is detected in the adjusted target data column, the execution process is terminated prematurely.

2. The method of claim 1, wherein before obtaining the anti-connection to be executed, the method further comprises: The data columns in the right table are pre-divided into data blocks for storage; and, For each of the aforementioned data blocks, the corresponding NULL statistics are calculated and recorded in the metadata. The NULL statistics identified as records of the data block specifically include: By accessing the metadata, NULL statistics recorded for the data block can be obtained.

3. The method as described in claim 1, wherein the NULL statistics include the number of NULL values ​​contained in the corresponding data block; If the NULL statistics of the data block indicate that the number of NULL values ​​in the data block is not zero, then the position of the data block in the target data column is adjusted upwards, specifically including: The multiple data blocks are reordered in descending order of the number of NULLs to obtain the adjusted target data column. Wherein, the number of NULLs in the first M data blocks after reordering is not 0, and M is not less than 1.

4. The method as described in claim 1, wherein the NULL statistics include the number of NULL values ​​contained in the corresponding data block; If the NULL statistics of the data block indicate that the number of NULL values ​​in the data block is not zero, then the position of the data block in the target data column is adjusted upwards, specifically including: The system iterates through the number of NULL values ​​contained in the plurality of data blocks. After obtaining a set number of data blocks containing a non-zero number of NULL values, the iteration stops. The set number is not less than 1. The positions of the set number of data blocks in the target data column are adjusted upwards.

5. The method as described in claim 1, wherein performing the anti-connection specifically includes: During the execution process, when a non-NULL value is detected in the adjusted target data column, the non-NULL value is processed by hash join.

6. The method of claim 1, wherein the step of prematurely terminating the execution process further includes: It does not return any data rows as the corresponding execution result.

7. The method according to any one of claims 1 to 6, wherein the anti-join is a NULL-sensitive anti-join.

8. The method of claim 7, wherein the anti-connection is implemented based on at least one of the following types of expressions: NOT IN, < > ALL.

9. A reverse join processing apparatus in a database, comprising: The anti-connection acquisition module acquires the anti-connections to be executed. The data column determination module determines the data column involved in the join condition of the anti-join in the right table of the anti-join, and uses it as the target data column; The data block determination module determines multiple data blocks into which the target data column is divided, and each data block corresponds to one or more data rows in the target data column; The statistics information determination module determines the NULL statistics information recorded in the data block. The data block adjustment module, if the NULL statistics of the data block indicate that the number of NULLs contained in the data block is not 0, then adjusts the position of the data block in the target data column upwards. The adjustment includes: adjusting the data block before the data blocks that do not contain NULLs. The anti-join execution module performs the anti-join based on the adjusted target data column. During the execution process, if a NULL value is detected in the adjusted target data column, the execution process is terminated prematurely.

10. The apparatus of claim 9, further comprising: The data block partitioning module pre-divides the data columns in the right table into data blocks for storage before obtaining the reverse join to be executed; Furthermore, the NULL statistics module calculates the corresponding NULL statistics for each data block and records them in the metadata. The statistical information determination module obtains the NULL statistical information recorded for the data block by accessing the metadata.

11. The apparatus of claim 9, wherein the NULL statistics include the number of NULLs contained in the corresponding data block; The data block adjustment module reorders the multiple data blocks in descending order of the number of NULLs to obtain the adjusted target data column. in, The number of NULL values ​​in the first M data blocks after reordering is not 0, and M is not less than 1.

12. The apparatus of claim 9, wherein the NULL statistics include the number of NULLs contained in the corresponding data block; The data block adjustment module iterates through the number of NULL values ​​contained in the plurality of data blocks. After obtaining a set number of data blocks containing a non-zero number of NULL values, the iteration stops. The set number is not less than 1. The positions of the set number of data blocks in the target data column are adjusted upwards.

13. The apparatus of claim 9, wherein the anti-connection execution module, during the execution process, when a non-NULL value is detected in the adjusted target data column, processes the non-NULL value through a hash connection.

14. The apparatus of claim 9, wherein the anti-connection execution module, in terminating the execution process prematurely, further comprises: It does not return any data rows as the corresponding execution result.

15. The apparatus of any one of claims 9 to 14, wherein the anti-connection is a NULL-sensitive anti-connection.

16. The apparatus of claim 15, wherein the anti-connection is implemented based on at least one of the following types of expressions: NOT IN, < > ALL.

17. A reverse connection processing device in a database, comprising: At least one processor; as well as, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform: Obtain the anti-connection to be executed; In the right table of the anti-join, determine the data column involved in the join condition of the anti-join as the target data column; The target data column is divided into multiple data blocks, each data block corresponding to one or more data rows in the target data column; The data block was identified as having NULL statistics. If the NULL statistics of the data block indicate that the number of NULLs contained in the data block is not 0, then the position of the data block in the target data column is adjusted upwards. The adjustment includes: moving the data block before the data blocks that do not contain NULLs. Based on the adjusted target data column, the reverse join is performed. During the execution process, if a NULL value is detected in the adjusted target data column, the execution process is terminated prematurely.

Images