A database table scanning method, apparatus and device
By combining virtual row numbers and Boolean strings, the problem of low efficiency in database table scanning is solved, enabling efficient data filtering and fast scanning of the latest data. It is suitable for databases with storage structures such as LSM-Tree.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- BEIJING OCEANBASE TECHNOLOGY CO LTD
- Filing Date
- 2022-10-11
- Publication Date
- 2026-05-19
AI Technical Summary
Existing database table scanning solutions involve large amounts of data and computation, resulting in high memory and hard disk space consumption, high CPU consumption, and low efficiency.
By employing a combination of virtual row numbers and Boolean strings, composite filtering conditions are efficiently executed through Boolean bitwise operations. This method is adapted to storage structures such as LSM-Tree and adjusts the distribution of virtual row numbers and Boolean strings to improve scanning and filtering efficiency.
It improves the efficiency of database table scanning, reduces the storage burden of virtual row numbers and boolean strings, and quickly scans to the latest data, making it suitable for databases containing baseline and incremental datasets.
Smart Images
Figure CN115563116B_ABST
Abstract
Description
Technical Field
[0001] This specification relates to the field of database technology, and in particular to a database table scanning method, apparatus, and device. Background Technology
[0002] When analyzing and processing data in a database table, multiple data columns are usually scanned to obtain filtering results. Composite filtering conditions containing multiple individual filtering conditions are used. Individual filtering conditions are combined using logical operators such as AND and OR. For example, a composite filtering condition is expressed as "C1<10 AND (C2 >5 OR C3 == 8)", where C1, C2, and C3 each represent a data column.
[0003] Currently, in these AND, OR and other combined operations, the primary key or physical address of the data row is usually used for representation and comparison, resulting in large data volume, large amount of computation, large consumption of memory and hard disk space, and large CPU consumption.
[0004] Therefore, a more efficient database table scanning solution is needed. Summary of the Invention
[0005] This specification provides one or more embodiments of a database table scanning method, apparatus, device, and storage medium to solve the following technical problem: the need for a more efficient database table scanning solution.
[0006] To solve the above-mentioned technical problems, one or more embodiments of this specification are implemented as follows:
[0007] This specification provides a database table scanning method according to one or more embodiments, wherein the database table is stored through multiple data sets, the multiple data sets including a baseline data set and an incremental data set, the method comprising:
[0008] The data columns involved in the set filtering conditions in the database table are determined as target columns. The database table has a corresponding Boolean string for each data set, and the Boolean bits in the Boolean string correspond to the virtual row number of the data row in the corresponding data set.
[0009] For each data row corresponding to the target column, perform the following: search in the incremental data set and the baseline data set to determine the Boolean string corresponding to the data set where the latest data of the data row is located; determine whether the latest data meets the filtering condition; and based on the determination result, assign a virtual row number of the data row in the data set where it is located, and assign a value to the corresponding Boolean bit in the Boolean string.
[0010] The filtering result is determined based on the assigned Boolean strings.
[0011] This specification provides a database table scanning device according to one or more embodiments, wherein the database table is stored through multiple data sets, the multiple data sets including a baseline data set and an incremental data set, and the device includes:
[0012] The target column determination module determines the data columns involved in the set filtering conditions in the database table as target columns. The database table has a corresponding Boolean string for each data set, and the Boolean bits in the Boolean string correspond to the virtual row number of the data row in the corresponding data set.
[0013] The target column scanning module performs the following steps for each data row corresponding to the target column: searching in the incremental data set and the baseline data set to determine the Boolean string corresponding to the data set where the latest data of the data row is located; determining whether the latest data meets the filtering conditions; and assigning a value to the corresponding Boolean bit in the Boolean string based on the determination result.
[0014] The filtering result determination module determines the filtering result based on the assigned Boolean strings.
[0015] This specification provides a database table scanning device according to one or more embodiments, wherein the database table is stored through multiple data sets, the multiple data sets including a baseline data set and an incremental data set, and the device includes:
[0016] At least one processor; and,
[0017] A memory communicatively connected to the at least one processor; wherein,
[0018] 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:
[0019] The data columns involved in the set filtering conditions in the database table are determined as target columns. The database table has a corresponding Boolean string for each data set, and the Boolean bits in the Boolean string correspond to the virtual row number of the data row in the corresponding data set.
[0020] For each data row corresponding to the target column, perform the following: search in the incremental data set and the baseline data set to determine the Boolean string corresponding to the data set where the latest data of the data row is located; determine whether the latest data meets the filtering condition; and based on the determination result, assign a virtual row number of the data row in the data set where it is located, and assign a value to the corresponding Boolean bit in the Boolean string.
[0021] The filtering result is determined based on the assigned Boolean strings.
[0022] This specification provides one or more embodiments of a non-volatile computer storage medium, in which database tables are stored through multiple data sets, the multiple data sets including a baseline data set and an incremental data set, and the medium stores computer-executable instructions configured as follows:
[0023] The data columns involved in the set filtering conditions in the database table are determined as target columns. The database table has a corresponding Boolean string for each data set, and the Boolean bits in the Boolean string correspond to the virtual row number of the data row in the corresponding data set.
[0024] For each data row corresponding to the target column, perform the following: search in the incremental data set and the baseline data set to determine the Boolean string corresponding to the data set where the latest data of the data row is located; determine whether the latest data meets the filtering condition; and based on the determination result, assign a virtual row number of the data row in the data set where it is located, and assign a value to the corresponding Boolean bit in the Boolean string.
[0025] The filtering result is determined based on the assigned Boolean strings.
[0026] The above-mentioned at least one technical solution adopted in one or more embodiments of this specification can achieve the following beneficial effects: Based on the combination of virtual row numbers and Boolean strings, various operations involved in composite filtering conditions can be efficiently performed through Boolean bitwise operations, and based on multi-way scanning (here, multi-way can include processing of multiple target columns or processing of multiple Boolean strings), the scanning filtering efficiency is effectively improved; moreover, it is particularly suitable for databases such as those using LSM-Tree storage structures that include baseline data sets and incremental data sets, by adjusting the distribution of virtual row numbers and Boolean strings and the scanning process, so that the virtual row numbers and Boolean strings involved in the scanning process are more lightweight and the latest data is scanned faster, which helps to further improve the scanning filtering efficiency. Attached Figure Description
[0027] 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.
[0028] Figure 1 A schematic flowchart illustrating a database table scanning method provided in one or more embodiments of this specification;
[0029] Figure 2 (a) and (b) in this specification are schematic diagrams illustrating the principle of a multi-layered storage structure for a database provided in one or more embodiments.
[0030] Figure 3 A schematic diagram illustrating the correspondence between virtual line numbers and Boolean strings provided for one or more embodiments of this specification;
[0031] Figure 4 A flowchart illustrating a data column scanning scheme provided for one or more embodiments of this specification;
[0032] Figure 5 A flowchart illustrating a materialization scheme provided for one or more embodiments of this specification;
[0033] Figure 6 A schematic diagram of the structure of a database table scanning device provided for one or more embodiments of this specification;
[0034] Figure 7 This is a schematic diagram of the structure of a database table scanning device provided for one or more embodiments of this specification. Detailed Implementation
[0035] This specification provides a database table scanning method, apparatus, device, and storage medium through its embodiments.
[0036] 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.
[0037] To address the problems in the background technology, we consider introducing virtual row numbers and corresponding boolean strings to perform scanning filtering based on filtering conditions.
[0038] In a database table, all data rows are sequentially counted using integers starting from 0 or 1 (assumed to be 1 in the following description) according to their primary key or physical address, serving as virtual row numbers. The primary key is a set of columns specified in the database table, ensuring no duplicate rows are included. Database data can be sorted and clustered by the primary key. The physical address refers to the actual storage location of the data row, such as represented by ROWID. Boolean strings, represented using data structures such as arrays or strings, contain multiple boolean bits, each corresponding to a virtual row number. The boolean bit value is either 1 or 0 (usually 1 for true and 0 for false), indicating whether the data indicated by the corresponding virtual row number meets the filtering conditions. Thus, AND, OR, and other operations between multiple data columns can be efficiently performed using the corresponding AND and OR operations on the boolean string. Furthermore, considering that this solution works well for relatively static data, but is less effective for continuously modified data, especially LogStructured Merge Tree (LSM-Tree) data, where rows are constantly being added or deleted, making virtual row numbers unusable directly, this solution has been further improved to be adapted for LSM-Tree scenarios. This approach will be explained in more detail below.
[0039] Figure 1 This is a flowchart illustrating a database table scanning method provided in one or more embodiments of this specification. This process is executed, for example, on a database server or a business processing device connected to the database. Figure 1 In such scenarios, database tables are stored through multiple data sets, which include baseline data sets and incremental data sets. LSM-Tree is a typical example of this type of structure. In LSM-Tree, the baseline data set is usually represented as a baseline data layer, and the incremental data set is represented as one or more incremental data layers.
[0040] See Figure 2 , Figure 2 (a) and (b) in this specification are schematic diagrams of a multi-layered storage structure for a database provided by one or more embodiments.
[0041] exist Figure 2 In (a), there are multiple data layers, one baseline data layer constitutes the baseline data set mentioned above, and one or more incremental data layers constitute the incremental data set mentioned above.
[0042] The baseline data layer typically stores most of the data corresponding to a relatively distant and stable baseline time. This data is usually stored on disk. After this baseline time, newly added data (data changes due to insertion, deletion, etc.) is temporarily stored in the incremental data layer. When appropriate, the data in the incremental data layer is merged into the baseline data layer. This incremental data layer is usually in memory, but may have some data on disk. Overall, the time series corresponding to the baseline data layer is older than that corresponding to the incremental data layer. However, when multiple incremental data layers exist, there is also a time series relationship between them. Figure 2 (a) indicates that the incremental data layer further away from the baseline data layer has a relatively new time series. Therefore, the incremental data layer referred to by level 1 is the latest in time series, and the baseline data layer referred to by level n is the oldest in time series.
[0043] exist Figure 2 (b) illustrates that data in a dataset can be stored by data column or by data column group. Each data column can be stored separately; for example, column group 2 and column group 3 each contain only one data column. Alternatively, multiple data columns can be stored together through column groups; for example, column group 1 contains two data columns. The number of data rows corresponding to each data column can be different.
[0044] Figure 1 The process includes the following steps:
[0045] S102: Determine the data columns involved in the database table for the set filtering conditions as target columns. The database table has a corresponding Boolean string for each data set, and the Boolean bits in the Boolean string correspond to the virtual row number of the data row in the corresponding data set.
[0046] In one or more embodiments of this specification, the Boolean strings corresponding to each data set can be independent of each other. The baseline data set has its corresponding Boolean string. Assuming that the baseline data set has N rows of data sorted by the primary key, their virtual row numbers are 1 to N, and the Boolean string corresponding to the baseline data set has N Boolean bits and a length of N / 8 bytes. The incremental data set also has its corresponding Boolean string. Assuming that the incremental data set has M rows of data sorted by the primary key (generally, M is much smaller than N), their virtual row numbers are 1 to M, and the Boolean string corresponding to the incremental data set has M Boolean bits.
[0047] It should be noted that for an incremental dataset containing multiple incremental data layers, these multiple incremental data layers can correspond to the same Boolean string (the data from these incremental data layers needs to be integrated so that they can be uniformly represented by a single Boolean string). The advantage of this approach is that it reduces the number of Boolean strings that need to be iterated over. Alternatively, each incremental data layer can have its own corresponding independent Boolean string (or only a few incremental data layers may correspond to the same Boolean string). The advantage of this approach is that it facilitates control over the size of the virtual row numbers. For ease of description, some of the following embodiments mainly illustrate the case where the entire incremental dataset has only one corresponding Boolean string. For cases where multiple incremental data layers correspond to different Boolean strings, the processing of each incremental data layer can be similarly handled, referring to the subsequent processing of the incremental dataset.
[0048] Filtering conditions involve one or more data columns (i.e., it is necessary to determine whether the filtering conditions are met based on the values in these data columns), and each data column has one or more individual filtering conditions. If a filtering condition contains multiple individual filtering conditions, it is called a composite filtering condition. Taking the composite filtering condition "C1<10AND (C2 >5 OR C3 == 8)" mentioned in the background technology as an example, it involves three data columns: C1, C2, and C3. The individual filtering condition for C1 is "C1<10", the individual filtering condition for C2 is "C2 >5", and the individual filtering condition for C3 is "C3 == 8". These three individual filtering conditions are connected by AND, OR, and parentheses logical operators to form a composite filtering condition.
[0049] The value of a Boolean bit in a Boolean string indicates whether the corresponding data row meets a single filtering condition or the entire compound filtering condition. A first value indicates that the condition is met and the data is retained after filtering; a second value indicates that the condition is not met and the data is discarded after filtering. A Boolean bit is a binary variable, taking the value 1 or 0. For ease of description, we assume the first value is 1 and the second value is 0, but the reverse is also possible.
[0050] For a more intuitive view, see Figure 3 , Figure 3 This is a schematic diagram illustrating the correspondence between virtual line numbers and Boolean strings, provided for one or more embodiments of this specification.
[0051] exist Figure 3In the diagram, the left side represents the virtual row numbers in the baseline or incremental dataset, counting from 1 to 6. The right side contains the corresponding Boolean strings, represented by an array where each element is a Boolean bit, corresponding one-to-one with the virtual row number on the left. It is assumed that all Boolean bits have already been assigned values. We can see that the Boolean bits for the current virtual row numbers 2, 4, and 5 are all 1, indicating that these rows meet the filtering conditions, while the other rows do not. The Boolean strings have a relatively small data size and low storage burden; further cost reduction can be achieved by compressing the Boolean strings.
[0052] In one or more embodiments of this specification, there may be multiple target columns. In this case, the target columns can be scanned in parallel to improve efficiency.
[0053] S104: For each data row corresponding to the target column, perform the following: search in the incremental data set and the baseline data set to determine the Boolean string corresponding to the data set where the latest data of the data row is located; determine whether the latest data meets the filtering condition; and based on the determination result, assign a virtual row number of the data row in the data set where it is located, and assign a value to the corresponding Boolean bit in the Boolean string.
[0054] Step S104 illustrates the scanning process of the target column. This includes sub-steps such as determining whether the filtering conditions are met and assigning values to Boolean bits. It should be noted that when multiple individual filtering conditions exist, these two steps can be performed in combination. Specifically, when determining whether the latest data meets the filtering conditions, it can first determine whether a portion of the data (e.g., rows belonging to a certain target column) meets a specific individual filtering condition, and then assign values to the corresponding Boolean bits. However, this assignment may not be the final result; further judgments and assignments need to be made based on this assignment for other target columns and other individual filtering conditions. Finally, the final Boolean bit values for the overall composite filtering conditions are obtained to determine the subsequent filtering results.
[0055] In one or more embodiments of this specification, when filtering data, the latest data of a data row should be used as the standard. The latest data of a data row may be in the baseline data set (e.g., the data row has not been updated recently) or in the incremental data set (e.g., the data row has been updated recently).
[0056] The latest data specifically refers to the latest data of the corresponding data row in the current target column. The latest data of the same data row in a specified data column is located in one of the data sets. To improve search efficiency, for a data row corresponding to a target column, during scanning, the latest data can be searched in the incremental data set first, in chronological order from newest to oldest. If found, there is no need to continue searching in the baseline data set; if not found, then the search continues in the baseline data set.
[0057] Based on the latest data, it is determined whether the filtering conditions are met, and the corresponding Boolean bits in the Boolean string corresponding to the dataset containing the latest data are assigned values accordingly. This ensures that the Boolean bit values can timely, correctly, orderly, and with low redundancy represent the relationship between the corresponding data and the filtering conditions, thereby helping to efficiently organize the filtering results in the following steps.
[0058] In one or more embodiments of this specification, if the determination result is yes, then the data row is the virtual row number in the data set where it is located, and the corresponding Boolean bit in the Boolean string is assigned a first value to indicate that it is retained after filtering; if the determination result is no, then the data row is the virtual row number in the data set where it is located, and the corresponding Boolean bit in the Boolean string is assigned a second value to indicate that it is discarded after filtering.
[0059] When the filtering condition is a single-item filtering condition, the processing described in the previous paragraph is concise, clear, and easy to understand. However, when the filtering condition is a composite filtering condition involving multiple data columns of a database table (i.e., multiple target columns), Boolean bitwise operations are also involved in the processing. Specifically, the same Boolean bit is determined, and its value is assigned to each of the multiple single-item filtering conditions included in the composite filtering condition. Based on the composite operation of the multiple single-item filtering conditions, Boolean bitwise operations are performed between the corresponding assignments. The result of the Boolean bitwise operation is used to determine whether the latest data satisfies the composite filtering condition.
[0060] For example, for the first target column, determine whether the corresponding single-item filter condition is met and assign a value accordingly. Then, the resulting Boolean string is given to the next target column so that Boolean operations can be performed with the corresponding assignment in the next target column. For example, for the AND operator, data rows with a Boolean value of 0 can be skipped directly, while for the OR operator, each data row needs to be checked to obtain an assignment, and then a bitwise OR operation is performed with the previous Boolean string. Of course, the implementation method is not limited to this one and can be varied. For example, for a Boolean string, a copy of the Boolean string can be generated for each target column, and then the Boolean string copy can be assigned a value according to its corresponding target column. Then, Boolean bitwise operations are performed between these Boolean string copies to obtain the final Boolean string with a determined assignment.
[0061] S106: Determine the filtering result based on the assigned Boolean strings.
[0062] In one or more embodiments of this specification, the filtering result is determined based on the Boolean bits in the Boolean string that are assigned the first value. Without considering the data order, the data rows corresponding to the Boolean bits that take the first value in each assigned Boolean string can be identified as retained data rows. The filtering result is determined based on each retained data row. If the relevant redundant and unexpected data are relatively cleanly excluded, then each retained data row can be directly determined as the filtering result.
[0063] To improve user experience and efficiently and systematically output filtering results, a further scheme for determining the filtering results is provided. Specifically, this includes: determining the virtual row number corresponding to the first Boolean bit with a value of 1 in the Boolean string corresponding to the baseline data set, and using this as the first row number; determining the virtual row number corresponding to the first Boolean bit with a value of 1 in the Boolean string corresponding to the incremental data set, and using this as the second row number; determining the first data row identifier corresponding to the first row number and the second data row identifier corresponding to the second row number, where both the first and second data row identifiers are either primary keys or physical addresses, or other identifiers that uniquely identify a data row; comparing the first and second data row identifiers, and selecting the data row corresponding to the smaller one as the retained data row; determining the virtual row number corresponding to the next Boolean bit with a value of 1 in the smaller one, and continuing the above comparison and data row selection process until the Boolean strings corresponding to the baseline and incremental data sets are processed, at which point each retained data row can be determined as the filtering result. The resulting filtering results conform to the primary key or physical address order. This method is particularly efficient when the filtered data volume is large, as it fully utilizes the partial order that virtual row numbers can represent, thereby effectively reducing redundant sorting operations and avoiding sorting large sets of primary keys or physical addresses. Further explanation will follow with practical examples.
[0064] In one or more embodiments of this specification, redundant data and unexpected data are mentioned above. Redundant data includes, for example, the old data corresponding to the latest data and the Boolean bits corresponding to the old data. Unexpected data includes, for example, deleted data and its corresponding Boolean bits. For this data, the corresponding Boolean bits can be actively assigned a second value to prevent these Boolean bits from remaining in the values assigned at some previous time and failing to correctly reflect the latest situation.
[0065] For example, after searching in the incremental data set and the baseline data set, it is determined whether the latest data found exists in the incremental data set or the baseline data set. If it exists in the incremental data set and the data row also exists in the baseline data set (indicating that the data in the baseline data set is old data), then the corresponding Boolean string in the baseline data set is determined as the virtual row number of the data row in the baseline data set, and the corresponding Boolean bit in the Boolean string is assigned the second value to indicate that it is discarded after filtering.
[0066] For example, after determining the Boolean string corresponding to the data set where the latest data of a data row is located, it is determined whether the latest data of that data row contains a deletion flag. If the deletion flag indicates that the operation on that data row was a data deletion operation, then the virtual row number of that data row in the data set is used, and the corresponding Boolean bit in the Boolean string is assigned the second value to indicate that it is discarded after filtering.
[0067] pass Figure 1 This method, based on the combination of virtual row numbers and Boolean strings, can efficiently perform various operations related to composite filtering conditions through Boolean bitwise operations, and effectively improves scanning filtering efficiency based on multi-way scanning. Moreover, it is particularly suitable for databases with storage structures such as LSM-Tree that include a baseline dataset and one or more incremental datasets. The distribution of virtual row numbers and Boolean strings and the scanning process are adjusted to make the virtual row numbers and Boolean strings involved in the scanning process more lightweight and to scan the latest data faster, which helps to further improve scanning filtering efficiency.
[0068] 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.
[0069] As mentioned earlier in one or more embodiments of this specification, in an LSM-Tree scenario, the dynamic nature of data rows due to continuous additions and deletions makes it impossible to directly use virtual row numbers. To address this issue, data reading in the database containing the aforementioned database table employs a snapshot read approach. This allows the introduction of virtual row numbers, similar to read-only data. During the reading process, even the latest incremental data being modified in memory remains unchanged relative to the snapshot used. Therefore, all incremental and baseline data can be considered constant. In this case, the data rows obtained are also snapshot data, facilitating the use of virtual row numbers and enabling efficient data filtering operations involving multiple data columns.
[0070] The above has described this solution. To facilitate understanding, and in conjunction with specific scenarios and more complete examples, exemplary implementation schemes for the two most important parts are shown below. Figure 4 , Figure 5 .
[0071] Figure 4 This is a flowchart illustrating a data column scanning scheme provided for one or more embodiments of this specification.
[0072] exist Figure 4 The image shows a scan process for one of the data columns C1, which may include the following steps:
[0073] Start scanning data column C1. Based on each boolean string, initiate a merge of the baseline data set and the incremental data set through the primary key or ROWID. Through this process, determine whether the latest data of a certain data row corresponding to C1 exists in the incremental data set.
[0074] If it exists, it means that the latest data of the data row is in the incremental data set. We can further determine whether the data of the data row also exists in the baseline data set. If it also exists in the baseline data set, we set the Boolean bit corresponding to the virtual row number in the Boolean string corresponding to the baseline data set to 0. We also need to determine whether the latest data in the incremental data set meets the filtering condition corresponding to C1 (e.g., C1>5). If it meets the condition, we set the Boolean bit corresponding to the virtual row number in the Boolean string corresponding to the incremental data set to 1. If it does not meet the condition, we set it to 0.
[0075] If it does not exist, it means that the latest data of the data row is in the baseline data set. Then, it is determined whether the latest data in the baseline data set meets the filtering condition corresponding to C1. If it does, the Boolean bit corresponding to the virtual row number in the Boolean string corresponding to the baseline data set is set to 1. If it does not meet, it is set to 0.
[0076] Continue iteratively executing the above steps for the next data row corresponding to C1 until the entire C1 has been scanned.
[0077] Figure 5 This is a flowchart illustrating a materialization scheme provided for one or more embodiments of this specification. Here, materialization refers to obtaining data that meets filtering conditions from a data table.
[0078] based on Figure 4 The process involves scanning all the data columns to be scanned, obtaining boolean strings corresponding to the baseline and incremental data sets. When materialization is needed (e.g., when returning the filtered results to the user), these boolean strings can be merged using the primary key or ROWID as the key. Specifically, as follows... Figure 5As shown in the process diagram, it may include the following steps:
[0079] Begin materialization by extracting the virtual row number corresponding to the first non-zero boolean bit of the boolean strings corresponding to the baseline data set and the incremental data set, respectively, and denoting them as base_vid and inc_vid;
[0080] Find the primary key or ROWID corresponding to base_vid and inc_vid respectively, and denote them as base_pk and inc_pk;
[0081] If base_pk > inc_pk, then the snapshot data of the data row corresponding to inc_pk is output. At the same time, the current inc_vid is consumed, and the virtual row number corresponding to the next non-zero boolean bit of the boolean string corresponding to the incremental data set is taken and used as inc_vid again.
[0082] If base_pk < inc_pk, then the snapshot data of the data row corresponding to base_pk is output. At the same time, the current base_vid is consumed, and the virtual row number corresponding to the next non-zero boolean bit of the boolean string corresponding to the baseline data set is taken and used as the base_vid again.
[0083] The process iterates through the steps of comparison and data retrieval, checking whether the boolean strings corresponding to the baseline data set and the incremental data set have been processed. If not, it continues iterating; if so, it executes the next step.
[0084] The captured snapshot data is used as a materialized result (i.e., a filtered result), and the materialized result is output and returned to the user who needs it.
[0085] 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.
[0086] Figure 6 This is a schematic diagram of a database table scanning device provided in one or more embodiments of this specification. The database table is stored through multiple data sets, which include a baseline data set and an incremental data set. The device includes:
[0087] The target column determination module 602 determines the data columns involved in the database table for the set filtering conditions as target columns. The database table has a corresponding Boolean string for each data set, and the Boolean bits in the Boolean string correspond to the virtual row number of the data row in the corresponding data set.
[0088] The target column scanning module 604 performs the following steps for each data row corresponding to the target column: searching in the incremental data set and the baseline data set to determine the Boolean string corresponding to the data set where the latest data of the data row is located; determining whether the latest data meets the filtering conditions; and assigning a value to the corresponding Boolean bit in the Boolean string based on the determination result for the virtual row number of the data row in the data set where it is located.
[0089] The filtering result determination module 606 determines the filtering result based on the assigned Boolean strings.
[0090] Optionally, if the target column scanning module 604 determines the result to be yes, it assigns the virtual row number of the data row in the data set to the first value to the corresponding Boolean bit in the Boolean string, so as to indicate that it is retained after filtering.
[0091] If the judgment result is negative, then the virtual row number of the data row in the data set is used, and the corresponding Boolean bit in the Boolean string is assigned the second value to indicate that it is discarded after filtering.
[0092] Optionally, after searching in the incremental data set and the baseline data set, the target column scanning module 604 determines whether the latest data exists in the incremental data set or in the baseline data set;
[0093] If the data of the same row exists in both the incremental data set and the baseline data set, then the Boolean string corresponding to the baseline data set is determined as the virtual row number of the data row in the baseline data set. The Boolean bit in the Boolean string is assigned a second value to indicate that the data is discarded after filtering.
[0094] Optionally, the filtering conditions are composite filtering conditions involving multiple data columns of the database table;
[0095] The target column scanning module 604 determines the same Boolean bit and assigns values to the multiple individual filtering conditions included in the composite filtering condition, respectively.
[0096] Based on the composite operation of the multiple individual filtering conditions in the composite filtering conditions, Boolean bitwise operations are performed between the corresponding assignments accordingly;
[0097] Based on the result of the Boolean bitwise operation, determine whether the latest data satisfies the composite filtering condition.
[0098] Optionally, after determining the Boolean string corresponding to the data set where the latest data of the data row is located, the target column scanning module 604 determines whether the latest data of the data row contains a deletion marker;
[0099] If so, then the virtual row number of the data row in the data set is used, and the corresponding Boolean bit in the Boolean string is assigned the second value to indicate that it will be discarded after filtering.
[0100] Optionally, the filtering result determination module 606 determines the data rows corresponding to the Boolean bits that take the first value in each of the assigned Boolean strings as the retained data rows;
[0101] The filtering results are determined based on each of the reserved data rows.
[0102] Optionally, the filtering result determination module 606 determines, in the Boolean string corresponding to the first Boolean bit that takes the first value, as the first row number;
[0103] In the Boolean string corresponding to the incremental data set, determine the virtual row number corresponding to the first Boolean bit that takes the first value, and use it as the second row number;
[0104] Determine the first data row identifier corresponding to the first row number and the second data row identifier corresponding to the second row number. Both the first data row identifier and the second data row identifier are primary keys or both are physical addresses.
[0105] Compare the size of the first data row identifier and the second data row identifier, and take the data row corresponding to the smaller one as the reserved data row;
[0106] Determine the virtual row number corresponding to the next Boolean bit with the value of the first value for the smaller one, so as to continue the above comparison and data row retrieval process until the Boolean strings corresponding to the baseline data set and the incremental data set are processed.
[0107] Each of the reserved data rows is determined as the filtering result.
[0108] Optionally, the data row is snapshot data.
[0109] Figure 7 This is a schematic diagram of the structure of a database table scanning device provided in one or more embodiments of this specification. The database table is stored through multiple data sets, which include a baseline data set and an incremental data set. The device includes:
[0110] At least one processor; and,
[0111] A memory communicatively connected to the at least one processor; wherein,
[0112] 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:
[0113] The data columns involved in the set filtering conditions in the database table are determined as target columns. The database table has a corresponding Boolean string for each data set, and the Boolean bits in the Boolean string correspond to the virtual row number of the data row in the corresponding data set.
[0114] For each data row corresponding to the target column, perform the following: search in the incremental data set and the baseline data set to determine the Boolean string corresponding to the data set where the latest data of the data row is located; determine whether the latest data meets the filtering condition; and based on the determination result, assign a virtual row number of the data row in the data set where it is located, and assign a value to the corresponding Boolean bit in the Boolean string.
[0115] The filtering result is determined based on the assigned Boolean strings.
[0116] Based on the same idea, one or more embodiments of this specification also provide corresponding... Figure 1 A non-volatile computer storage medium according to the method of the Chinese method, wherein the database table is stored through multiple data sets, the multiple data sets including a baseline data set and an incremental data set, and the medium stores computer-executable instructions, the computer-executable instructions being configured as follows:
[0117] The data columns involved in the set filtering conditions in the database table are determined as target columns. The database table has a corresponding Boolean string for each data set, and the Boolean bits in the Boolean string correspond to the virtual row number of the data row in the corresponding data set.
[0118] For each data row corresponding to the target column, perform the following: search in the incremental data set and the baseline data set to determine the Boolean string corresponding to the data set where the latest data of the data row is located; determine whether the latest data meets the filtering condition; and based on the determination result, assign a virtual row number of the data row in the data set where it is located, and assign a value to the corresponding Boolean bit in the Boolean string.
[0119] The filtering result is determined based on the assigned Boolean strings.
[0120] 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 also 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 also 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.
[0121] 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: ARC 625D, 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, application-specific integrated circuits, 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.
[0122] 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.
[0123] 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.
[0124] 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.
[0125] 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, generate instructions 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.
[0126] 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.
[0127] 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.
[0128] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.
[0129] 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.
[0130] 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.
[0131] 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.
[0132] 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.
[0133] 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.
[0134] 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.
[0135] 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 database table scanning method, wherein the database table is stored through multiple data sets, the multiple data sets including a baseline data set and an incremental data set, the method comprising: The data columns involved in the set filtering conditions in the database table are determined as target columns. The database table has a corresponding Boolean string for each data set, and the Boolean bits in the Boolean string correspond to the virtual row number of the data row in the corresponding data set. For each data row corresponding to the target column, perform the following: search in the incremental data set and the baseline data set to determine the Boolean string corresponding to the data set where the latest data of the data row is located; determine whether the latest data meets the filtering condition; and based on the determination result, assign a virtual row number of the data row in the data set where it is located, and assign a value to the corresponding Boolean bit in the Boolean string. The filtering result is determined based on the assigned Boolean strings. When the filtering condition is a composite filtering condition, the step of determining whether the latest data meets the filtering condition includes: performing Boolean bit operations based on the assignment of Boolean bits corresponding to each individual filtering condition to determine whether the composite filtering condition is met. The process of determining the filtering result includes: merging the virtual row numbers corresponding to the first value of the Boolean bits in the Boolean strings corresponding to the baseline data set and the incremental data set, and outputting the data rows in order as the filtering result.
2. The method as described in claim 1, wherein assigning a value to the Boolean bit corresponding to the virtual row number of the data row in the data set within the Boolean string based on the judgment result specifically includes: If the judgment result is yes, then the virtual row number of the data row in the data set is given, and the corresponding Boolean bit in the Boolean string is assigned the first value to indicate that it is retained after filtering; If the judgment result is negative, then the virtual row number of the data row in the data set is used, and the corresponding Boolean bit in the Boolean string is assigned the second value to indicate that it is discarded after filtering.
3. The method of claim 1 or 2, wherein after searching in the incremental data set and the baseline data set, the method further comprises: Determine whether the latest data exists in the incremental data set or in the baseline data set; If the data of the same row exists in both the incremental data set and the baseline data set, then the Boolean string corresponding to the baseline data set is determined as the virtual row number of the data row in the baseline data set. The Boolean bit in the Boolean string is assigned a second value to indicate that the data is discarded after filtering.
4. The method as described in claim 1, wherein the filtering condition is a composite filtering condition involving multiple data columns of the database table; The determination of whether the latest data meets the filtering conditions specifically includes: Determine the same Boolean bit and assign values to the individual filtering conditions contained in the composite filtering condition, respectively. Based on the composite operation of the multiple individual filtering conditions in the composite filtering conditions, Boolean bitwise operations are performed between the corresponding assignments accordingly; Based on the result of the Boolean bitwise operation, determine whether the latest data satisfies the composite filtering condition.
5. The method as described in claim 1 or 2, after determining the boolean string corresponding to the data set containing the latest data of the data row, the method further includes: Determine if the latest data in the given data row contains a deletion marker; If so, then the virtual row number of the data row in the data set is used, and the corresponding Boolean bit in the Boolean string is assigned the second value to indicate that it will be discarded after filtering.
6. The method as described in claim 2, wherein determining the filtering result based on each of the assigned Boolean strings specifically includes: The data rows corresponding to the Boolean bits that take the first value in each of the assigned Boolean strings are determined as reserved data rows; The filtering results are determined based on each of the reserved data rows.
7. The method as described in claim 2 or 6, wherein determining the filtering result specifically includes: In the Boolean string corresponding to the baseline data set, determine the virtual row number corresponding to the first Boolean bit that takes the first value, and use it as the first row number; In the Boolean string corresponding to the incremental data set, determine the virtual row number corresponding to the first Boolean bit that takes the first value, and use it as the second row number; Determine the first data row identifier corresponding to the first row number and the second data row identifier corresponding to the second row number. Both the first data row identifier and the second data row identifier are primary keys or both are physical addresses. Compare the size of the first data row identifier and the second data row identifier, and take the data row corresponding to the smaller one as the reserved data row; Determine the virtual row number corresponding to the next Boolean bit with the value of the first value for the smaller one, so as to continue the above comparison and data row retrieval process until the Boolean strings corresponding to the baseline data set and the incremental data set are processed. Each of the reserved data rows is determined as the filtering result.
8. The method of claim 1, wherein the data row is snapshot data.
9. A database table scanning apparatus, wherein the database table is stored through multiple data sets, the multiple data sets including a baseline data set and an incremental data set, the apparatus comprising: The target column determination module determines the data columns involved in the set filtering conditions in the database table as target columns. The database table has a corresponding Boolean string for each data set, and the Boolean bits in the Boolean string correspond to the virtual row number of the data row in the corresponding data set. The target column scanning module performs the following steps for each data row corresponding to the target column: searching in the incremental data set and the baseline data set to determine the Boolean string corresponding to the data set where the latest data of the data row is located; determining whether the latest data meets the filtering conditions; and assigning a value to the corresponding Boolean bit in the Boolean string based on the determination result. The filtering result determination module determines the filtering result based on the assigned Boolean strings. When the filtering condition is a composite filtering condition, the step of determining whether the latest data meets the filtering condition includes: performing Boolean bit operations based on the assignment of Boolean bits corresponding to each individual filtering condition to determine whether the composite filtering condition is met. The process of determining the filtering result includes: merging the virtual row numbers corresponding to the first value of the Boolean bits in the Boolean strings corresponding to the baseline data set and the incremental data set, and outputting the data rows in order as the filtering result.
10. The apparatus of claim 9, wherein if the determination result of the target column scanning module is yes, the virtual row number of the data row in the data set is assigned to the corresponding Boolean bit in the Boolean string as a first value to indicate that it is retained after filtering; If the judgment result is negative, then the virtual row number of the data row in the data set is used, and the corresponding Boolean bit in the Boolean string is assigned the second value to indicate that it is discarded after filtering.
11. The apparatus of claim 9 or 10, wherein the target column scanning module, after searching in the incremental data set and the baseline data set, determines whether the latest data exists in the incremental data set or in the baseline data set; If the data of the same row exists in both the incremental data set and the baseline data set, then the Boolean string corresponding to the baseline data set is determined as the virtual row number of the data row in the baseline data set. The Boolean bit in the Boolean string is assigned a second value to indicate that the data is discarded after filtering.
12. The apparatus of claim 9, wherein the filtering conditions are composite filtering conditions involving multiple data columns of the database table; The target column scanning module determines the same Boolean bit and assigns values to the multiple individual filtering conditions included in the composite filtering condition, respectively. Based on the composite operation of the multiple individual filtering conditions in the composite filtering conditions, Boolean bitwise operations are performed between the corresponding assignments accordingly; Based on the result of the Boolean bitwise operation, determine whether the latest data satisfies the composite filtering condition.
13. The apparatus of claim 9 or 10, wherein the target column scanning module, after determining the boolean string corresponding to the data set containing the latest data of the data row, determines whether the latest data of the data row contains a deletion marker; If so, then the virtual row number of the data row in the data set is used, and the corresponding Boolean bit in the Boolean string is assigned the second value to indicate that it will be discarded after filtering.
14. The apparatus of claim 10, wherein the filtering result determining module determines the data rows corresponding to the Boolean bits that take the first value in each of the assigned Boolean strings as reserved data rows; The filtering results are determined based on each of the reserved data rows.
15. The apparatus of claim 10 or 14, wherein the filtering result determining module determines, in the Boolean string corresponding to the first Boolean bit that takes the first value, as the first row number; In the Boolean string corresponding to the incremental data set, determine the virtual row number corresponding to the first Boolean bit that takes the first value, and use it as the second row number; Determine the first data row identifier corresponding to the first row number and the second data row identifier corresponding to the second row number. Both the first data row identifier and the second data row identifier are primary keys or both are physical addresses. Compare the size of the first data row identifier and the second data row identifier, and take the data row corresponding to the smaller one as the reserved data row; Determine the virtual row number corresponding to the next Boolean bit with the value of the first value for the smaller one, so as to continue the above comparison and data row retrieval process until the Boolean strings corresponding to the baseline data set and the incremental data set are processed. Each of the reserved data rows is determined as the filtering result.
16. The apparatus of claim 9, wherein the data row is snapshot data.
17. A database table scanning device, wherein the database table is stored through multiple data sets, the multiple data sets including a baseline data set and an incremental data set, the device comprising: At least one processor; as well as, A memory communicatively connected to the at least one processor; wherein, 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: The data columns involved in the set filtering conditions in the database table are determined as target columns. The database table has a corresponding Boolean string for each data set, and the Boolean bits in the Boolean string correspond to the virtual row number of the data row in the corresponding data set. For each data row corresponding to the target column, perform the following: search in the incremental data set and the baseline data set to determine the Boolean string corresponding to the data set where the latest data of the data row is located; determine whether the latest data meets the filtering condition; and based on the determination result, assign a virtual row number of the data row in the data set where it is located, and assign a value to the corresponding Boolean bit in the Boolean string. The filtering result is determined based on the assigned Boolean strings. When the filtering condition is a composite filtering condition, the step of determining whether the latest data meets the filtering condition includes: performing Boolean bit operations based on the assignment of Boolean bits corresponding to each individual filtering condition to determine whether the composite filtering condition is met. The process of determining the filtering result includes: merging the virtual row numbers corresponding to the first value of the Boolean bits in the Boolean strings corresponding to the baseline data set and the incremental data set, and outputting the data rows in order as the filtering result.