A filtering condition rewriting method, device, equipment, medium and product

Abstract

The application provides a filtering condition rewriting method, device, equipment, medium and product, comprising: obtaining a filtering condition; when the filtering condition meets an optimization condition, splitting the filtering condition into a condition unit; when the condition unit is a Boolean expression containing a column name and a constant, determining a current partition table in which a target column corresponding to the column name is located and a current partition column of the current partition table, determining an optimization mark of the condition unit by comparing the current partition column with the target column; if the optimization mark of the condition unit is first mark information and a partition base table of the current partition table of the condition unit is a range partition table, obtaining the constant and a comparison operator in the condition unit, selecting a current lower-level partition table from lower-level partition tables of the partition base table, and determining optimization indication information of the condition unit according to the constant, the comparison operator and partition information of the current lower-level partition table; and rewriting based on the optimization indication information to obtain a rewritten filtering condition. The method improves the query efficiency of the filtering condition.

Description

Technical Field

[0001] This invention relates to the field of partitioned cropping optimization technology, and in particular to a method, apparatus, equipment, medium and product for rewriting filter conditions. Background Technology

[0002] Range partitioning is a commonly used database partitioning technique. Its core is to divide the data in a table into multiple independent physical storage units based on a specified current partition column.

[0003] When querying range-partitioned tables, Structured Query Language (SQL) is typically used, and filtering conditions are employed to prune partitions, accessing only subsets of partitions that meet the specified criteria, thereby improving query efficiency. In this technique, query efficiency is highly dependent on the choice of filtering conditions; different filtering conditions lead to different partition pruning effects, thus impacting query execution time and efficiency. Therefore, optimizing filtering conditions to achieve more accurate and efficient partition pruning is a key issue that needs to be addressed. Summary of the Invention

[0004] This invention provides a method, apparatus, device, medium, and product for rewriting filter conditions to improve the query efficiency of filter conditions.

[0005] According to one aspect of the present invention, a method for rewriting filter conditions is provided, comprising:

[0006] Obtain the filtering conditions in the structured query statement, which includes a statement that queries a range of partitioned tables;

[0007] If the filtering conditions satisfy the optimization conditions, the filtering conditions are divided into condition units. The condition unit includes logical operation units that cannot be further divided by delimiters in the filtering conditions, and the condition unit does not contain member conditions.

[0008] For each condition unit, when the condition unit is a Boolean expression containing column names and constants, determine the current partition table where the target column corresponding to the column name is located, determine the current partition column of the current partition table, and determine the optimization mark of the condition unit by comparing the current partition column with the target column;

[0009] For each condition unit, if the optimization flag corresponding to the condition unit is the first flag information, and the partition base table of the current partition table corresponding to the condition unit is a range partition table, then the constant and comparison operator in the condition unit are obtained, the current lower-level partition table is selected from the lower-level partition table of the partition base table, and the optimization indication information of the condition unit is determined according to the constant, the comparison operator and the partition information of the current lower-level partition table. The first flag information indicates that the condition unit is to be optimized.

[0010] The filtering conditions are rewritten based on the optimization indication information to obtain the rewritten filtering conditions.

[0011] According to another aspect of the present invention, a filter condition rewriting apparatus is provided, comprising:

[0012] The acquisition module is used to acquire the filtering conditions in the structured query statement, which includes a statement for querying a range partitioned table;

[0013] The segmentation module is used to segment the filtering conditions into condition units when the filtering conditions meet the optimization conditions. The condition unit includes logical operation units in the filtering conditions that cannot be further divided by delimiters, and the condition unit does not contain member conditions.

[0014] The determination module is used to determine the current partition table where the target column corresponding to the column name is located when the condition unit is a Boolean expression containing column name and constant for each condition unit, determine the current partition column of the current partition table, and determine the optimization mark of the condition unit by comparing the current partition column with the target column.

[0015] An optimization module is configured to, for each condition unit, if the optimization flag corresponding to the condition unit is the first flag information and the partition base table of the current partition table corresponding to the condition unit is a range partition table, obtain the constant and comparison operator in the condition unit, select the current lower-level partition table from the lower-level partition table of the partition base table, and determine the optimization indication information of the condition unit based on the constant, the comparison operator and the partition information of the current lower-level partition table, wherein the first flag information indicates that the condition unit is to be optimized;

[0016] The rewriting module is used to rewrite the filtering conditions based on the optimization indication information to obtain the rewritten filtering conditions.

[0017] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising:

[0018] At least one processor; and

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

[0020] The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform a filter condition rewriting method according to any embodiment of the present invention.

[0021] According to another aspect of the present invention, a computer-readable storage medium is provided, the computer-readable storage medium storing computer instructions for causing a processor to execute and implement a filtering condition rewriting method according to any embodiment of the present invention.

[0022] According to another aspect of the present invention, a computer program product is provided, the computer program product comprising a computer program that, when executed by a processor, implements the method described in any embodiment of the present invention.

[0023] The technical solution of this invention involves obtaining filtering conditions from a structured query statement; when the filtering conditions satisfy optimization conditions, dividing the filtering conditions into condition units; for each condition unit, if the condition unit is a Boolean expression containing column names and constants, determining the current partition table where the target column corresponding to the column name is located, determining the current partition column of the current partition table, and determining the optimization flag of the condition unit by comparing the current partition column with the target column; only rewriting and optimizing filtering conditions that satisfy the conditions to improve query efficiency; for each condition unit, if the optimization flag corresponding to the condition unit is the first flag information, and the partition base table of the current partition table corresponding to the condition unit is... For the range partition table, the constants and comparison operators in the condition unit are obtained. The current lower-level partition table is selected from the lower-level partition table of the partition base table. Based on the constants, the comparison operators, and the partition information of the current lower-level partition table, the optimization indication information of the condition unit is determined. By determining the intersection relationship between the range of the current lower-level partition table and the range of the filtering conditions, it is determined whether to optimize the condition unit and what kind of optimization to perform. This completes the partition pruning of the data table, reduces the amount of data processing, further improves query efficiency, and reduces the computational resource overhead of the system. Based on each of the optimization indication information, the filtering conditions are rewritten to obtain the rewritten filtering conditions, thus completing the rewriting and optimization of the filtering conditions and improving the accuracy of partition pruning.

[0024] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description

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

[0026] Figure 1 A flowchart of a method for rewriting filter conditions is provided in Embodiment 1 of the present invention;

[0027] Figure 2 This is a flowchart of a filter condition rewriting method provided in Embodiment 2 of the present invention;

[0028] Figure 3 A flowchart of a filter condition rewriting method provided in Embodiment 3 of the present invention;

[0029] Figure 4 This is a schematic diagram of a filter condition rewriting device provided in Embodiment 4 of the present invention;

[0030] Figure 5 A structural block diagram of an electronic device that can be used to implement embodiments of the present invention is shown. Detailed Implementation

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

[0032] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0033] Structured Query Language (SQL) is the most important and commonly used language for manipulating relational databases.

[0034] Horizontal partitioning is a core strategy for database table partitioning. It splits a logically large table into multiple physically stored sub-tables, each containing a subset of rows from the original table and sharing the same table structure. Horizontal partitioning primarily includes range partitioning, list partitioning, and hash partitioning. These types can be combined arbitrarily to further partition the resulting sub-tables, creating multi-level partitioned tables.

[0035] Range partitioning creates partitions based on a range of values ​​in one or more columns. When a user writes data to a table, the database server will determine the appropriate partition based on the values ​​in these columns.

[0036] When performing structured queries on partitioned subtables, there are often filtering conditions applied to column data. Since range-partitioned tables are partitioned according to the value range of the partition column, when range-related filtering conditions exist on the partition column, the following situation may occur: all data in some partitioned subtables meets the condition, while all data in other partitioned subtables does not meet the condition. In such cases, the filtering conditions on the partitioned table are essentially always true or always false, so there is no need to evaluate each piece of data in the table individually.

[0037] For example, create a range partition table T3 with partition column C2, containing three sub-tables P1, P2, and P3. Partition P1 stores data where C2 < 10, partition P2 stores data where 10 <= C2 < 20, and partition P3 stores data where C2 >= 20. The three partitions do not overlap.

[0038] Execute the SQL query statement: Query all records in partition table T3 whose C2 column value is less than 18 from partition P1.

[0039] Since the partition column and the filter column of T3_P1 are the same, both being C2, and C2<18 completely includes C2<10, all data in T3_P1 satisfies this filter condition. Therefore, this filter condition can be optimized to TRUE, which is equivalent to the original statement becoming: unconditionally query all data columns and all rows from table T3_P1.

[0040] Example 1

[0041] Figure 1This is a flowchart illustrating a filter condition rewriting method according to Embodiment 1 of the present invention. This embodiment is applicable to optimizing filter conditions. The method can be executed by a filter condition rewriting device, which can be implemented in hardware and / or software and can be configured in an electronic device. The electronic device can be a computer, personal digital assistant, or server, etc. Figure 1 As shown, the method includes:

[0042] S110. Obtain the filtering conditions in the structured query statement, which includes a statement for querying a range partitioned table.

[0043] In this embodiment, the structured query statement can be a query statement composed of SQL. The structured query statement is used to query data in the database that meets the filtering conditions. The filtering conditions can be conditional statements used to filter and limit the query results. When querying a range partitioned table, using filtering conditions can quickly locate the relevant data partitions, reducing query time. A range partitioned table can be a data table divided according to the continuous value range of a key column. The range data table can be divided into multiple sub-tables based on the continuous value range of the key column, and the sub-tables are arranged in order of the size of the continuous value range of the key column.

[0044] Specifically, the process involves obtaining a structured query statement, parsing the structured query statement, and extracting the filtering conditions contained within the structured query statement. The structured query statement is a query statement for a range-partitioned table. The structured query statement can be input by the database user or generated by the database; this invention does not impose any restrictions on this.

[0045] S120. If the filtering condition satisfies the optimization condition, the filtering condition is divided into condition units. The condition unit includes logical operation units in the filtering condition that cannot be further divided by a delimiter, and the condition unit does not contain member conditions.

[0046] In this embodiment, the optimization condition can be a judgment condition that evaluates whether the filter condition can be rewritten. The optimization condition can be, for example, whether the format and / or content of the filter condition meets the requirements. The condition unit can be the basic unit obtained after the filter condition is divided by a delimiter. The condition unit includes the numerical range limited by the query, represented in a form that includes comparison operators (such as <, >, or =). The delimiter can be a logical operator used to divide the filter condition. Delimiters include, but are not limited to, AND or OR. The membership condition can be a condition used to query whether a member (which can be represented as a constant) exists in a specific data column of the range partition table. For example, the membership condition can be in the form IN(0,1), where the members are 0 and 1.

[0047] Specifically, when the format and / or content of the filter condition meets the requirements of the optimization conditions, the filter condition is divided into at least one condition unit according to the delimiter. It is worth noting that when there are member conditions in the filter condition, the member conditions are replaced with the equality expression for each member in the expression when splitting the filter condition.

[0048] For example, the expression IN(0,1) contains members 0 and 1, which are replaced with =0 and =1 when splitting the filtering conditions.

[0049] Optionally, the optimization conditions include: the filtering condition is a comparison between the partition column and a constant value, and / or, a comparison between the partition column and a member condition.

[0050] In this embodiment, the partition column can be at least one specific data column in the range partitioning table that is designated as the basis for data partitioning. The partition column of the range partitioning table can be obtained by looking up metadata in the database. The specific value in the partition column, i.e., the partition value, has a range that serves as the basis for dividing the data into partitions.

[0051] Specifically, the optimization conditions include: filtering conditions such as whether the partition value in the query partition column belongs to a certain constant value range, and / or whether the partition value in the query partition column is equal to the member value in the member condition.

[0052] For example, table T1 has C1 as the partition column for its first-level partition and C2 as the partition column for its second-level partition. The filter condition is to query all records from the sub-partition P1_P11 (denoted as T1_P1_P11) of table T1 that simultaneously satisfy C1<15 AND C2 IN (0,1). In the filter condition, C1<15 satisfies the optimization condition, which is a comparison between the partition column and a constant value; in the filter condition, C2 IN (0,1) satisfies the optimization condition, which is a comparison between the partition column and a member condition. Since the filter condition satisfies the optimization condition, it can be split. The delimiter is AND, and this filter condition can be split into three condition units: C1<15, C2=0, and C2=1.

[0053] S130. For each condition unit, when the condition unit is a Boolean expression containing column names and constants, determine the current partition table where the target column corresponding to the column name is located, determine the current partition column of the current partition table, and determine the optimization mark of the condition unit by comparing the current partition column with the target column.

[0054] In this embodiment, the column name can be the name of the data column to be queried, as indicated by the condition unit. By judging whether the values ​​in the data column meet the conditions indicated by the condition unit, the data rows corresponding to the values ​​that meet the conditions are filtered out and output as the query results. The target column can be a specific data column identified by its column name. Through the target column indicated by the column name, the range partition table where the target column is located can be located, i.e., the current partition table, and the partition column of the current partition table is the current partition column. The optimization flag can be a flag indicating whether the current partition table can be queried according to the condition unit. The optimization flag can be of Boolean type.

[0055] Specifically, it iterates through all condition cells, checking if the current condition cell is a Boolean expression containing column names and constants. If not, it iterates to the next condition cell, leaving the current condition cell unchanged. If it is, it searches the database for the target column corresponding to the column name in the condition cell, along with the current partition table containing the target column, based on the column name in the condition cell. It then determines the current partition column of the current partition table. If the current partition column and the target column indicated by the column name in the condition cell are the same data column, it sets an optimization flag for the current condition cell, indicating that the current condition cell can be modified. If they are not the same data column, it sets an optimization flag for the current condition cell, indicating that the current condition cell should be retained without modification.

[0056] S140. For each condition unit, if the optimization flag corresponding to the condition unit is the first flag information, and the partition base table of the current partition table corresponding to the condition unit is a range partition table, then obtain the constant and comparison operator in the condition unit, select the current lower-level partition table from the lower-level partition table of the partition base table, and determine the optimization indication information of the condition unit according to the constant, the comparison operator and the partition information of the current lower-level partition table, wherein the first flag information indicates that the condition unit is to be optimized.

[0057] In this embodiment, the first marker information can be information indicating that the current partition column and the target column are the same data column. The first marker information can be a Boolean type, such as TRUE. The partition base table can be the logical master table of the range partition table. The partition base table is divided into multiple lower-level partition tables according to the numerical range of the partition values ​​in the partition columns. The lower-level partition tables can be data tables of a subset of the partition base table. The number of lower-level partition tables is at least one. The partition information can be information describing the lower-level partition tables. The lower-level partition tables include, but are not limited to: the table identifier of the lower-level partition table, the partition value limits of the partition columns of the lower-level partition table, and an identifier indicating whether the lower-level partition table contains partition value limits. The optimization indication information can be information indicating that the condition unit can be rewritten. The optimization indication information can be a Boolean type, such as TRUE or FALSE.

[0058] Specifically, if the optimization flag corresponding to the condition unit is the first flag information, then the partition base table is determined based on the current partition table. If the partition base table is a range partition table, then the current condition unit is parsed to obtain the constants and comparison operators in that condition unit. (From the metaarray) all lower-level partition tables of the partition base table are found, and the partition information of each corresponding lower-level partition table in the metaarray is obtained. The partition value range indicated by the partition information of the lower-level partition table is compared with the range of the condition unit indicated by the constants and comparison operators in the condition unit. Based on the intersection of the two ranges, the optimization indication information of the current condition unit is determined. When the intersection of two ranges is non-intersecting, the optimization instruction indicates that the current lower-level partition table does not contain partition values ​​that satisfy the current condition unit, and the current lower-level partition table is not queried. When the intersection of two ranges is such that the partition value range is a subset of the condition unit's range, the optimization instruction indicates that all data in the current lower-level partition table satisfies the query conditions indicated by the condition unit, and the current lower-level partition is returned directly without querying the current lower-level partition table. When the intersection of two ranges is partially intersecting, the optimization instruction indicates that the current condition unit is not rewritten and is retained.

[0059] S150. The filtering conditions are rewritten based on the optimization indication information to obtain the rewritten filtering conditions.

[0060] Specifically, all condition units are traversed, and each condition unit is rewritten one by one according to the optimization instructions. After all the rewriting is completed, the rewritten filter conditions are obtained.

[0061] The technical solution of this invention involves obtaining filtering conditions from a structured query statement; when the filtering conditions satisfy optimization conditions, dividing the filtering conditions into condition units; for each condition unit, if the condition unit is a Boolean expression containing column names and constants, determining the current partition table where the target column corresponding to the column name is located, determining the current partition column of the current partition table, and determining the optimization flag of the condition unit by comparing the current partition column with the target column; only rewriting and optimizing filtering conditions that satisfy the conditions to improve query efficiency; for each condition unit, if the optimization flag corresponding to the condition unit is the first flag information, and the partition base table of the current partition table corresponding to the condition unit is... For the range partition table, the constants and comparison operators in the condition unit are obtained. The current lower-level partition table is selected from the lower-level partition table of the partition base table. Based on the constants, the comparison operators, and the partition information of the current lower-level partition table, the optimization indication information of the condition unit is determined. By determining the intersection relationship between the range of the current lower-level partition table and the range of the filtering conditions, it is determined whether to optimize the condition unit and what kind of optimization to perform. This completes the partition pruning of the data table, reduces the amount of data processing, further improves query efficiency, and reduces the computational resource overhead of the system. Based on each of the optimization indication information, the filtering conditions are rewritten to obtain the rewritten filtering conditions, thus completing the rewriting and optimization of the filtering conditions and improving the accuracy of partition pruning.

[0062] Example 2

[0063] Figure 2 This is a flowchart illustrating a filtering condition rewriting method according to Embodiment 2 of the present invention. This embodiment is an optimization based on any of the above embodiments, and mainly includes a detailed description of the determination process of the condition unit, optimization mark, partition base table, and current partition table, the judgment process of the positioning attribute, and the rewriting and simplification process of the filtering conditions. It should be noted that technical details not described in detail in this embodiment can be found in any of the above embodiments. Figure 2 As shown, the method includes:

[0064] S210. Obtain the filtering conditions in the structured query statement, wherein the structured query statement includes a statement for querying a range partition table.

[0065] S220. The filtering conditions are divided using the delimiter in the filtering conditions to obtain initial segmentation units.

[0066] In this embodiment, the initial segmentation unit can be a unit obtained after preliminary segmentation based on the delimiter. If the initial segmentation unit contains membership conditions, it needs to be further segmented.

[0067] Specifically, the filtering conditions are divided according to the delimiters in the filtering conditions to obtain at least one initial segmentation unit.

[0068] For example, the filtering condition is C1<15 AND C2 IN (0,1), the delimiter is AND, and the initial splitting unit is C1<15 and C2 IN (0,1).

[0069] S230. For each initial segmentation unit, if the initial segmentation unit contains member conditions, then the member conditions are rewritten as an expression composed of constants and columns in the member conditions, and the rewritten expression is used as a condition unit; otherwise, the initial segmentation unit is used as a condition unit.

[0070] Specifically, if the initial partitioning unit includes member conditions, it is rewritten as an expression consisting of the numerical constants of each member in the member conditions and the column names, and the rewritten expression is used as the condition unit. If it does not include member conditions, the initial partitioning unit is used as the condition unit.

[0071] For example, the initial segmentation units are C1<15 and C2 IN (0,1). C1<15 does not include member conditions, so C1<15 is used as the condition unit. C2 IN (0,1) includes member conditions, and the numerical constants of the members are 0 and 1, so it is rewritten as C2=0 and C2=1. C2=0 and C2=1 do not include member conditions, so C2=0 and C2=1 are used as the condition units. The final condition units are: C1<15, C2=0, and C2=1.

[0072] S240. Determine whether the current partition column is the same as the target column.

[0073] Specifically, it determines whether the current partition column of the current partition table is the same as the target column indicated by the condition cell.

[0074] S250. If so, determine the optimization flag of the condition unit as the first flag information, and determine the parent partition table of the current partition table as the partition base table of the current partition table, and execute S280.

[0075] Specifically, if the target column is the same as the current partition column, the optimization flag is set as the first flag information, and the parent partition table of the current partition table found in the metadata is determined as the partition base table.

[0076] S260. If not, determine whether the current partition table is the partition root table. If yes, determine the optimization mark of the condition unit as the second mark information. Otherwise, take the parent partition table of the current partition table as the new current partition table, and return to continue comparing the current partition column of the new current partition table with the target column to determine the optimization mark of the condition unit, and execute S2120.

[0077] In this embodiment, the partition root table can be a table without a parent partition table. The partition root table can be the top-level logical master table in the range partition table hierarchy. The second marker information can be information indicating that the current partition column and the target column are not the same data column. The second marker information can be a Boolean type, such as FALSE.

[0078] Specifically, if the target column and the current partition column are not the same column, the parent partition table of the current partition table is searched from the metadata. If no parent partition table exists, the current partition table is the partition root table, and the optimization mark is set to the second mark information. If the parent partition table is found, the parent partition table is used as the current partition table, its current partition column is obtained, and the determination of whether the target column and the current partition column are the same column is continued until they are the same column or the current partition table is the partition root table, and the optimization mark of all conditional units is determined.

[0079] For example, a range partition table T1 is created, containing three sub-partitions divided by the integer values ​​in column C1: P1 (C1<10), P2 (10≤C1<20), and P3 (C1≥20). Each sub-partition contains two next-level sub-partitions divided by column C2: P11 (C2<1) and P12 (C2≥1). The condition cells are C1<15, C2=0, and C2=1, and the current partition table is T1_P1_P11. For the condition cell indicating C1<15, the target column is C1, and the current partition column of T1_P1_P11 is C2. Since they are different, the parent partition table of T1_P1_P11 is found to be T1_P1. T1_P1 is used as the current partition table, and the current partition column is C1, which is the same as the target column. Therefore, the partition base table is set to the parent partition table of T1_P1, which is T1, and the optimization mark of the condition cell is determined as the first mark information. For a condition cell indicating C2=0, the target column is C2, and the current partition column of T1_P1_P11 is also C2. Since they are the same, the parent partition table T1_P1 of T1_P1_P11 is set as the partition base table, the optimization flag of the condition cell is determined to be the first flag information, and the current partition table is T1_P1_P11. Similarly, for a condition cell indicating C2=1, the partition base table is T1_P1, and the optimization flag of the condition cell is determined to be the first flag information.

[0080] S270, the second marking information indicates that the condition unit cannot be optimized, and S2120 is executed.

[0081] Specifically, when the optimization flag of a conditional unit is the second flag information, the conditional unit is retained and not rewritten.

[0082] S280. For each condition unit, if the optimization flag corresponding to the condition unit is the first flag information, and the partition base table of the current partition table corresponding to the condition unit is a range partition table, then obtain the constant and comparison operator in the condition unit. If the table name of the current lower-level partition table is the same as the table name of the current partition table, then determine the positioning attribute of the current lower-level partition table as the first attribute; otherwise, determine the positioning attribute of the current lower-level partition table as the second attribute.

[0083] The partition information includes the table name, partition value limits, and boundary value markers.

[0084] In this embodiment, the location attribute can be an attribute indicating whether the current partition table is located. The location attribute can be a first attribute or a second attribute. The first attribute can be an attribute indicating that the current partition table is located. In this case, the current lower-level partition table is the current partition table. The first attribute can be a Boolean type, such as TRUE. The second attribute can be an attribute indicating that the current partition table is not located. In this case, the current lower-level partition table is not the current partition table. The second attribute can be a Boolean type, such as FALSE. The partition value limit can be the upper limit of the partition value range of the lower-level partition table. Since the sub-tables of the range partition table are arranged in order of partition value range, in two adjacent lower-level partition tables, the partition value limit of the previous lower-level partition table is definitely not greater than the partition value limit of the next lower-level partition table. The partition value limit of the previous lower-level partition table is the lower limit of the partition value range of the next lower-level partition table. The boundary value marker can be a marker indicating whether the lower-level partition table contains a partition value limit (i.e., a boundary value). The boundary value marker can be a Boolean type.

[0085] Specifically, iterate through all the lower-level partition tables under the base partition table, set the currently traversed lower-level partition table as the current lower-level partition table, and determine whether the positioning attribute of the current partition table has been located by checking whether the table name of the current partition table is the same as the table name of the current lower-level partition table. Then, set the positioning attribute of the current lower-level partition table to the first attribute or the second attribute.

[0086] S290. Compare the constant with the partition value boundary to obtain the comparison result.

[0087] Specifically, the constant is compared with the partition value limit to obtain the comparison result. If the comparison result indicates that the constant is greater than or less than the partition value limit, execute S2100. If the comparison result indicates that the constant is equal to the partition value limit, execute S2110.

[0088] S2100: When the comparison result indicates that the constant is greater than or less than the partition value limit, determine the optimization indication information of the condition unit according to the positioning attribute of the current lower-level partition table and the comparison operator, and execute S2120.

[0089] Specifically, when the comparison result indicates that the constant is greater than or less than the partition value limit: if the comparison result indicates that the constant is greater than the partition value limit, all lower-level partition tables need to be traversed sequentially until the positioning attribute indicates that the current lower-level partition table is located in the current partition table, before the intersection relationship between the partition value range of the current lower-level partition table and the range of the condition cell can be determined; if the comparison result indicates that the constant is less than the partition value limit, regardless of whether the current partition table is located, the intersection relationship between the partition value range of the lower-level partition table and the range of the condition cell can be determined. Based on the intersection relationship, optimization indication information is determined.

[0090] S2110. When the comparison result indicates that the constant is equal to the partition value limit, the optimization indication information of the condition unit is determined according to the positioning attribute of the current lower-level partition table, the boundary value marker and the comparison operator, wherein the boundary value marker indicates whether the current lower-level partition table contains a boundary value.

[0091] Specifically, when the comparison result indicates that the constant is equal to the partition value boundary, it is necessary to combine the positioning attributes, boundary value markers, and comparison operators to determine the intersection relationship between the partition value range of the lower-level partition table and the range of the condition unit. Based on the intersection relationship, the optimization indication information is determined.

[0092] S2120. For each condition unit in the filtering conditions, if the optimization instruction information indicates that the condition unit should not be rewritten, then the original expression corresponding to the condition unit is retained in the filtering conditions; otherwise, one of the operations S2130 or S2140 is performed.

[0093] In this embodiment, the original expression can be the unmodified expression in the condition unit. The original expression includes the partition column, comparison operators, and constants.

[0094] Specifically, when the condition unit of the optimization instruction information is not rewritten, the original expression in the condition unit is retained and not modified. If the condition unit of the optimization instruction information needs to be rewritten, S2130 or S2140 is executed.

[0095] S2130. When the original expression corresponding to the condition unit is a Boolean expression, and the optimization indication information indicates that the expression of the condition unit is a first expression or a second expression, replace the original expression corresponding to the condition unit in the filtering conditions with the indicated expression, and execute S2150.

[0096] In this embodiment, the first expression can be an expression indicating that the current lower-level partition table does not meet the query conditions indicated by the condition unit, and therefore the current lower-level partition table is not queried. When the optimization instruction information indicates that the expression of the condition unit is the first expression, the current lower-level partition table is not queried, and the query result does not include the lower-level partition table. The second expression can be an expression indicating that all contents in the current lower-level partition table meet the query conditions indicated by the condition unit, and therefore the current lower-level partition table is not queried. When the optimization instruction information indicates that the expression of the condition unit is the second expression, the current lower-level partition table is not queried, and the query result includes the lower-level partition table.

[0097] Specifically, when the original expression corresponding to the condition unit is a Boolean expression, and the optimization indication information indicates that the expression of the condition unit is a first expression or a second expression, the first expression or the second expression is used to replace the original expression.

[0098] S2140: When the original expression corresponding to the condition unit is a member condition, and the optimization indication information indicates that the expression of the condition unit is the first expression, the constants included in the condition unit are removed from the corresponding positions in the filtering conditions, and S2150 is executed.

[0099] Specifically, when the original expression is a member condition and the optimization indicator indicates that the expression of the condition unit is the first expression, it means that the range partition table to be queried must not contain the member value in the member condition. At this time, the constant (i.e. member value) included in the condition unit can be removed from the corresponding position (i.e. the corresponding member position) in the filter condition.

[0100] For example, the filtering condition is C2 IN (0,1), and the condition units are C2=0 and C2=1. When the condition unit is C2=1, the optimization indication information at this time is determined to be the first expression, i.e., FALSE. At this time, member 1 is directly deleted from the member condition, and the filtering condition is C2 IN (0).

[0101] S2150. Simplify the rewritten filtering conditions according to Boolean operation rules.

[0102] Specifically, if the rewritten condition unit is TRUE and the delimiter is OR, the filter condition can be simplified to TRUE, meaning that all content in the range lookup table indicated by the filter condition satisfies the condition unit, and the output query result includes the entire range lookup table; if the rewritten condition unit is TRUE and the delimiter is AND, the condition unit can be deleted, and only other condition units are queried; if the rewritten condition unit is FALSE and the delimiter is AND, the filter condition can be simplified to FALSE, meaning that all content in the range lookup table indicated by the filter condition does not satisfy the condition unit, and the output query result is 0; if the rewritten condition unit is FALSE and the delimiter is OR, the condition unit can be deleted, and only other condition units are queried.

[0103] The technical solution of this invention involves obtaining filtering conditions from a structured query statement; segmenting the filtering conditions using delimiters to obtain initial segmentation units; for each initial segmentation unit, if the initial segmentation unit contains member conditions, rewriting the member conditions as expressions composed of constants and columns in the member conditions, and using the rewritten expressions as condition units; otherwise, using the initial segmentation unit as a condition unit, ensuring that the segmented condition units are the most basic units, facilitating the rewriting of subsequent condition units, thereby improving query efficiency; and determining whether the current partition column is the same as the target column to determine if it is possible. The current partition table is rewritten based on conditional units. If so, the optimization flag of the conditional unit is determined as the first flag information, and the parent partition table of the current partition table is determined as the partition base table of the current partition table. If not, it is determined whether the current partition table is a partition root table. If so, the optimization flag of the conditional unit is determined as the second flag information. Otherwise, the parent partition table of the current partition table is taken as the new current partition table, and the comparison between the current partition column and the target column of the new current partition table is continued to determine the optimization flag of the conditional unit. The second flag information indicates that the conditional unit cannot be optimized, and the current partition table is further determined. Whether the rewrite conditions are met, reducing the computational resource overhead of the database system, and optimizing the resource utilization of the database system; if the table name of the current partition table is the same as the table name of the current lower-level partition table, then the positioning attribute of the current lower-level partition table is determined as the first attribute; otherwise, the positioning attribute of the current lower-level partition table is determined as the second attribute; a comparison is performed between the constant and the partition value limit to obtain a comparison result; when the comparison result indicates that the constant is greater than or less than the partition value limit, the optimization indication information of the condition unit is determined according to the positioning attribute of the current lower-level partition table and the comparison operator .... When the value equals the partition value limit, the optimization indication information of the condition unit is determined based on the positioning attribute of the current lower-level partition table, the boundary value marker, and the comparison operator. By judging the intersection relationship, the optimization indication information is determined, and the range partition table is selectively searched, which improves query efficiency and further reduces computational overhead. For each condition unit in the filtering condition, the original expression is selectively rewritten according to the optimization indication information, which simplifies the execution plan during the query and improves query efficiency. The rewritten filtering condition is simplified according to Boolean operation rules, which increases the stability of the query execution plan, eliminates redundant condition units, and improves query performance.

[0104] Example 3

[0105] Figure 3This is a flowchart of a filtering condition rewriting method provided in Embodiment 3 of the present invention. This embodiment is an optimization based on any of the above embodiments, and mainly includes a detailed description of the process. It should be noted that technical details not described in detail in this embodiment can be found in any of the above embodiments. Figure 3 As shown, the method includes:

[0106] S310. Obtain the filtering conditions in the structured query statement, wherein the structured query statement includes a statement for querying a range partition table.

[0107] S320. If the filtering condition satisfies the optimization condition, the filtering condition is divided into condition units. The condition unit includes logical operation units in the filtering condition that cannot be further divided by a delimiter, and the condition unit does not contain member conditions.

[0108] S330. For each condition unit, when the condition unit is a Boolean expression containing column names and constants, determine the current partition table where the target column corresponding to the column name is located, determine the current partition column of the current partition table, and determine the optimization mark of the condition unit by comparing the current partition column with the target column.

[0109] S340. For each condition unit, if the optimization flag corresponding to the condition unit is the first flag information, and the partition base table of the current partition table corresponding to the condition unit is a range partition table, then obtain the constant and comparison operator in the condition unit. If the table name of the current lower-level partition table is the same as the table name of the current partition table, then determine the positioning attribute of the current lower-level partition table as the first attribute; otherwise, determine the positioning attribute of the current lower-level partition table as the second attribute.

[0110] S350. Compare the constant with the partition value boundary to obtain the comparison result.

[0111] S360. When the comparison result indicates that the constant is greater than the partition value limit, if the positioning attribute is the second attribute, then the next lower-level partition table is taken as the new current lower-level partition table, and the optimization instruction information determination operation continues; if the positioning attribute is the first attribute, then when the comparison operator is equal to, greater than, or greater than or equal to, the optimization instruction information indicates that the expression of the condition unit is the first expression; when the comparison operator is not equal to, less than, or less than or equal to, the optimization instruction information indicates that the expression of the condition unit is the second expression, and S3120 is executed.

[0112] Specifically, if the location attribute is the second attribute, it means that the current partition table has not been located. Since the lower-level partition tables are arranged in ascending order according to the partition value limits, during sequential traversal, the partition limit value of the unlocated lower-level partition table will definitely not be greater than the partition value limit of the current partition table. When the comparison result indicates that the constant is greater than the partition value limit, the data that may meet the query conditions indicated by the condition unit may be located in a subsequent lower-level partition table with a larger partition value limit. Therefore, it is necessary to continue traversing until the location attribute is the first attribute before terminating the traversal. When the current partition table is located, the location attribute is the first attribute. When the comparison operator is equal to, greater than, or greater than or equal to, the partition value range does not intersect with the range indicated by the condition unit, and the expression of the condition unit is determined to be the first expression. When the comparison operator is not equal to, less than, or less than or equal to, the partition value range is a subset of the range indicated by the condition unit, and the expression of the condition unit is determined to be the second expression.

[0113] For example, the condition unit is C1<15, and the constant is 15. At this time, the lower-level partition tables are T1_P1, T1_P2, and T1_P3. The lower-level partition tables are traversed sequentially. When traversing to T1_P1, the partition value limit is 10, and 15>10. At this time, the current partition table is located, the location attribute is the first attribute, and the comparison operator is less than. At this time, the partition value range is a subset of the range indicated by the condition unit. The optimization indication information indicates that the expression of the condition unit is TRUE.

[0114] S370. When the comparison result indicates that the constant is less than the partition value limit, if the positioning attribute is the first attribute, then the optimization indication information of the condition unit indicates that the condition unit should not be rewritten; if the positioning attribute is the second attribute, then when the comparison operator is equal to, less than, or less than or equal to, the optimization indication information indicates that the expression of the condition unit is the first expression; when the comparison identifier is not equal to, greater than, or greater than or equal to, the optimization indication information indicates that the expression of the condition unit is the second expression, and S3120 is executed.

[0115] Specifically, when the comparison result indicates that the constant is less than the partition value limit, if the location attribute is the second attribute, since the lower-level partition tables are arranged in ascending order according to the partition value limits, the intersection relationship between the partition value range and the range indicated by the condition unit can be directly determined before the current partition table is located: when the comparison operator is equal to, less than, or less than or equal to, the partition value range and the range indicated by the condition unit have no intersection, and the expression of the condition unit is determined to be the first expression; when the comparison operator is not equal to, greater than, or greater than or equal to, the partition value range is a subset of the range indicated by the condition unit, and the expression of the condition unit is determined to be the second expression. If the location attribute is the first attribute, the intersection relationship between the partition value range and the range indicated by the condition unit cannot be directly determined, and it is necessary to traverse the specific partition value data in the current partition table for judgment. In this case, no expression optimization is performed.

[0116] For example, when the condition unit is C2=0, the current partition table is T1_P1_P11. The lower-level partition tables are T1_P1_P11 and T1_P1_P12. The current lower-level partition table is T1_P1_P11, and the partition value limit is 1, 0<1. At this time, the current partition table is located, the location attribute is the first attribute, and the original expression C2=0 is retained.

[0117] S380. When the comparison result indicates that the constant is equal to the partition value limit, if the positioning attribute is the first attribute and the boundary value marker indicates that the current lower-level partition table contains a boundary value, then when the comparison operator is equal to, not equal to, less than, or greater than or equal to, the optimization indication information of the condition unit is determined to indicate that the condition unit is not rewritten; when the comparison operator is less than or equal to, the optimization indication information is determined to indicate that the expression of the condition unit is the second expression; when the comparison operator is greater than, the optimization indication information is determined to indicate that the expression of the condition unit is the first expression, and S3120 is executed.

[0118] Specifically, when the comparison result indicates that the constant is equal to the partition value boundary, the positioning attribute is the first attribute, and the boundary value marker indicates that the current lower-level partition table contains the boundary value: if the comparison operator is equal to, not equal to, less than, or greater than or equal to, it is impossible to directly determine the intersection relationship between the partition value range and the range indicated by the condition unit. It is necessary to traverse the specific partition value data in the current partition table to make a judgment without rewriting the condition unit; if the comparison operator is less than or equal to, the partition value range is a subset of the range indicated by the condition unit, and the expression of the condition unit is determined to be the second expression; if the comparison operator is greater than, the partition value range has no intersection with the range indicated by the condition unit, and the expression of the condition unit is determined to be the first expression.

[0119] S390. When the comparison result indicates that the constant is equal to the partition value limit, if the positioning attribute is the first attribute and the boundary value marker indicates that the current lower-level partition table does not contain a boundary value, then when the comparison operator is equal to, greater than, or greater than or equal to, the expression of the condition unit indicated by the optimization indication information is determined to be the first expression; when the comparison operator is not equal to, less than, or less than or equal to, the expression of the condition unit indicated by the optimization indication information is determined to be the second expression, and S3120 is executed.

[0120] Specifically, when the comparison result indicates that the constant is equal to the partition value limit, the positioning attribute is the first attribute, and the boundary value marker indicates that the current lower-level partition table does not contain boundary values: if the comparison operator is equal to, greater than, or greater than or equal to, then the partition value range and the range indicated by the condition unit do not intersect, and the expression of the condition unit indicated by the optimization indication information is determined to be the first expression; if the comparison operator is not equal to, less than, or less than or equal to, then the partition value range is a subset of the range indicated by the condition unit, and the expression of the condition unit indicated by the optimization indication information is determined to be the second expression.

[0121] For example, condition unit C2=1, the current partition table is T1_P1_P11, the partition value limit is 1, 1=1, the lower-level partition tables are T1_P1_P11 and T1_P1_P12, the current lower-level partition table is T1_P1_P11, the positioning attribute is the first attribute, it does not include the boundary value, the boundary value is marked as FALSE, the comparison operator is equal, and the expression of the condition unit indicating the optimization indication information is the first expression FALSE.

[0122] S3100: When the comparison result indicates that the constant is equal to the partition value limit, if the positioning attribute is the second attribute and the boundary value marker indicates that the current lower-level partition table contains a boundary value, then when the comparison operator is equal to, less than, or less than or equal to, the expression of the condition unit indicated by the optimization indication information is determined to be the first expression; when the comparison operator is not equal to, greater than, or greater than or equal to, the expression of the condition unit indicated by the optimization indication information is determined to be the second expression, and S3120 is executed.

[0123] Specifically, when the comparison result indicates that the constant is equal to the partition value limit, the positioning attribute is the second attribute, and the boundary value marker indicates that the current lower-level partition table contains the boundary value: if the comparison operator is equal to, less than, or less than or equal to, then the partition value range and the range indicated by the condition unit do not intersect, and the expression of the condition unit is determined to be the first expression; if the comparison operator is not equal to, greater than, or greater than or equal to, then the partition value range is a subset of the range indicated by the condition unit, and the expression of the condition unit is determined to be the second expression.

[0124] S3110: When the comparison result indicates that the constant is equal to the partition value limit, if the positioning attribute is the second attribute and the boundary value marker indicates that the current lower-level partition table does not contain a boundary value, then when the comparison operator is equal to, not equal to, less than or equal to, or greater than, continue to select the next lower-level partition table as the new current lower-level partition table; when the comparison operator is less than, determine that the expression of the condition unit indicated by the optimization indication information is the first expression; when the comparison operator is greater than or equal to, determine that the expression of the condition unit indicated by the optimization indication information is the second expression, and execute S3120.

[0125] Specifically, when the comparison result indicates that the constant is equal to the partition value boundary, the positioning attribute is the second attribute, and the boundary value marker indicates that the current lower-level partition table does not contain the boundary value: if the comparison operator is equal to, not equal to, less than or equal to, or greater than, it is not possible to determine whether the subsequent lower-level partitions contain data that meets the query conditions indicated by the condition unit based solely on the current lower-level partition. In this case, it is necessary to continue traversing to avoid query omissions; if the comparison operator is less than, it can be directly determined that the partition value range does not intersect with the range indicated by the condition unit, and the optimization indication information indicates that the expression of the condition unit is the first expression; if the comparison operator is greater than or equal to, the partition value range is a subset of the range indicated by the condition unit, and the optimization indication information indicates that the expression of the condition unit is the second expression.

[0126] S3120. The filtering conditions are rewritten based on the optimization indication information to obtain the rewritten filtering conditions.

[0127] S3130. If all expressions corresponding to the original expression of the conditional unit after being split are the first expression, the original expression of the conditional unit is rewritten as the first expression.

[0128] Specifically, if all condition units obtained by splitting the original expression in a condition unit can be rewritten as the first expression, then the original expression of the condition unit is directly rewritten as the first expression.

[0129] For example, if the original expression contains member conditions, and each member of the member conditions can be optimized to the first expression, i.e., FALSE, then the original expression can be directly rewritten as FALSE.

[0130] The technical solution of this invention involves obtaining filtering conditions from a structured query statement; when the filtering conditions satisfy optimization conditions, dividing the filtering conditions into condition units; for each condition unit, when the condition unit is a Boolean expression containing column names and constants, determining the current partition table where the target column corresponding to the column name is located, determining the current partition column of the current partition table, and determining the optimization flag of the condition unit by comparing the current partition column with the target column; if the table name of the current partition table is the same as the table name of the current lower-level partition table, determining the positioning attribute of the current lower-level partition table as a first attribute; otherwise, determining the positioning attribute of the current lower-level partition table as a second attribute; comparing the constant with the partition value limit to obtain a comparison result; when the comparison result indicates that the constant is greater than or less than the partition value limit, optimizing the condition unit according to the condition unit... The range indicated by the constant and comparison operator in the meta-database is compared with the range of partition column values ​​in the current lower-level partition table. The positioning attributes are analyzed to determine optimization instructions, allowing for selective querying and avoiding traversal of the entire range of partition tables, thus improving query efficiency and accuracy. When the comparison result indicates that the constant equals the partition value boundary, optimization instructions are determined based on the positioning attributes, comparison operators, and boundary value markers. Ignore lower-level partition sub-tables are pruned to avoid traversal of the entire range of partition tables, further improving query efficiency and accuracy. The filtering conditions are rewritten based on these optimization instructions to obtain rewritten filtering conditions. If all expressions corresponding to the original expression of the condition unit are the first expression after decomposition, the original expression of the condition unit is rewritten as the first expression. By optimizing the execution plan during the query, computational resource overhead is reduced, further improving query efficiency.

[0131] The present invention will be described exemplarily below. In this embodiment, "smallest unit" represents "condition unit", "in condition", "in lst" or "lst" represents "member condition", and "operator" represents "comparison operator":

[0132] This invention proposes a method for rewriting filter conditions. For filter queries performed on m-level partitioned sub-tables, when the filter condition is a range-related operation and the column of the filter condition is an n (n<=m) level range partition column, the filter condition can be optimized to avoid redundant condition filtering processes as much as possible and reduce resource consumption during the query of the range partitioned table.

[0133] In this invention, the smallest unit in an AND / OR connective is a logical operation unit (i.e., a conditional unit) that cannot be further divided using AND or OR as a delimiter. For example, the smallest units after dividing A and (B or (C and D)) are A, B, C, and D, respectively.

[0134] This invention analyzes the filtering conditions of SQL statements that query data from partitioned sub-tables, and selects those conditions that can be optimized. After the filtering condition analysis, partition pruning and optimization are performed.

[0135] First, analyze the filtering conditions:

[0136] It only supports comparisons between partition columns and constant values ​​(<, <=, >, >=, =) or IN condition comparisons, or logical operations combining these two filtering conditions connected by AND / OR.

[0137] ① For cases where the filtering condition is an AND or OR expression, perform optimization operations on the smallest filtering condition unit under each AND or OR branch.

[0138] ② For filter conditions of the form col (i.e., partition column (column, col)) in lst, construct a col = constant expression for each constant (i.e., member) in lst, and attempt to optimize the expression. If the optimization is FALSE (i.e., the optimization indicator indicates that the expression is the first expression), the constant can be removed from lst. If every constant in lst can be optimized to FALSE, then the in lst expression can be optimized to FALSE.

[0139] After completing the deformation of the filter conditions, we attempt to optimize each smallest unit of the filter conditions.

[0140] Range partitioning tables can determine whether to perform optimization processing by judging the interval relationship between the filtering conditions and the partition range (i.e., the range of partition values).

[0141] 1. If the range of values ​​in the partition column of the partitioned subtable is a subset of the range of the filtering conditions, optimize the filtering conditions to TRUE (i.e., the expression is the second expression).

[0142] 2. If the range of partition column values ​​in the partitioned sub-table does not overlap with the range of the filtering conditions, optimize the filtering conditions to FASLE (i.e., the expression is the first expression).

[0143] 3. If the range of partition column values ​​in the partitioned subtable is not a subset of the range of filtering conditions, and the range of partition column values ​​in the partitioned subtable overlaps with the range of filtering conditions, no optimization will be performed.

[0144] Given the following example partitioned table T1: T1 is divided into three sub-tables based on the integer values ​​in column C1: P1 (C1<10); P2 (10≤C1<20); P3 (C1≥20);

[0145] Within each partitioned sub-table, further divide it into two sub-partitioned sub-tables based on column C2: P11 (C2<1); P12 (C2≥1).

[0146] The filtering conditions of this invention can be rewritten according to the following steps:

[0147] (1) Execute the SQL statement to query the data of the partitioned sub-tables to begin partition pruning and optimization. For example: query all records from the sub-partition P1_P11 of table T1 that simultaneously satisfy C1<15 and C2 value is 0 or 1;

[0148] (2) Perform a preliminary analysis of the filtering conditions and cut them into the smallest units connected by AND / OR (i.e., delimiters). Iterate through each smallest unit of the filtering conditions and set it as the current condition cdt. (When the smallest unit is in the form of "in lst", construct the form of "=constant expression" for each constant in lst and set it as the current condition cdt one by one.) Execute step (3) for each cdt and try to optimize it. Example: The filtering conditions in the example statement are C1<15 AND C2 IN (0,1). The cdts that are tried to be optimized one by one are C1<15, C2=0, and C2=1.

[0149] (3) Determine if the current cdt is a Boolean expression of columns and constants, for example: c1=10. If yes, execute step (4); otherwise, execute step (14). Example: First traversal: C1<15, meets the condition; Second traversal: C2=0, meets the condition; Third traversal: C2=1, meets the condition.

[0150] (4) Parse out the column col (i.e., the target column) involved in the cdt, and the table tab (the partition table specified in FROM) where the column is located, and set the current processing table to the table tab. Example: C1<15: tab:T1_P1_P11; current processing table:T1_P1_P11; C2=0: tab:T1_P1_P11; current processing table:T1_P1_P11; C2=1: same as C2=0.

[0151] (5) Get the partition column part_col of the current processing table. If it is the same as col, it means that optimization can be performed on the current level partition. At this time, set the optimization flag to TRUE (i.e., the first flag information), and set the partition base table base_tab to the upper level partition table of tab, and the partition sub-table sub_tab to the current partition table tab. Execute step (6). If it is different from col, if the current processing table is the partition root table, the current filtering condition cannot be optimized (i.e., set the optimization flag to the second flag information). Execute step (14). If the current processing table is not the partition root table, get the upper level partition table of the current processing table and set it to the current processing table tab. Continue step (5).

[0152] Example: C1 < 15: The first time, part_col is C2; col is C1, they are inconsistent, so optimization cannot be performed; tab is updated to the parent partition table T1_P1, and a second judgment is made. The second time, part_col is C1, which is consistent with col, so the partition base table base_tab is set to T1, and the partition sub-table sub_tab is set to T1_P1, and the optimization is marked as TRUE. C2 = 0: The first time, part_col is C2, col is C2, they are consistent, so the partition base table base_tab can be directly set to T1_P1, and the partition sub-table sub_tab is set to T1_P1_P11, and the optimization is marked as TRUE. C2 = 1: Same as C2 = 0.

[0153] (6) If the optimization flag is not TRUE or the partition base table is not a range partition table, then proceed to step (14); otherwise, proceed to step (7). Example: C1<15: The optimization flag is TRUE, and the partition base table T1 is a range partition formed with C1 as the partition column. C2=0: The optimization flag is TRUE, and the partition base table T1_P1 is a range partition formed with C2 as the partition column. C2=1: Same as C2=0.

[0154] (7) Obtain the constant 'e' and comparison operator 'ops' from the filter condition expression. Set the first lower-level partition (i.e., the lower-level partition table) of base_tab to last_tab. Execute step (8). Example: C1<15: e is 15, ops is '<'; last_tab=T1_P1. C2=0: e is 0, ops is '='; last_tab=T1_P1_P11. C2=1: Same as C2=0.

[0155] (8) Perform the following operations on the currently traversed last_tab: Obtain the partition value boundary part_value of last_tab, the table id (i.e., table name) of the sub-table of this partition, and the flag include_high_flag indicating whether this partition contains boundary values. And determine whether the current sub-partition can be located: If the table id of last_tab is equal to the table id of sub_tab, it means that the current sub-partition can be located.

[0156] Example: List the values of each lower-level partition here. Actually, not all lower-level partitions will be traversed in this example. C1 < 15: The lower-level partitions of base_tab are T1_P1, T1_P2, T1_P3 respectively, and the values are shown in Table 1 as follows:

[0157] Table 1 T1 Partition Information Table

[0158]

[0159] C2 = 0: The lower-level partitions of base_tab are T1_P1_P11, T1_P1_P12 respectively. The obtained values are shown in Table 2 as follows:

[0160] Table 2 T1_P1 Partition Information Table

[0161]

[0162] C2 = 1: Same as C2 = 0.

[0163] (9) Judge the relationship between the constant e and the partition value part_value of the currently traversed partition table (i.e., the current lower-level partition table). If e < part_value, execute (10). If e = part_value, execute (11). Otherwise, execute (12).

[0164] Example: C1 < 15: When traversing to T1_P1, the table id = the table id of sub_tab, so T1_P1 can be located. e = 15 > 10 = part_value, execute (12). C2 = 0: When traversing to T1_P1_P11, the table id = the table id of sub_tab, so T1_P1_P11 can be located. e = 0 < 1 = part_value, execute (10). C2 = 1: When traversing to T1_P1_P11, the table id = the table id of sub_tab, so T1_P1_P11 can be located. e = 1 = 1 = part_value, execute (11).

[0165] (10) If the current sub-partition is located (i.e., the current partition table is located, and the location attribute is the first attribute), no optimization is performed, and go to (14);

[0166] If the current subpartition is not located (i.e., the current partition table is not located, the location attribute is the second attribute):

[0167] When the operator ops is =, < or <=, generate the FALSE expression (i.e., the first expression), go to (13);

[0168] When the operator ops is !=, > or >=, a TRUE expression (i.e., the second expression) is generated, and go to (13).

[0169] Example: C2=0 jumps to this point. Since the current subpartition is located, no optimization is performed.

[0170] (11) If the current subpartition is located and the partition contains a boundary value (include_high_flag=TRUE):

[0171] When the operator ops is =, !=, < or >=, no optimization is performed, go to (13);

[0172] When the operator ops is <=, generate a TRUE expression and go to (13).

[0173] When the operator ops is >, generate a FALSE expression and go to (13).

[0174] If the current subpartition is located and that subpartition does not contain boundary values ​​(include_high_flag=FALSE):

[0175] When the operator ops is =, > or >=, generate a FALSE expression and go to (13).

[0176] When the operator ops is !=, < or <=, generate a TRUE expression, go to (13).

[0177] If the current subpartition is not located and that subpartition contains a boundary value (include_high_flag=TRUE):

[0178] When the operator ops is =, < or <=, generate a FALSE expression and go to (13).

[0179] When the operator ops is !, =, >, or >=, a TRUE expression is generated, go to (13).

[0180] If the current subpartition is not located and the subpartition does not contain boundary values ​​(include_high_flag=FALSE):

[0181] When the operator ops is =, !=, <= or >, set last_tab as the next lower partition of base_tab and go to (8) to continue traversing;

[0182] When the operator ops is <, generate a FALSE expression and go to (13).

[0183] When the operator ops is >=, generate a TRUE expression and go to (13).

[0184] Example: C2=1: Locate the current subpartition, which does not contain boundary values, and generate a FALSE expression.

[0185] (12) If the current subpartition is located:

[0186] When the operator ops is =, > or >=, generate a FALSE expression and go to (13).

[0187] When the operator ops is !=, < or <=, generate a TRUE expression, go to (13).

[0188] If the current subpartition is not located, set last_tab to the next lower partition of base_tab and go to (8) to continue traversing;

[0189] Example: C1<15: The operator is <, so a TRUE expression is generated.

[0190] (13) Analyze the newly generated expression and replace the expression in the original filter condition. Jump to (14). The detailed replacement rules are as follows: If the original expression of cdt is a Boolean expression of column and constant, and it is optimized to TRUE or FALSE, directly replace the filter expression with TRUE / FALSE. If the original expression of cdt is an in expression and it is optimized to FALSE, remove the value from the in list in the original filter condition. (This invention will not optimize the filter condition of "=constant" to TRUE) Regardless of the form of the original expression of cdt, if no optimization is performed, the original expression is retained in the original filter condition.

[0191] Example: After optimizing C1<15 to TRUE, the original filter condition is replaced with: TRUE AND C2 IN(0, 1). C2=0 cannot be optimized, so the original filter condition remains unchanged; C2=1 is optimized to FALSE because the expression is constructed from in_lst, so 1 is removed from in_lst: TRUE AND C2 IN (0).

[0192] (14) When there is an untraversed minimum filter condition unit, set the current minimum filter condition unit as the next one and jump to (3) to continue trying to optimize. When all minimum filter condition units have been traversed, jump to (15).

[0193] (15) If there is a branch to optimize, analyze and simplify the filter expression, and jump to (16). Calculate and simplify the optimized filter condition expression according to the Boolean operation rules. If there is an in lst expression and all constants in lst are removed, then optimize the in lst expression to FALSE. Example: The final simplification is: Query the partition value where C2=0.

[0194] (16) Optimization complete.

[0195] For example, a range-partitioned table TEST_RANGE was created, and 6 million test data records were inserted in batches. The partitioning information is shown in Table 3:

[0196] Table 3 TEST_RANGE Partition Information Table

[0197]

[0198] If the filtering condition is C2>22, and the partitioning condition of sub-partition P1 is C2<=10, the partitioning condition range (i.e., the range of partition values) and the filtering condition range (i.e., the range of filtering conditions) have no intersection. Therefore, the partition sub-table TEST_RANGE_P1 has no data that meets the filtering condition, and the optimization will change the filtering condition to FALSE.

[0199] The execution plan before optimization involved traversing the TEST_RANGE_P1 table, with an execution time of 1248ms. The optimized execution plan directly excludes all data in the TEST_RANGE_P1 table without traversing it, with an execution time of 28.796ms.

[0200] If the filtering condition is to count the number of records in column C2 with a value less than or equal to 100 from partition P2 of partition table TEST_RANGE, the partition condition range (i.e., the range of partition values) is a subset of the filtering condition range (i.e., the range of filtering conditions). All data in partition sub-table TEST_RANGE_P2 satisfies the filtering condition, and the optimization will optimize the filtering condition to TRUE.

[0201] The original execution plan iterated through all data in the TEST_RANGE_P2 table, taking 1851ms. The optimized execution plan directly outputs the row count of the TEST_RANGE_P2 table without actually iterating through all data, taking 37.169ms.

[0202] By comparing the execution plan and execution time, it can be seen that the present invention can improve query efficiency.

[0203] Example 4

[0204] Figure 4This is a schematic diagram of a filter condition rewriting device provided in Embodiment 4 of the present invention. Figure 4 As shown, the device includes:

[0205] The acquisition module 410 is used to acquire the filtering conditions in the structured query statement, which includes a statement for querying a range partitioned table;

[0206] The segmentation module 420 is used to segment the filtering conditions into condition units when the filtering conditions meet the optimization conditions. The condition unit includes logical operation units in the filtering conditions that cannot be further divided by a delimiter, and the condition unit does not contain member conditions.

[0207] The determination module 430 is used to determine, for each condition unit, when the condition unit is a Boolean expression containing column name and constant, the current partition table where the target column corresponding to the column name is located, the current partition column of the current partition table, and the optimization mark of the condition unit by comparing the current partition column with the target column;

[0208] The optimization module 440 is configured to, for each condition unit, if the optimization flag corresponding to the condition unit is the first flag information and the partition base table of the current partition table corresponding to the condition unit is a range partition table, obtain the constant and comparison operator in the condition unit, select the current lower-level partition table from the lower-level partition table of the partition base table, and determine the optimization indication information of the condition unit according to the constant, the comparison operator and the partition information of the current lower-level partition table, wherein the first flag information indicates that the condition unit is to be optimized;

[0209] The rewriting module 450 is used to rewrite the filtering conditions based on the optimization indication information to obtain the rewritten filtering conditions.

[0210] The technical solution of this invention involves: an acquisition module obtaining filtering conditions from a structured query statement; a segmentation module segmenting the filtering conditions into condition units when the filtering conditions satisfy optimization conditions; a determination module determining the current partition table containing the target column corresponding to the column name and the current partition column of the current partition table for each condition unit, when the condition unit is a Boolean expression containing column names and constants; and determining the optimization flag of the condition unit by comparing the current partition column with the target column, thus rewriting and optimizing only the filtering conditions that satisfy the conditions to improve query efficiency. The optimization module, for each condition unit, determines the condition unit if the optimization flag corresponding to the condition unit is the first flag information and the current partition table corresponding to the condition unit is... If the base partition table is a range partition table, then the constants and comparison operators in the condition unit are obtained. The current lower-level partition table is selected from the lower-level partition table of the base partition table. Based on the constants, the comparison operators, and the partition information of the current lower-level partition table, the optimization indication information of the condition unit is determined. By determining the intersection relationship between the range of the current lower-level partition table and the range of the filtering conditions, it is determined whether to optimize the condition unit and what kind of optimization to perform. This completes the partition pruning of the data table, reduces the amount of data processing, further improves query efficiency, and reduces the computational resource overhead of the system. The rewriting module rewrites the filtering conditions based on the optimization indication information to obtain the rewritten filtering conditions, completing the rewriting optimization of the filtering conditions and improving the accuracy of partition pruning.

[0211] In another embodiment, the determining module 430 is specifically used for:

[0212] Determine whether the current partition column is the same as the target column;

[0213] If so, determine the optimization flag of the condition unit as the first flag information, and determine the parent partition table of the current partition table as the partition base table of the current partition table;

[0214] If not, determine whether the current partition table is the partition root table. If yes, determine the optimization mark of the condition unit as the second mark information; otherwise, take the parent partition table of the current partition table as the new current partition table, and return to continue comparing the current partition column of the new current partition table with the target column to determine the optimization mark of the condition unit.

[0215] The second marker information indicates that the condition unit cannot be optimized.

[0216] In another embodiment, the partition information includes table name, partition value limits, and boundary value markers. The optimization module 440 further includes:

[0217] The first determining unit is configured to determine the positioning attribute of the current lower-level partition table as a first attribute if the table name of the current lower-level partition table is the same as the table name of the current partition table; otherwise, determine the positioning attribute of the current lower-level partition table as a second attribute.

[0218] A comparison unit is used to compare the constant with the partition value boundary to obtain a comparison result.

[0219] The second determining unit is used to determine the optimization indication information of the condition unit based on the positioning attribute of the current lower-level partition table and the comparison operator when the comparison result indicates that the constant is greater than or less than the partition value limit.

[0220] The third determining unit is used to determine the optimization indication information of the condition unit based on the positioning attribute of the current lower-level partition table, the boundary value marker, and the comparison operator when the comparison result indicates that the constant is equal to the partition value boundary. The boundary value marker indicates whether the current lower-level partition table contains a boundary value.

[0221] In another embodiment, the second determining unit is specifically used for:

[0222] When the comparison result indicates that the constant is greater than the partition value limit, if the positioning attribute is the second attribute, then the next lower-level partition table is taken as the new current lower-level partition table, and the optimization instruction information determination operation continues; if the positioning attribute is the first attribute, then when the comparison operator is equal to, greater than, or greater than or equal to, the optimization instruction information indicates that the expression of the condition unit is the first expression; when the comparison operator is not equal to, less than, or less than or equal to, the optimization instruction information indicates that the expression of the condition unit is the second expression.

[0223] When the comparison result indicates that the constant is less than the partition value limit, if the positioning attribute is the first attribute, then the optimization indication information of the condition unit indicates that the condition unit should not be rewritten; if the positioning attribute is the second attribute, then when the comparison operator is equal to, less than, or less than or equal to, the optimization indication information indicates that the expression of the condition unit is the first expression; when the comparison identifier is not equal to, greater than, or greater than or equal to, the optimization indication information indicates that the expression of the condition unit is the second expression.

[0224] In another embodiment, the third determining unit is specifically used for:

[0225] When the comparison result indicates that the constant is equal to the partition value limit, if the positioning attribute is the first attribute and the boundary value marker indicates that the current lower-level partition table contains a boundary value, then when the comparison operator is equal to, not equal to, less than, or greater than or equal to, the optimization indication information of the condition unit indicates that the condition unit should not be rewritten; when the comparison operator is less than or equal to, the optimization indication information indicates that the expression of the condition unit is the second expression; when the comparison operator is greater than, the optimization indication information indicates that the expression of the condition unit is the first expression.

[0226] When the comparison result indicates that the constant is equal to the partition value limit, if the positioning attribute is the first attribute and the boundary value marker indicates that the current lower-level partition table does not contain a boundary value, then when the comparison operator is equal to, greater than, or greater than or equal to, the expression of the condition unit indicated by the optimization indication information is determined to be the first expression; when the comparison operator is not equal to, less than, or less than or equal to, the expression of the condition unit indicated by the optimization indication information is determined to be the second expression.

[0227] When the comparison result indicates that the constant is equal to the partition value limit, if the positioning attribute is the second attribute and the boundary value marker indicates that the current lower-level partition table contains a boundary value, then when the comparison operator is equal to, less than, or less than or equal to, the expression of the condition unit indicated by the optimization indication information is determined to be the first expression; when the comparison operator is not equal to, greater than, or greater than or equal to, the expression of the condition unit indicated by the optimization indication information is determined to be the second expression.

[0228] When the comparison result indicates that the constant is equal to the partition value limit, if the positioning attribute is the second attribute and the boundary value marker indicates that the current lower-level partition table does not contain a boundary value, then when the comparison operator is equal to, not equal to, less than or equal to, or greater than, the next lower-level partition table is selected as the new current lower-level partition table. When the comparison operator is less than, the expression of the condition unit indicated by the optimization indication information is determined to be the first expression. When the comparison operator is greater than or equal to, the expression of the condition unit indicated by the optimization indication information is determined to be the second expression.

[0229] In another embodiment, the device is specifically used for: the filtering condition being a comparison of a partition column with a constant value, and / or a comparison of a partition column with a membership condition.

[0230] In another embodiment, the segmentation module 420 is specifically used for:

[0231] The filtering conditions are divided using the delimiters in the filtering conditions to obtain initial segmentation units;

[0232] For each initial segmentation unit, if the initial segmentation unit contains member conditions, the member conditions are rewritten as an expression composed of constants and columns in the member conditions, and the rewritten expression is used as a condition unit; otherwise, the initial segmentation unit is used as a condition unit.

[0233] In another embodiment, the rewrite module 450 is specifically used for:

[0234] For each condition unit in the filtering conditions, if the optimization indication information indicates that the condition unit should not be rewritten, then the original expression corresponding to the condition unit is retained in the filtering conditions; otherwise, one of the following operations is performed:

[0235] When the original expression corresponding to the condition unit is a Boolean expression, and the optimization indication information indicates that the expression of the condition unit is a first expression or a second expression, the indicated expression replaces the original expression corresponding to the condition unit in the filtering condition.

[0236] When the original expression corresponding to the condition unit is a member condition, and the optimization indication information indicates that the expression of the condition unit is a first expression, the constants included in the condition unit are removed from the corresponding positions in the filtering conditions.

[0237] In another embodiment, the device further includes:

[0238] The simplification module is used to simplify the rewritten filtering conditions according to Boolean operation rules.

[0239] In another embodiment, the simplification module is specifically used for:

[0240] If all expressions corresponding to the original expression of the conditional unit after being split are the first expression, then the original expression of the conditional unit is rewritten as the first expression.

[0241] The filter condition rewriting device provided in this embodiment of the invention can execute a filter condition rewriting method provided in any embodiment of the invention, and has the corresponding functional modules and beneficial effects of the method.

[0242] Example 5

[0243] Figure 5A structural block diagram of an electronic device that can be used to implement embodiments of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital assistants, cellular phones, smartphones, wearable devices (such as helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.

[0244] like Figure 5 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12 or a random access memory (RAM) 13, communicatively connected to the at least one processor 11. The memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes based on the computer program stored in the ROM 12 or loaded from storage unit 18 into the RAM 13. The RAM 13 can also store various programs and data required for the operation of the electronic device 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0245] Multiple components in electronic device 10 are connected to I / O interface 15, including: input unit 16, such as keyboard, mouse, etc.; output unit 17, such as various types of displays, speakers, etc.; storage unit 18, such as disk, optical disk, etc.; and communication unit 19, such as network card, modem, wireless transceiver, etc. Communication unit 19 allows electronic device 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.

[0246] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as a filter condition rewriting method.

[0247] In some embodiments, a filter condition rewriting method may be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or mounted on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the filter condition rewriting method described above may be performed. Alternatively, in other embodiments, processor 11 may be configured to perform a filter condition rewriting method by any other suitable means (e.g., by means of firmware).

[0248] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard parts (ASSPs), systems-on-chip (SoCs), complex programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0249] Computer programs used to implement the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0250] In the context of this invention, a computer-readable storage medium stores computer instructions that, when executed by a processor, implement a filtering condition rewriting method provided by this invention.

[0251] The present invention also provides a computer program product comprising a computer program that, when executed by a processor, implements the method provided according to embodiments of the present invention.

[0252] Computer-readable storage media can be tangible media that may contain or store computer programs for use by or in conjunction with an instruction execution system, apparatus, or device. Computer-readable storage media can be, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, computer-readable storage media can be machine-readable signal media. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0253] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device for displaying information to the user, such as a cathode ray tube (CRT) or a liquid crystal display (LCD); and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0254] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or middleware components (e.g., application servers), or frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0255] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system. It addresses the shortcomings of traditional physical hosts and Virtual Private Server (VPS) services, such as high management difficulty and weak business scalability.

[0256] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and no limitation is imposed herein.

[0257] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A method for rewriting filter conditions, characterized in that, include: Obtain the filtering conditions in the structured query statement, which includes a statement that queries a range of partitioned tables; If the filtering conditions satisfy the optimization conditions, the filtering conditions are divided into condition units. The condition unit includes logical operation units that cannot be further divided by delimiters in the filtering conditions, and the condition unit does not contain member conditions. For each condition unit, when the condition unit is a Boolean expression containing column names and constants, determine the current partition table where the target column corresponding to the column name is located, determine the current partition column of the current partition table, and determine the optimization mark of the condition unit by comparing the current partition column with the target column; For each condition unit, if the optimization flag corresponding to the condition unit is the first flag information, and the partition base table of the current partition table corresponding to the condition unit is a range partition table, then the constant and comparison operator in the condition unit are obtained, the current lower-level partition table is selected from the lower-level partition table of the partition base table, and the optimization indication information of the condition unit is determined according to the constant, the comparison operator and the partition information of the current lower-level partition table. The first flag information indicates that the condition unit is to be optimized. The filtering conditions are rewritten based on the optimization indication information to obtain the rewritten filtering conditions; The step of rewriting the filtering conditions based on each of the optimization indications to obtain the rewritten filtering conditions includes: For each condition unit in the filtering conditions, if the optimization indication information indicates that the condition unit should not be rewritten, then the original expression corresponding to the condition unit is retained in the filtering conditions; otherwise, one of the following operations is performed: When the original expression corresponding to the condition unit is a Boolean expression, and the optimization indication information indicates that the expression of the condition unit is a first expression or a second expression, the indicated expression replaces the original expression corresponding to the condition unit in the filtering condition. When the original expression corresponding to the condition unit is a member condition, and the optimization indication information indicates that the expression of the condition unit is a first expression, the constants included in the condition unit are removed from the corresponding positions in the filtering conditions.

2. The method according to claim 1, characterized in that, The step of determining the optimization flag of the condition unit by comparing the current partition column with the target column includes: Determine whether the current partition column is the same as the target column; If so, determine the optimization flag of the condition unit as the first flag information, and determine the parent partition table of the current partition table as the partition base table of the current partition table; If not, determine whether the current partition table is the partition root table. If yes, determine the optimization mark of the condition unit as the second mark information; otherwise, take the parent partition table of the current partition table as the new current partition table, and return to continue comparing the current partition column of the new current partition table with the target column to determine the optimization mark of the condition unit. The second marker information indicates that the condition unit cannot be optimized.

3. The method according to claim 1, characterized in that, The partitioning information includes table name, partition value limits, and boundary value markers; The step of selecting the current lower-level partition table from the lower-level partition table of the partition base table, and determining the optimization indication information of the condition unit based on the constant, the comparison operator, and the partition information of the current lower-level partition table, includes: If the name of the current lower-level partition table is the same as the name of the current partition table, then the positioning attribute of the current lower-level partition table is determined to be the first attribute; otherwise, the positioning attribute of the current lower-level partition table is determined to be the second attribute. The comparison result is obtained by comparing the constant with the partition value boundary. When the comparison result indicates that the constant is greater than or less than the partition value limit, the optimization indication information of the condition unit is determined according to the positioning attribute of the current lower-level partition table and the comparison operator. When the comparison result indicates that the constant is equal to the partition value limit, the optimization indication information of the condition unit is determined according to the positioning attribute of the current lower-level partition table, the boundary value marker, and the comparison operator. The boundary value marker indicates whether the current lower-level partition table contains a boundary value.

4. The method according to claim 3, characterized in that, When the comparison result indicates that the constant is greater than or less than the partition value limit, the optimization indication information of the condition unit is determined based on the positioning attribute of the current lower-level partition table and the comparison operator, including: When the comparison result indicates that the constant is greater than the partition value limit, if the positioning attribute is the second attribute, then the next lower-level partition table is taken as the new current lower-level partition table, and the optimization instruction information determination operation continues; if the positioning attribute is the first attribute, then when the comparison operator is equal to, greater than, or greater than or equal to, the optimization instruction information indicates that the expression of the condition unit is the first expression; when the comparison operator is not equal to, less than, or less than or equal to, the optimization instruction information indicates that the expression of the condition unit is the second expression. When the comparison result indicates that the constant is less than the partition value limit, if the positioning attribute is the first attribute, then the optimization indication information of the condition unit indicates that the condition unit should not be rewritten; if the positioning attribute is the second attribute, then when the comparison operator is equal to, less than, or less than or equal to, the optimization indication information indicates that the expression of the condition unit is the first expression, and when the comparison operator is not equal to, greater than, or greater than or equal to, the optimization indication information indicates that the expression of the condition unit is the second expression.

5. The method according to claim 3, characterized in that, When the comparison result indicates that the constant is equal to the partition value boundary, the optimization indication information of the condition unit is determined based on the positioning attribute of the current lower-level partition table, the boundary value marker, and the comparison operator, including: When the comparison result indicates that the constant is equal to the partition value limit, if the positioning attribute is the first attribute and the boundary value marker indicates that the current lower-level partition table contains a boundary value, then when the comparison operator is equal to, not equal to, less than, or greater than or equal to, the optimization indication information of the condition unit indicates that the condition unit should not be rewritten; when the comparison operator is less than or equal to, the optimization indication information indicates that the expression of the condition unit is the second expression; when the comparison operator is greater than, the optimization indication information indicates that the expression of the condition unit is the first expression. When the comparison result indicates that the constant is equal to the partition value limit, if the positioning attribute is the first attribute and the boundary value marker indicates that the current lower-level partition table does not contain a boundary value, then when the comparison operator is equal to, greater than, or greater than or equal to, the expression of the condition unit indicated by the optimization indication information is determined to be the first expression; when the comparison operator is not equal to, less than, or less than or equal to, the expression of the condition unit indicated by the optimization indication information is determined to be the second expression. When the comparison result indicates that the constant is equal to the partition value limit, if the positioning attribute is the second attribute and the boundary value marker indicates that the current lower-level partition table contains a boundary value, then when the comparison operator is equal to, less than, or less than or equal to, the expression of the condition unit indicated by the optimization indication information is determined to be the first expression; when the comparison operator is not equal to, greater than, or greater than or equal to, the expression of the condition unit indicated by the optimization indication information is determined to be the second expression. When the comparison result indicates that the constant is equal to the partition value limit, if the positioning attribute is the second attribute and the boundary value marker indicates that the current lower-level partition table does not contain a boundary value, then when the comparison operator is equal to, not equal to, less than or equal to, or greater than, the next lower-level partition table is selected as the new current lower-level partition table. When the comparison operator is less than, the expression of the condition unit indicated by the optimization indication information is determined to be the first expression. When the comparison operator is greater than or equal to, the expression of the condition unit indicated by the optimization indication information is determined to be the second expression.

6. The method according to claim 1, characterized in that, The optimization conditions include: The filtering condition is a comparison between the partition column and a constant value, and / or a comparison between the partition column and a member condition.

7. The method according to claim 1, characterized in that, The step of dividing the filtering conditions into condition units includes: The filtering conditions are divided using the delimiters in the filtering conditions to obtain initial segmentation units; For each initial segmentation unit, if the initial segmentation unit contains member conditions, the member conditions are rewritten as an expression composed of constants and columns in the member conditions, and the rewritten expression is used as a condition unit; otherwise, the initial segmentation unit is used as a condition unit.

8. The method according to claim 1, characterized in that, After rewriting the filtering conditions based on the optimization indication information to obtain the rewritten filtering conditions, the method further includes: simplifying the rewritten filtering conditions according to Boolean operation rules.

9. The method according to claim 8, characterized in that, The simplification of the rewritten filtering conditions according to Boolean operation rules includes: If all expressions corresponding to the original expression of the conditional unit after being split are the first expression, then the original expression of the conditional unit is rewritten as the first expression.

10. A filter condition rewriting device, characterized in that, include: The acquisition module is used to acquire the filtering conditions in the structured query statement, which includes a statement for querying a range partitioned table; The segmentation module is used to segment the filtering conditions into condition units when the filtering conditions meet the optimization conditions. The condition unit includes logical operation units in the filtering conditions that cannot be further divided by delimiters, and the condition unit does not contain member conditions. The determination module is used to determine the current partition table where the target column corresponding to the column name is located when the condition unit is a Boolean expression containing column name and constant for each condition unit, determine the current partition column of the current partition table, and determine the optimization mark of the condition unit by comparing the current partition column with the target column. An optimization module is configured to, for each condition unit, if the optimization flag corresponding to the condition unit is the first flag information and the partition base table of the current partition table corresponding to the condition unit is a range partition table, obtain the constant and comparison operator in the condition unit, select the current lower-level partition table from the lower-level partition table of the partition base table, and determine the optimization indication information of the condition unit based on the constant, the comparison operator and the partition information of the current lower-level partition table, wherein the first flag information indicates that the condition unit is to be optimized; The rewriting module is used to rewrite the filtering conditions based on each of the optimization indication information to obtain the rewritten filtering conditions. Specifically, the rewriting module is used for: For each condition unit in the filtering conditions, if the optimization indication information indicates that the condition unit should not be rewritten, then the original expression corresponding to the condition unit is retained in the filtering conditions; otherwise, one of the following operations is performed: When the original expression corresponding to the condition unit is a Boolean expression, and the optimization indication information indicates that the expression of the condition unit is a first expression or a second expression, the indicated expression replaces the original expression corresponding to the condition unit in the filtering condition. When the original expression corresponding to the condition unit is a member condition, and the optimization indication information indicates that the expression of the condition unit is a first expression, the constants included in the condition unit are removed from the corresponding positions in the filtering conditions.

11. An electronic device, characterized in that, The electronic device includes: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor to enable the at least one processor to perform the method of any one of claims 1-9.

12. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that are used to cause a processor to execute the method of any one of claims 1-9.

13. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the method according to any one of claims 1-9.

Images