Filtering condition rewriting method, device, equipment, medium and product

Abstract

The invention provides a filtering condition rewriting method and device, equipment, a medium and a product. The filtering condition rewriting method comprises the following steps: obtaining a filtering condition; when the filtering condition meets the optimization condition, the filtering condition is divided into condition units; when the condition unit is a Boolean expression containing a column name and a constant, determining a current partition table where a target column corresponding to the column name is located and a current partition column of the current partition table, and 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 the first mark information and the partition base table of the current partition table of the condition unit is the range partition table, acquiring a 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 according to the constant, selecting the current lower-level partition table from the range partition table; comparing the operator with partition information of the current lower-level partition table, and determining optimization indication information of the condition unit; and rewriting based on the optimization indication information to obtain a rewritten filtering condition. According to the method, the query efficiency of the filtering condition is improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of partition pruning optimization, and particularly relates to a filtering condition rewriting method and device, equipment, medium and product. BACKGROUND

[0002] Range partition is a commonly used database partitioning technology, and 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 a range partition table, a structured query language (SQL) is usually used, and partition pruning is achieved by means of a filtering condition, that is, only a subset of partitions meeting the condition is accessed, so as to improve the query efficiency. In this technology, the query efficiency is highly dependent on the selection of the filtering condition, and different filtering conditions will lead to different partition pruning effects, thereby affecting the execution time and efficiency of the query. Therefore, how to optimize the filtering condition to achieve more accurate and efficient partition pruning is a key problem to be solved at present. SUMMARY

[0004] The present application provides a filtering condition rewriting method, device, equipment, medium and product to improve the query efficiency of the filtering condition.

[0005] According to an aspect of the present application, a filtering condition rewriting method is provided, comprising:

[0006] obtaining a filtering condition in a structured query statement, wherein the structured query statement comprises a statement for querying a range partition table;

[0007] if the filtering condition meets an optimization condition, the filtering condition is divided into a condition unit, wherein the condition unit comprises a logical operation unit in the filtering condition that cannot be further divided by a separator, and the condition unit does not contain a member condition;

[0008] for each condition unit, if the form of the condition unit is a Boolean expression containing a column name and a constant, a current partition table in which a target column corresponding to the column name is located is determined, a current partition column of the current partition table is determined, and an optimization mark of the condition unit is determined by comparing the current partition column and the target column;

[0009] The optimization module is configured to: for each condition unit, if an optimization mark corresponding to the condition unit is first mark information, and a partition base table of a current partition table corresponding to the condition unit is a range partition table, then a constant and a comparison operator in the condition unit are obtained, a current lower partition table is selected from lower partition tables of the partition base table, and optimization indication information of the condition unit is determined according to the constant, the comparison operator, and partition information of the current lower partition table, the first mark information indicating that the condition unit is to be optimized.

[0010] The rewriting module is configured to rewrite the filter condition based on each of the optimization indication information, to obtain a rewritten filter condition.

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

[0012] The obtaining module is configured to obtain a filter condition in a structured query statement, the structured query statement comprising a statement for querying a range partition table;

[0013] The splitting module is configured to split the filter condition into condition units in a case where the filter condition satisfies an optimization condition, the condition units comprising logical operation units in the filter condition that cannot be further split by a split symbol, and the condition units not containing member conditions;

[0014] The determining module is configured to, for each condition unit, in a case where a form of the condition unit is a Boolean expression containing a column name and a constant, determine a current partition table in which a target column corresponding to the column name is located, determine a current partition column of the current partition table, and determine an optimization mark of the condition unit by comparing the current partition column and the target column;

[0015] The optimization module is configured to: for each condition unit, if an optimization mark corresponding to the condition unit is first mark information, and a partition base table of a current partition table corresponding to the condition unit is a range partition table, then a constant and a comparison operator in the condition unit are obtained, a current lower partition table is selected from lower partition tables of the partition base table, and optimization indication information of the condition unit is determined according to the constant, the comparison operator, and partition information of the current lower partition table, the first mark information indicating that the condition unit is to be optimized;

[0016] The rewriting module is configured to rewrite the filter condition based on each of the optimization indication information, to obtain a rewritten filter condition.

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

[0018] at least one processor; and

[0019] a memory in communication with the at least one processor; wherein

[0020] The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor to enable the at least one processor to perform the filtering condition rewriting method according to any one of the embodiments of the present application.

[0021] According to another aspect of the present application, there is provided a computer readable storage medium storing computer instructions for causing a processor to implement the filtering condition rewriting method according to any one of the embodiments of the present application when executed by the processor.

[0022] According to another aspect of the present application, there is provided a computer program product comprising a computer program for implementing the method according to any one of the embodiments of the present application when executed by a processor.

[0023] The technical scheme of the embodiments of the present application comprises the following steps: obtaining a filtering condition in a structured query statement; in the case that the filtering condition meets an optimization condition, splitting the filtering condition into condition units; for each condition unit, in the case that the form of 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, determining a current partition column of the current partition table, and determining an optimization mark of the condition unit by comparing the current partition column and the target column; only rewriting and optimizing the filtering condition meeting the condition, so as to improve the query efficiency; for each condition unit, in the case that the optimization mark corresponding to the condition unit is first mark information and the partition base table of the current partition table corresponding to the condition unit is a range partition table, obtaining a 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; determining whether to optimize the condition unit and how to optimize the condition unit by determining the intersection relationship between the range in which the current lower-level partition table is located and the range of the filtering condition, completing the partition pruning of the data table, reducing the data processing amount, further improving the query efficiency and reducing the computing resource consumption of the system; rewriting the filtering condition based on the optimization indication information, obtaining the rewritten filtering condition, completing the rewriting and optimization of the filtering condition, and improving the accuracy of the partition pruning.

[0024] It should be understood that the content described in this part is not intended to identify key or important features of the embodiments of the present application, nor to limit the scope of the present application. Other features of the present application will become apparent from the following description. BRIEF DESCRIPTION OF DRAWINGS

[0025] In order to make the technical solutions in the embodiments of the present application clearer, the accompanying drawings needed in the embodiments will be briefly introduced. Obviously, the accompanying drawings in the following description only constitute some embodiments of the present application, and other drawings can be obtained by those skilled in the art without any creative effort on the basis of these drawings.

[0026] Figure 1 A flow chart of a filtering condition rewriting method is provided for the first embodiment of the present application.

[0027] Figure 2 A flow chart of a filtering condition rewriting method is provided for the second embodiment of the present application.

[0028] Figure 3 A flow chart of a filtering condition rewriting method is provided for the third embodiment of the present application.

[0029] Figure 4 A structural schematic diagram of a filtering condition rewriting device is provided for the fourth embodiment of the present application.

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

[0031] In order to make the technical solutions in the embodiments of the present application clearer, the accompanying drawings needed in the embodiments will be briefly introduced. Obviously, the accompanying drawings in the following description only constitute some embodiments of the present application, and other drawings can be obtained by those skilled in the art without any creative effort on the basis of these drawings.

[0032] It should be noted that the terms "first", "second", and the like in the description and claims of the present application and the above-mentioned accompanying drawings are used to distinguish similar objects, and do not necessarily indicate a specific order or sequence. It should be understood that the data thus used can be interchanged under appropriate circumstances, so that the embodiments of the present application described herein can be implemented in an order other than that illustrated or described herein. In addition, the terms "include" and "have" and any variations thereof are intended to cover non-exclusive inclusion, for example, a process, method, system, product or device that includes a series of steps or units does not necessarily have to be limited to those steps or units clearly listed, but can include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

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

[0034] Horizontal partitioning is a core strategy for database table partitioning, which splits a large logical table into multiple physically stored sub-tables according to rows, each of which contains a part of the original table rows and shares the same table structure. Horizontal partitioning mainly includes range partitioning, list partitioning, hash partitioning, etc. These types can also be combined arbitrarily to further partition the partitioned sub-tables to form multi-level partitioned tables.

[0035] Range (RANGE) partitioning is to create partitions according to the value range of a column or several columns. When a user writes data to a table, the database server will judge according to the value on these columns and write the data to the corresponding partition.

[0036] When partitioning a structured query sub-table, there are usually some filtering conditions for column data. Since the range partitioned table is partitioned according to the value range of the partition column, when there are some range-related filtering conditions on the partition column of the partitioned table, the following situation may occur: all data in some partition sub-tables meet the condition, while all data in some other partition sub-tables do not meet the condition. For this kind of situation, the filtering condition on the partitioned table is equivalent to always true or always false, so there is no need to judge the data in the table one by one.

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

[0038] Execute the SQL query statement: query all records with C2 column value less than 18 from the P1 partition of the partitioned table T3.

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

[0040] Embodiment One

[0041] Figure 1A flowchart of a filtering condition rewriting method is provided for Embodiment One of the present application. The present embodiment can be applied to optimize filtering conditions. The method can be executed by a filtering condition rewriting device, which can be implemented in the form of hardware and / or software, and can be configured in an electronic device. The electronic device can be a computer, a personal digital assistant, a server, or the like. As shown in FIG. 1, the method comprises the following steps. Figure 1

[0042] S110, obtaining a filtering condition in a structured query statement, wherein the structured query statement comprises a statement for querying a range partition table.

[0043] In the present embodiment, the structured query statement can be a query statement composed of SQL. The structured query statement is used to query data in a database that meets the filtering condition. The filtering condition can be a conditional statement used to filter and limit the query result. When querying a range partition table, the relevant data partition can be quickly located by using the filtering condition for judgment, thereby reducing the query time. The range partition table can be a data table divided according to the continuous value range of a certain key column in the data table. The range data table can be divided into multiple sub-tables according to 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 structured query statement is obtained, the structured query statement is parsed, and the filtering condition contained in the structured query statement is extracted. The structured query statement can be a query statement for a range partition table, which can be input by a user of the database or generated by the database, and the present application does not limit this.

[0045] S120, in the case where the filtering condition meets an optimization condition, the filtering condition is divided into a condition unit, the condition unit comprises a logical operation unit in the filtering condition that cannot be further divided by a divider, and the condition unit does not contain a member condition.

[0046] In the present embodiment, the optimization condition can be a judgment condition for evaluating whether the filtering condition can be rewritten. The optimization condition can be, for example, a condition in which the format and / or content of the filtering condition meet the requirements. The condition unit can be a basic unit obtained by dividing the filtering condition by a divider. The condition unit comprises a query-limited numerical range, which is represented in the form of containing a comparison operator (such as <, >, or =, etc.). The divider can be a logical operator used to divide the filtering condition. The divider includes but is not limited to AND or OR. The member condition can be a condition for querying whether a member (which can be represented as a constant) in the condition exists in a specific data column of the range partition table. For example, the member condition can be in the form of IN(0, 1), and the members are 0 and 1.

[0047] ​Specifically, when the format and / or content of the filter condition meets the requirement of the optimization condition, the filter condition is divided into at least one condition unit according to the division symbol. It is worth noting that when there is a member condition in the filter condition, the member condition is replaced by an equal expression of each member in the expression when the filter condition is divided.

[0048] For example, the expression of IN(0, 1) contains members 0 and 1, which are replaced by =0 and =1 when the filter condition is divided.

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

[0050] In this embodiment, the partition column can be at least one specific data column designated as the basis for data partitioning in a range partitioned table. The partition column of the range partitioned table can be obtained by searching the metadata in the database. The specific value in the partition column, i.e. the partition value, and the value range of the partition value are the basis for dividing the data partition.

[0051] Specifically, the optimization condition includes: the filter condition is to query whether the partition value in the query partition column belongs to a certain range of data, and / or whether the partition value in the query partition column is equal to the member value in the member condition.

[0052] For example, the partition column of the first-level partition of table T1 is C1, and the partition column of the second-level partition is C2. The filter condition is to query all records from the sub-partition P1_P11 (denoted as T1_P1_P11) of table T1 that satisfy C1<15 AND C2 IN (0, 1) at the same time. The filter condition C1<15 meets the optimization condition, which is a comparison between a partition column and a constant value. The filter condition C2 IN (0, 1) meets the optimization condition, which is a comparison between a partition column and a member condition. The filter condition meets the optimization condition, and the filter condition division can be performed. The division symbol is AND, and the filter condition can be divided into three condition units: C1<15, C2=0 and C2=1.

[0053] S130, for each condition unit, when the form of the condition unit is a Boolean expression containing a column name and a constant, determining a current partition table in which the target column corresponding to the column name is located, determining a current partition column of the current partition table, and determining an optimization mark of the condition unit by comparing the current partition column and the target column.

[0054] In the embodiment, the column name can be the name of a data column indicated by the condition unit and required to be queried. By judging whether the value in the data column satisfies the condition indicated by the condition unit, the data row corresponding to the value satisfying the condition is filtered out as the query result and output. The target column can be a specific data column identified by the column name. The target column indicated by the column name can be located in the range partition table where the target column is located, i.e., the current partition table, the partition column of the current partition table, i.e., the current partition column. The optimization mark can be a mark indicating whether the current partition table can be queried according to the condition unit. The optimization mark can be of a Boolean type.

[0055] Specifically, all condition units are traversed, and it is checked whether the current condition unit is a Boolean expression containing a column name and a constant. If not, the next condition unit is traversed, and the current condition unit is not rewritten. If yes, the target column corresponding to the column name and the current partition table where the target column is located are found from the meta array in the database according to the column name in the condition unit, the current partition column of the current partition table is determined, and if the current partition column and the target column indicated by the column name in the condition unit are the same data column, the optimization mark of the current condition unit is determined, which indicates that the current condition unit can be rewritten. If not, the optimization mark of the current condition unit is determined, which indicates that the current condition unit is retained and not rewritten.

[0056] S140, for each condition unit, if the optimization mark corresponding to the condition unit is first mark information, and the partition base table of the current partition table corresponding to the condition unit is a range partition table, the constant and the comparison operator in the condition unit are obtained, the current lower-level partition table is selected from the lower-level partition tables 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 mark information indicates that the condition unit is to be optimized.

[0057] In the embodiment, the first mark information can be information indicating that the current partition column and the target column are the same data column. The first mark information can be of a Boolean type, for example, TRUE. The partition base table can be the main table of the range partition table in logic. The partition base table is divided into a plurality of lower-level partition tables according to the numerical range of the partition value in the partition column. The lower-level partition table can be a data table 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 table. The lower-level partition table includes but is not limited to the table identifier of the lower-level partition table, the partition value limit of the partition column of the lower-level partition table, and the identifier of whether the lower-level partition table contains the partition value limit. The optimization indication information can be information indicating that the condition unit can be rewritten. The optimization indication information can be of a Boolean type, for example, TRUE or FALSE.

[0058] Specifically, if the optimization mark corresponding to the condition unit is the first mark information, a partition base table is determined according to the current partition table, if the partition base table is a range partition table, the current condition unit is parsed to obtain the constant and the comparison operator in the condition unit. All subordinate partition tables of the partition base table are found (from the meta array), the partition information of each corresponding subordinate partition table in the meta array is obtained, the range indicated by the partition information of the subordinate partition table is compared with the range of the condition unit indicated by the constant and the comparison operator in the condition unit, according to the intersection relationship of the two ranges, the optimization indication information of the current condition unit is determined. When the intersection relationship of the two ranges is no intersection, the optimization indication information indicates that the current subordinate partition table does not contain the partition value satisfying the current condition unit, and the current subordinate partition table is not queried; when the intersection relationship of the two ranges is that the partition value range is a subset of the range of the condition unit, the optimization indication information indicates that all data in the current subordinate partition table satisfy the query condition indicated by the condition unit, and the current subordinate partition is directly returned without querying the current subordinate partition table; when the intersection relationship of the two ranges is partial intersection, the optimization indication information indicates that the current condition unit is not rewritten and the current condition unit is retained.

[0059] S150, rewriting the filter condition based on each optimization indication information to obtain a rewritten filter condition.

[0060] Specifically, all condition units are traversed, and the condition units are rewritten one by one according to the optimization indication information, and after all the rewriting is completed, the rewritten filter condition is obtained.

[0061] The technical scheme of the embodiment of the present application comprises the following steps: obtaining a filter condition in a structured query statement; in the case that the filter condition meets an optimization condition, the filter condition is divided into condition units; for each condition unit, in the case that the form of the condition unit is a Boolean expression containing a column name and a constant, a current partition table in which a target column corresponding to the column name is located is determined, a current partition column of the current partition table is determined, and an optimization mark of the condition unit is determined by comparing the current partition column and the target column; only the filter condition meeting the condition is rewritten and optimized to improve the query efficiency; for each condition unit, in the case that the optimization mark corresponding to the condition unit is first mark information and the partition base table of the current partition table corresponding to the condition unit is a range partition table, a constant and a comparison operator in the condition unit are obtained, a current lower-level partition table is selected from lower-level partition tables of the partition base table, and optimization indication information of the condition unit is determined according to the constant, the comparison operator and partition information of the current lower-level partition table; whether the condition unit is optimized and how the condition unit is optimized are determined by determining the intersection relationship between the range in which the current lower-level partition table is located and the range of the filter condition, partition pruning of the data table is completed, the data processing amount is reduced, the query efficiency is further improved, and the computing resource consumption of the system is reduced; the filter condition is rewritten based on the optimization indication information, the rewritten filter condition is obtained, the rewriting and optimization of the filter condition are completed, and the accuracy of the partition pruning is improved.

[0062] Embodiment two

[0063] Figure 2 A flowchart of a filter condition rewriting method provided by the second embodiment of the present application is described in detail below. The second embodiment is an optimization based on any of the above-mentioned embodiments, and mainly comprises the following steps: determining a condition unit, an optimization mark, a partition base table and a current partition table, judging a locating attribute, rewriting and simplifying a filter condition. It should be noted that technical details not described in detail in the second embodiment can be found in any of the above-mentioned embodiments. As shown in the following table, the method comprises the following steps: Figure 2

[0064] S210, obtaining a filter condition in a structured query statement, wherein the structured query statement comprises a statement for querying a range partition table.

[0065] S220, dividing the filter condition by a division symbol in the filter condition to obtain an initial division unit.

[0066] In the second embodiment, the initial division unit can be a unit obtained by preliminary division according to the division symbol. If the initial division unit contains a member condition, the initial division unit needs to be further divided.

[0067] ​Specifically, the filtering condition is divided according to the separator in the filtering condition to obtain at least one initial division unit.

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

[0069] S230, for each initial division unit, if the initial division unit contains a member condition, rewriting the member condition into an expression composed of a constant and a column in the member condition, and taking the rewritten expression as a condition unit, otherwise, taking the initial division unit as a condition unit.

[0070] Specifically, if the initial division unit includes a member condition, the initial division unit is rewritten into an expression composed of a numerical constant of each member in the member condition and a column name, and the rewritten expression is taken as a condition unit. If it does not include a member condition, the initial division unit is taken as a condition unit.

[0071] For example, the initial division unit is C1<15 and C2 IN (0,1). C1<15 does not include a member condition, so C1<15 is taken as a condition unit. C2 IN (0,1) includes a member condition, and the numerical constant of the member is 0 and 1, so it is rewritten into C2=0 and C2=1, C2=0 and C2=1 do not include a member condition, so C2=0 and C2=1 are taken as condition units. The final all condition units are: C1<15, C2=0 and C2=1.

[0072] S240, determining whether the current partition column and the target column are the same.

[0073] Specifically, it is determined whether the current partition column of the current partition table and the target column indicated by the condition unit are the same.

[0074] S250, if yes, determining that the optimization mark of the condition unit is the first mark information, and determining the upper partition table of the current partition table as the partition base table of the current partition table, and executing S280.

[0075] Specifically, if the target column and the current partition column are the same column, the optimization mark is set to the first mark information, and the upper partition table of the current partition table found from the metadata is determined as the partition base table.

[0076] S260, if not, determining whether the current partition table is a partition root table, if yes, determining the optimization mark of the condition unit as second mark information; otherwise, taking the upper level partition table of the current partition table as a new current partition table, and returning to continue comparing the current partition column of the new current partition table with the target column, determining the optimization mark of the condition unit, and performing S2120.

[0077] In the embodiment, the partition root table can be a table without an upper level partition table. The partition root table can be a logical main table at the top level of the range partition table hierarchy. The second mark information can be information indicating that the current partition column and the target column are not the same data column. The second mark information can be of a Boolean type, for example, FALSE.

[0078] Specifically, if the target column and the current partition column are not the same column, the upper level partition table of the current partition table is searched from the metadata, if there is no upper level partition table, the current partition table is a partition root table, and the optimization mark is set as the second mark information; if the upper level partition table is found, the upper level partition table is taken as the current partition table, the current partition column thereof is obtained, and it is continued to judge whether the target column and the current partition column are the same column until they are the same column or the current partition table is a partition root table, and the optimization marks of all the condition units are determined.

[0079] For example, a range partition table T1 is created, which contains three sub-partitions P1 (C1<10), P2 (10≤C1<20) and P3 (C1≥20) divided by integer values of C1 column, and each sub-partition contains two lower level sub-partitions P11 (C2<1) and P12 (C2≥1) divided by C2 column. The condition units are C1<15, C2=0 and C2=1, and the current partition table is T1_P1_P11. For the condition unit indicating C1<15, at this time the target column is C1, the current partition column of T1_P1_P11 is C2, and they are different, so the upper level partition table of T1_P1_P11 is searched as T1_P1, T1_P1 is taken as the current partition table, the current partition column is C1, which is the same as the target column, so the partition base table is set as the upper level partition table of T1_P1, that is, T1, and the optimization mark of the condition unit is determined as the first mark information. For the condition unit indicating C2=0, at this time the target column is C2, the current partition column of T1_P1_P11 is C2, and they are the same, so the upper level partition table of T1_P1_P11, that is, T1_P1, is set as the partition base table, the optimization mark of the condition unit is determined as the first mark information, and the current partition table is T1_P1_P11. Similarly, for the condition unit indicating C2=1, at this time the partition base table is T1_P1, and the optimization mark of the condition unit is determined as the first mark information.

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

[0081] Specifically, when the optimization mark of the condition unit is the second mark information, the condition unit is reserved and is not rewritten.

[0082] S280, for each condition unit, if the optimization mark corresponding to the condition unit is the first mark information, and the partition base table of the current partition table corresponding to the condition unit is a range partition table, the constant and the comparison operator in the condition unit are obtained, if the table name of the current lower-level partition table is the same as the table name of the current partition table, it is determined that the positioning attribute of the current lower-level partition table is the first attribute; otherwise, it is determined that the positioning attribute of the current lower-level partition table is the second attribute.

[0083] The partition information includes a table name, a partition value limit, and a boundary value mark.

[0084] In the embodiment, the positioning attribute can be an attribute indicating whether to locate to the current partition table. The positioning attribute can be the first attribute or the second attribute. The first attribute can be an attribute indicating locating to the current partition table. At this time, the current lower-level partition table is the current partition table. The first attribute can be of a Boolean type, for example: TRUE. The second attribute can be an attribute indicating not locating to the current partition table. At this time, the current lower-level partition table is not the current partition table. The second attribute can be of a Boolean type, for example: FALSE. The partition value limit can be the upper limit of the range of the partition value of the lower-level partition table. Since the sub-tables of the range partition table are arranged in the order of the range of the partition value, the partition value limit of the previous lower-level partition table is certainly not greater than the partition value limit of the next lower-level partition table in the two adjacent lower-level partition tables, and the partition value limit of the previous lower-level partition table is the lower limit of the range of the partition value of the next lower-level partition table. The boundary value mark can be a mark indicating whether the lower-level partition table contains the partition value limit (i.e. the boundary value). The boundary value mark can be of a Boolean type.

[0085] Specifically, all lower-level partition tables under the partition base table are traversed, the current traversed lower-level partition table is set as the current lower-level partition table, and whether to locate to the current partition table is determined by judging whether the table name of the current partition table is the same as the table name of the current lower-level partition table, and the positioning attribute of the current lower-level partition table is set as the first attribute or the second attribute.

[0086] S290, a size comparison is performed between the constant and the partition value limit, and a comparison result is obtained.

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

[0088] S2100, when the comparison result indicates that the constant is greater than or less than the partition value limit, determining the optimization indication information of the condition unit according to the locating attribute of the current lower-level partition table and the comparison operator, and executing 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 sequentially traversed until the locating attribute indicates that the current lower-level partition table is located to the current partition table, so as to determine the intersection relationship between the partition value range of the current lower-level partition table and the range of the condition unit; if the comparison result indicates that the constant is less than the partition value limit, the intersection relationship between the partition value range of the lower-level partition table and the range of the condition unit can be determined regardless of whether the current partition table is located. According to the intersection relationship, the optimization indication information is determined.

[0090] S2110, when the comparison result indicates that the constant is equal to the partition value limit, determining the optimization indication information of the condition unit according to the locating attribute of the current lower-level partition table, the boundary value mark and the comparison operator, the boundary value mark indicating 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 limit, the intersection relationship between the partition value range of the lower-level partition table and the range of the condition unit needs to be specifically judged in combination with the locating attribute, the boundary value mark and the comparison operator. According to the intersection relationship, the optimization indication information is determined.

[0092] S2120, for each condition unit in the filter condition, if the optimization indication information indicates that the condition unit does not need to be rewritten, the original expression corresponding to the condition unit is retained in the filter condition; otherwise, one of S2130 or S2140 is executed.

[0093] In this embodiment, the original expression can be an expression in the condition unit that is not modified. The original expression includes a partition column, a comparison operator and a constant.

[0094] Specifically, when the optimization indication information indicates that the condition unit does not need to be rewritten, the original expression in the condition unit is retained and not modified. When the optimization indication information indicates that the condition unit 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 the first expression or the second expression, replacing the original expression corresponding to the condition unit in the filter condition with the indicated expression, and performing S2150.

[0096] In the embodiment, the first expression can be an expression indicating that the current lower-level partition table does not satisfy the query condition indicated by the condition unit, and the current lower-level partition table is not queried. When the optimization indication 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 satisfy the query condition indicated by the condition unit, and the current lower-level partition table is not queried. When the optimization indication 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 the first expression or the 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, removing the constant included in the condition unit from the corresponding position in the filter condition, and performing S2150.

[0099] Specifically, when the original expression is a member condition, and the optimization indication information indicates that the expression of the condition unit is the first expression, it is equivalent to that the range partition table to be queried does not include the member value in the member condition. At this time, the constant (i.e. the 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 filter condition is C2 IN (0, 1), the condition unit is C2=0 and C2=1, and 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, the member 1 in the member condition is directly deleted, and the filter condition is C2 IN (0).

[0101] S2150, simplifying the rewritten filter condition according to the Boolean operation rule.

[0102] Specifically, if the rewritten condition element is TRUE and the separator is OR, the filter condition can be simplified to TRUE, i.e. all the contents in the range query table indicated by the filter condition satisfy the condition element, and the output query result contains all the range query table; if the rewritten condition element is TRUE and the separator is AND, the condition element can be deleted, and only other condition elements are queried; if the rewritten condition element is FALSE and the separator is AND, the filter condition can be simplified to FALSE, i.e. all the contents in the range query table indicated by the filter condition do not satisfy the condition element, and the output query result is 0; if the rewritten condition element is FALSE and the separator is OR, the condition element can be deleted, and only other condition elements are queried.

[0103] The technical scheme of the embodiment of the present application comprises the following steps: obtaining a filter condition in a structured query statement; splitting the filter condition by a split symbol in the filter condition to obtain an initial split unit; for each initial split unit, if the initial split unit contains a member condition, rewriting the member condition into an expression composed of constants and columns in the member condition, and taking the rewritten expression as a condition unit, otherwise, taking the initial split unit as a condition unit, so as to ensure that the split condition unit is the most basic unit, facilitate rewriting of a subsequent condition unit, and further improve query efficiency; determining whether the current partition column and the target column are the same, and judging whether the condition unit can be rewritten for the current partition table; if yes, determining that the optimization mark of the condition unit is first mark information, and determining the upper-level partition table of the current partition table as a partition base table of the current partition table; if no, determining whether the current partition table is a partition root table; if yes, determining that the optimization mark of the condition unit is second mark information; otherwise, taking the upper-level partition table of the current partition table as a new current partition table, and returning 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 mark information indicates that the condition unit cannot be optimized, and by further determining whether the current partition table satisfies a rewriting condition, the computing resource consumption of a database system is reduced, and the resource utilization rate of the database system is optimized; if the table name of the current partition table is the same as the table name of the current lower-level partition table, determining that the positioning attribute of the current lower-level partition table is a first attribute; otherwise, determining that the positioning attribute of the current lower-level partition table is 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, determining optimization indication information of the condition unit 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, determining the optimization indication information of the condition unit according to the positioning attribute of the current lower-level partition table, the boundary value mark and the comparison operator, by judging an intersection relationship, determining optimization indication information, selectively searching a range partition table, and improving query efficiency and further reducing computing consumption; for each condition unit in the filter condition, selectively rewriting an original expression according to the optimization indication information, simplifying an execution plan during query, and improving query efficiency; simplifying the rewritten filter condition according to a Boolean operation rule, increasing stability of a query execution plan, eliminating redundant condition units, and improving query performance.

[0104] Embodiment three

[0105] Figure 3A flowchart of a filtering condition rewriting method provided for the third embodiment of the present application is provided, and the embodiment is optimized on the basis of any of the above embodiments, and mainly includes detailed description of the process. It should be noted that technical details not described in detail in this embodiment can be seen in any of the above embodiments. As shown in the following, the method includes: Figure 3

[0106] S310, obtain the filtering condition in the structured query statement, the structured query statement including a statement for querying the range partition table.

[0107] S320, in the case that the filtering condition meets the optimization condition, split the filtering condition into a condition unit, the condition unit including a logical operation unit in the filtering condition that cannot be further split by a split symbol, and the condition unit not containing a member condition.

[0108] S330, for each condition unit, in the case that the form of the condition unit is a Boolean expression containing a column name and a constant, determine a current partition table in which a target column corresponding to the column name is located, determine a current partition column of the current partition table, and determine an optimization mark of the condition unit by comparing the current partition column and the target column.

[0109] S340, for each condition unit, if the optimization mark corresponding to the condition unit is first mark 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 the 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, determine that the positioning attribute of the current lower-level partition table is a first attribute; otherwise, determine that the positioning attribute of the current lower-level partition table is a second attribute.

[0110] S350, perform size comparison between the constant and the partition value limit to obtain a comparison result.

[0111] S360, in the case that the comparison result indicates that the constant is greater than the partition value limit, if the positioning attribute is the second attribute, take the next lower-level partition table as a new current lower-level partition table, and continue to perform the optimization indication information determination operation; if the positioning attribute is the first attribute, in the case that the comparison operator is equal, greater than, or greater than or equal to, the optimization indication information indicates that the expression of the condition unit is a first expression; in the case that the comparison operator is not equal, less than, or less than or equal to, the optimization indication information indicates that the expression of the condition unit is a second expression, and S3120 is performed.

[0112] ​Specifically, if the locating attribute is the second attribute, it indicates that the current partition table is not located. Since the lower-level partition tables are arranged in ascending order according to the partition value limits, when sequentially traversing, the partition limit value of the lower-level partition table that is not located is certainly not 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 condition indicated by the condition cell may be located in the lower-level partition table with a larger subsequent partition value limit. Therefore, the traversal needs to be performed until the locating attribute is the first attribute, and then the traversal is terminated. When the current partition table is located, the locating attribute is the first attribute. When the comparison operator is equal, greater than or greater than or equal, it indicates that the partition value range has no intersection with the range indicated by the condition cell. It is determined that the optimization indication information indicates that the expression of the condition cell is the first expression. When the comparison operator is not equal, less than or less than or equal, it indicates that the partition value range is a subset of the range indicated by the condition cell. It is determined that the optimization indication information indicates that the expression of the condition cell is the second expression.

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

[0114] In S370, when the comparison result indicates that the constant is less than the partition value limit, if the locating attribute is the first attribute, it is determined that the optimization indication information of the condition cell indicates that the condition cell is not rewritten. If the locating attribute is the second attribute, when the comparison operator is equal, less than or less than or equal, the optimization indication information indicates that the expression of the condition cell is the first expression. When the comparison operator is not equal, greater than or greater than or equal, the optimization indication information indicates that the expression of the condition cell is the second expression, and S3120 is performed.

[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, if 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 flag indicates that the current lower-level partition table does not contain a boundary value, if the comparison operator is equal, not equal, less than or equal, or greater than, a next lower-level partition table is selected as a new current lower-level partition table, if the comparison operator is less than, it is determined that 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, it is determined that the optimization indication information indicates that the expression of the condition unit is the second expression, and S3120 is executed.

[0125] 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 flag indicates that the current lower-level partition table does not contain a boundary value: if the comparison operator is equal, not equal, less than or equal, or greater than, it is impossible to determine whether the subsequent lower-level partition contains data satisfying the query condition indicated by the condition unit only by the current lower-level partition, and at this time, it is necessary to continue to traverse to avoid query omission; if the comparison operator is less than, it can be directly judged that the partition value range has no intersection with the range indicated by the condition unit, it is determined that 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, the partition value range is a subset of the range indicated by the condition unit, and it is determined that the optimization indication information indicates that the expression of the condition unit is the second expression.

[0126] S3120, the filter condition is rewritten based on each optimization indication information, and a rewritten filter condition is obtained.

[0127] S3130, if all the expressions corresponding to the condition units obtained by splitting the original expression of the condition unit are the first expression, the original expression of the condition unit is rewritten as the first expression.

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

[0129] For example, for the case where the original expression contains a member condition, if each member in the member condition can be optimized to the first expression, i.e. FALSE, the original expression is directly rewritten as FALSE.

[0130] The technical scheme of the embodiment of the present application comprises the following steps: obtaining a filter condition in a structured query statement; in the case that the filter condition meets an optimization condition, the filter condition is divided into condition units; for each condition unit, in the case that the form of the condition unit is a Boolean expression containing a column name and a constant, a current partition table in which a target column corresponding to the column name is located is determined, a current partition column of the current partition table is determined, an optimization mark of the condition unit is determined by comparing the current partition column with the target column; in the case that the table name of the current partition table is the same as the table name of the current lower-level partition table, a positioning attribute of the current lower-level partition table is determined as a first attribute; otherwise, the positioning attribute of the current lower-level partition table is determined as a second attribute; a comparison result is obtained by comparing the constant with a partition value limit; in the case that the comparison result indicates that the constant is greater than or less than the partition value limit, optimization indication information is determined according to a range indicated by the constant and a comparison operator in the condition unit and a range of a partition column value of the current lower-level partition table, and the positioning attribute is analyzed, and selective query is performed, so that all range partition tables are avoided from being traversed, and the query efficiency and accuracy are improved; in the case that the comparison result indicates that the constant is equal to the partition value limit, the optimization indication information is determined according to the positioning attribute, the comparison operator and a boundary value mark, and a lower-level partition sub-table that can be ignored is pruned, so that all range partition tables are avoided from being traversed, and the query efficiency and accuracy are improved; the filter condition is rewritten based on the optimization indication information, and a rewritten filter condition is obtained; in the case that all condition units corresponding to an original expression corresponding to the condition unit are first expressions after the original expression corresponding to the condition unit is split, the original expression of the condition unit is rewritten as the first expression, and the computational resource consumption is reduced by using an execution plan in the optimization query, and the query efficiency is further improved.

[0131] The present application is exemplarily described below, in the embodiment, a "minimum unit" represents a "condition unit", an "in condition", an "in lst" or an "lst" represents a "member condition", and an "operator" represents a "comparison operator":

[0132] The present application provides a filter condition rewriting method, for a filter query performed on m-level partition sub-tables, when a filter condition is a range-related operation and a column of the filter condition is an n (n<=m) level range partition column, optimization of the filter condition can be attempted, so that a redundant condition screening process is avoided as much as possible, and resource consumption in the query range partition table process is reduced.

[0133] In the present application, a minimum unit in an AND / OR connection item is a logical operation unit (i.e. a condition unit) that cannot be further divided by using AND or OR as a divider. For example, the minimum units after A and (B or (C and D)) are divided are A, B, C and D.

[0134] The present application filters the SQL statement related to the range partition sub-table data query, analyzes the filtering condition, and screens the filtering condition that can be optimized. After the filtering condition analysis, partition pruning optimization is performed.

[0135] First, the filtering condition analysis is performed:

[0136] Only the comparison of the partition column and the constant value (<, <=, >, >=, =) or the in condition comparison is supported, and the logical operation combination of the two filtering conditions can be connected by AND / OR.

[0137] ① For the case where the filtering condition is an and or or expression, the minimum filtering condition unit under each and or or branch is optimized respectively.

[0138] ② For the filtering condition in the form of col (i.e. the partition column (column, col)) in lst, the col = constant expression is constructed for each constant (i.e. member) in lst, and the optimization of the expression is attempted. If the optimization is FALSE (i.e. the optimization indication information indicates that the expression is the first expression), the constant can be removed from lst, and if each constant in lst can be optimized to FALSE, the in lst expression can be optimized to FALSE.

[0139] After the filtering condition transformation, optimization is attempted for each minimum unit of the filtering condition.

[0140] The range partition table can be optimized by judging the interval relationship between the filtering condition and the partition range (i.e. the range of the partition value).

[0141] 1. If the partition column value range of the partition sub-table is a subset of the filtering condition range, the filtering condition is optimized to TRUE (i.e. the expression is the second expression);

[0142] 2. If the partition column value range of the partition sub-table has no intersection with the filtering condition range, the filtering condition is optimized to FALSE (i.e. the expression is the first expression);

[0143] 3. If the partition column value range of the partition sub-table is not a subset of the filtering condition range, and the partition column value range of the partition sub-table has intersection with the filtering condition range, no optimization is performed.

[0144] The following example partition table T1 is given: T1 is divided into three partition sub-tables P1 (C1<10), P2 (10≤C1<20) and P3 (C1≥20) according to the integer value of C1 column;

[0145] Each partition sub-table is further divided into two second-level partition sub-tables according to the C2 column: P11 (C2<1) and P12 (C2≥1).

[0146] The filtering condition rewriting can be performed according to the following steps:

[0147] (1) The SQL statement for querying the partition sub-table data is executed, and the partition pruning optimization is started. For example, all records satisfying C1<15 and C2 value being 0 or 1 are queried from the sub-partition P1_P11 of the table T1.

[0148] (2) The filtering condition is analyzed initially, and is cut into minimum units connected by AND / OR (i.e. the separator). Each filtering condition minimum unit in the filtering condition is traversed, and is set as the current condition cdt (when the minimum unit is in the form of "in lst", each constant in the lst is constructed into "=constant expression" form, and is set as the current condition cdt one by one), and each cdt performs step (3) to attempt optimization. For example, the filtering condition in the example statement is C1<15 AND C2 IN (0, 1), and the cdt for attempting optimization one by one is C1<15, C2=0 and C2=1.

[0149] (3) It is judged whether the current cdt is in the form of column and constant Boolean expression, for example, c1=10. If yes, step (4) is performed, and if not, step (14) is performed. For example, the first traversal is C1<15, which meets the condition; the second traversal is C2=0, which meets the condition; and the third traversal is C2=1, which meets the condition.

[0150] (4) 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 are parsed, and the current processing table is set as the table tab. For example, C1<15: tab: T1_P1_P11; the current processing table: T1_P1_P11; C2=0: tab: T1_P1_P11; the current processing table: T1_P1_P11; and 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 the optimization can be performed at the current level of partitioning, 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, and execute step (6); if it is different from col, if the current processing table is a 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 a partition root table, get the upper level partition table of the current processing table, and set it as the current processing table tab, and continue step (5).

[0152] Example: C1<15: the first time part_col is C2; col is C1, and the two are inconsistent, and cannot be optimized; tab is updated to the upper level partition table T1_P1, and the second time judgment is performed. The second time part_col is C1, and is consistent with col, then the partition base table base_tab takes T1, the partition sub-table sub_tab takes T1_P1, and the optimization flag is TRUE. C2=0: the first time part_col is C2, col is C2, and the two are consistent, the partition base table base_tab can be directly set to T1_P1, the partition sub-table sub_tab takes T1_P1_P11, and the optimization flag is 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, execute step (14), otherwise execute step (7). Example: C1<15: the optimization flag is TRUE, and the partition base table T1 is a range partition formed by taking C1 as the partition column. C2=0: the optimization flag is TRUE, and the partition base table T1_P1 is a range partition formed by taking C2 as the partition column. C2=1: same as C2=0.

[0154] (7) Get the constant e and the comparison operator ops in the filtering condition expression. And 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 takes 15, and ops takes '<'; last_tab=T1_P1. C2=0: e takes 0, and ops takes '='; last_tab=T1_P1_P11. C2=1: same as C2=0.

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

[0156] Example: The values of each lower partition are listed here. In fact, this example does not traverse all lower partitions. C1<15: The lower partitions of base_tab are T1_P1, T1_P2, T1_P3, and the values are shown in Table 1:

[0157] Table 1 T1 partition information table

[0158]

[0159] C2=0: The lower partitions of base_tab are T1_P1_P11, T1_P1_P12, and the values are shown in Table 2:

[0160] Table 2 T1_P1 partition information table

[0161]

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

[0163] (9) Determine the relationship between the constant e and the partition value part_value of the partition table being traversed (i.e. the current lower partition table). If e < part_value, perform (10); if e = part_value, perform (11). Otherwise, perform (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, and (12) is performed. 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, and (10) is performed. 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, and (11) is performed.

[0165] (10) If the current sub-partition is located (i.e. the current partition table is located, and the locating 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, and the locate 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 >=, generate the TRUE expression (i.e., the second expression), go to (13).

[0169] Example: C2 = 0 jumps here, since the current subpartition is located, no optimization is performed.

[0170] (11) If the current subpartition is located and the partition contains the 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 the TRUE expression, go to (13);

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

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

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

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

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

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

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

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

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

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

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

[0184] Example: C2 = 1: locate to the current sub-partition, and does not contain the boundary value, generate a FALSE expression.

[0185] (12) If locate to the current sub-partition:

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

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

[0188] If not locate to the current sub-partition, set last_tab to the next lower sub-partition of base_tab, go to (8) to continue the traversal;

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

[0190] (13) Analyze the newly generated expression, and replace the expression of the original filter condition. Go to (14). The detailed replacement rules are as follows: for the case that the original expression form of cdt is a Boolean expression of column and constant, and is optimized to TRUE or FALSE expression, directly replace the filter expression with TRUE / FALSE. For the case that the original expression form of cdt is in expression form, and is optimized to FALSE, remove the value from the in list in the original filter condition. (The present application will not optimize the filter condition "= constant" to TRUE) Regardless of the form of the original expression of cdt, when no optimization is performed, the original expression is retained in the original filter condition.

[0191] Example: C1 < 15 is optimized to TRUE, and the original filter condition is replaced with: TRUE AND C2 IN(0, 1). C2 = 0 cannot be optimized, and the original filter condition is not changed; C2 = 1 is optimized to FALSE, because the expression is generated from the in_lst, and 1 is removed from the 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 to the next one, go to (3) to continue to try optimization, when all the minimum filter condition units have been traversed, go to (15)

[0193] (15) If there is a branch for optimization, analyze and simplify the filter expression, and jump to (16). According to the Boolean operation rules, calculate and simplify the optimized filter condition expression, and if there is an in lst expression and all constants in the lst are removed, the in lst expression is optimized to FALSE. For example, the final simplification is: query the partition value of C2 = 0.

[0194] (16) Optimization is completed.

[0195] For example, a range partition table TEST_RANGE is created, and 6 million test data are inserted in batches, and the partition information is shown in Table 3:

[0196] Table 3 TEST_RANGE partition information table

[0197]

[0198] If the filter condition is C2>22, the partition condition of the sub-partition P1 is C2<=10, the partition condition interval (i.e. the partition value range) and the filter condition interval (i.e. the range of the filter condition) have no intersection, the sub-partition table TEST_RANGE_P1 has no data satisfying the filter condition, and the optimization will optimize the filter condition to FALSE.

[0199] The execution plan before optimization is to traverse the TEST_RANGE_P1 table, and the execution time is 1248 ms; the execution plan after optimization is to directly exclude all data in the TEST_RANGE_P1 table without traversal, and the execution time is 28.796 ms.

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

[0201] The execution plan before optimization is to traverse all data in the TEST_RANGE_P2 table, and the execution time is 1851 ms. The execution plan after optimization is to directly output the row number of the TEST_RANGE_P2 table without actually traversing all data, and the execution time is 37.169 ms.

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

[0203] Example Four

[0204] Figure 4A structural schematic diagram of a filtering condition rewriting device provided for Embodiment Four of the present application. As shown in the figure, the device comprises: Figure 4

[0205] An acquisition module 410 is configured to acquire a filtering condition in a structured query statement, the structured query statement comprising a statement for querying a range partition table;

[0206] A splitting module 420 is configured to split the filtering condition into a condition unit if the filtering condition meets an optimization condition, the condition unit comprising a logical operation unit in the filtering condition which cannot be further split by a split symbol, and the condition unit not containing a member condition;

[0207] A determination module 430 is configured to, for each condition unit, determine a current partition table in which a target column corresponding to a column name in the condition unit is located if the condition unit is in a form of a Boolean expression containing the column name and a constant, determine a current partition column of the current partition table, and determine an optimization mark of the condition unit by comparing the current partition column and the target column.

[0208] An optimization module 440 is configured to, for each condition unit, acquire a constant and a comparison operator in the condition unit if an optimization mark corresponding to the condition unit is first mark information and a partition base table of a current partition table corresponding to the condition unit is a range partition table, select a current lower-level partition table from lower-level partition tables of the partition base table, and determine optimization indication information of the condition unit according to the constant, the comparison operator, and partition information of the current lower-level partition table, the first mark information indicating that the condition unit is to be optimized.

[0209] A rewriting module 450 is configured to rewrite the filtering condition based on the optimization indication information to obtain a rewritten filtering condition.

[0210] ​The technical scheme of the embodiment of the present application comprises the following steps: an acquisition module acquires a filter condition in a structured query statement; a segmentation module segments the filter condition into a condition unit under the condition that the filter condition meets an optimization condition; a determination module determines, for each condition unit, a current partition table in which a target column corresponding to a column name of the condition unit is located, a current partition column of the current partition table, and an optimization mark of the condition unit by comparing the current partition column with the target column, so as to perform rewriting optimization on only the filter condition meeting the condition and improve query efficiency; an optimization module, for each condition unit, acquires a constant and a comparison operator in the condition unit, selects a current lower-level partition table from lower-level partition tables of a partition base table of the current partition table corresponding to the condition unit, and determines optimization indication information of the condition unit according to the constant, the comparison operator, and partition information of the current lower-level partition table, so as to determine whether to perform optimization on the condition unit and what kind of optimization to perform by determining an intersection relationship between a range in which the current lower-level partition table is located and a range of the filter condition, complete partition pruning on the data table, reduce data processing amount, further improve query efficiency, and reduce system computing resource consumption; and a rewriting module rewrites the filter condition based on the optimization indication information, obtains a rewritten filter condition, completes rewriting optimization of the filter condition, and improves the accuracy of the partition pruning.

[0211] In another embodiment, the determination module 430 is specifically configured to:

[0212] determine whether the current partition column and the target column are the same;

[0213] if yes, determine that the optimization mark of the condition unit is first mark information, and determine a parent partition table of the current partition table as a partition base table of the current partition table;

[0214] if no, determine whether the current partition table is a partition root table, if yes, determine that the optimization mark of the condition unit is second mark information; otherwise, take the parent partition table of the current partition table as a 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 mark information indicates that the condition unit cannot be optimized.

[0216] In another embodiment, the partition information comprises a table name, a partition value limit, and a boundary value mark, and the optimization module 440 further comprises:

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

[0218] The comparing unit is configured to perform a size comparison between the constant and the partition value limit to obtain a comparison result.

[0219] The second determining unit is configured to determine optimization indication information of the condition unit according to the locating attribute of the current sub-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 configured to determine the optimization indication information of the condition unit according to the locating attribute of the current sub-partition table, the boundary value flag and the comparison operator when the comparison result indicates that the constant is equal to the partition value limit, the boundary value flag indicating whether the current sub-partition table contains a boundary value.

[0221] In another embodiment, the second determining unit is specifically configured to:

[0222] When the comparison result indicates that the constant is greater than the partition value limit, if the locating attribute is the second attribute, a next sub-partition table is taken as a new current sub-partition table, and the optimization indication information determination operation is continued; if the locating attribute is the first attribute, the optimization indication information indicates that an expression of the condition unit is a first expression when the comparison operator is equal, greater than or greater than or equal to; and the optimization indication information indicates that the expression of the condition unit is a second expression when the comparison operator is not equal, less than or less than or equal to.

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

[0224] In another embodiment, the third determining unit is specifically configured to:

[0225] If the comparison result indicates that the constant is equal to the partition value limit, and the locating attribute is the first attribute and the boundary value mark indicates that the current sub-partition table contains a boundary value, when the comparison operator is equal, not equal, less than or greater than or equal, it is determined that the optimization indication information of the condition cell indicates that the condition cell is not rewritten, when the comparison operator is less than or equal, it is determined that the optimization indication information indicates that the expression of the condition cell is the second expression, and when the comparison operator is greater than, it is determined that the optimization indication information indicates that the expression of the condition cell is the first expression.

[0226] If the comparison result indicates that the constant is equal to the partition value limit, and the locating attribute is the first attribute and the boundary value mark indicates that the current sub-partition table does not contain a boundary value, when the comparison operator is equal, greater than or greater than or equal, it is determined that the optimization indication information indicates that the expression of the condition cell is the first expression, and when the comparison operator is not equal, less than or less than or equal, it is determined that the optimization indication information indicates that the expression of the condition cell is the second expression.

[0227] If the comparison result indicates that the constant is equal to the partition value limit, and the locating attribute is the second attribute and the boundary value mark indicates that the current sub-partition table contains a boundary value, when the comparison operator is equal, less than or less than or equal, it is determined that the optimization indication information indicates that the expression of the condition cell is the first expression, and when the comparison operator is not equal, greater than or greater than or equal, it is determined that the optimization indication information indicates that the expression of the condition cell is the second expression.

[0228] If the comparison result indicates that the constant is equal to the partition value limit, and the locating attribute is the second attribute and the boundary value mark indicates that the current sub-partition table does not contain a boundary value, when the comparison operator is equal, not equal, less than or greater than, the next sub-partition table is selected as a new current sub-partition table, when the comparison operator is less than, it is determined that the optimization indication information indicates that the expression of the condition cell is the first expression, and when the comparison operator is greater than or equal, it is determined that the optimization indication information indicates that the expression of the condition cell is the second expression.

[0229] In another embodiment, the apparatus is specifically configured to: the filtering condition is a comparison of a partition column with a constant value, and / or a comparison of a partition column with a member condition.

[0230] In another embodiment, the splitting module 420 is specifically configured to:

[0231] Split the filtering condition by a split symbol in the filtering condition to obtain an initial split cell.

[0232] For each of the initial partition units, if the initial partition unit contains a member condition, rewriting the member condition into an expression consisting of constants and columns in the member condition, and taking the rewritten expression as a condition unit, otherwise, taking the initial partition unit as a condition unit.

[0233] In another embodiment, the rewriting module 450 is specifically configured to:

[0234] For each of the condition units in the filter condition, if the optimization indication information indicates that the condition unit is not to be rewritten, keeping the original expression corresponding to the condition unit in the filter condition, otherwise, performing one of the following operations:

[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 the first expression or the second expression, replacing the original expression corresponding to the condition unit in the filter condition with the indicated expression.

[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 the first expression, removing the constants included in the condition unit from the corresponding positions in the filter condition.

[0237] In another embodiment, the apparatus further comprises:

[0238] The simplifying module is configured to simplify the rewritten filter condition according to Boolean operation rules.

[0239] In another embodiment, the simplifying module is specifically configured to:

[0240] If the expressions corresponding to all the condition units after the original expression corresponding to the condition unit is split are the first expression, rewriting the original expression of the condition unit into the first expression.

[0241] The filter condition rewriting apparatus provided by the embodiments of the present application can perform the filter condition rewriting method provided by any of the embodiments of the present application, and has the corresponding function modules and beneficial effects of the execution method.

[0242] Embodiment five

[0243] Figure 5A structural block diagram of an electronic device with which embodiments of the present application can be implemented is shown. The electronic device is intended to represent various forms of digital computers, such as laptops, desktops, tablets, personal digital assistants, servers, blade servers, mainframes, and other appropriate computers. The electronic device can also represent various forms of mobile devices, such as personal digital assistants, cellular telephones, smartphones, wearable devices (e.g., headsets, glasses, watches, etc.), and other similar computing devices. The components shown here, their connections and relationships, and their functions, are meant to be examples only, and are not intended to limit the implementations of the present application described and / or claimed in this document.

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

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

[0246] The processor 11 can be various general and / or special purpose processing components with processing and computing capabilities. Some examples of the 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 appropriate processor, controller, microcontroller, etc. The processor 11 performs various methods and processes described above, such as a filtering condition rewriting method.

[0247] In some embodiments, a filtering condition rewriting method can be implemented as a computer program tangibly embodied in a computer readable storage medium, e.g., storage unit 18. In some embodiments, parts or all of the computer program can be loaded and / or installed onto electronic device 10 via, e.g., ROM 12 and / or communication unit 19. When the computer program is loaded onto RAM 13 and executed by processor 11, one or more steps of a filtering condition rewriting method as described above can be performed. Alternatively, in other embodiments, processor 11 can be configured to perform a filtering condition rewriting method by other means, e.g., with the aid of firmware.

[0248] Various implementations of the systems and techniques described above can be realized in digital electronic circuitry, integrated circuitry, a Field Programmable Gate Array (FPGA), an Application Specific Integrated Circuit (ASIC), a System on Chip (SOC), a Complex Programmable logic device (CPLD), computer hardware, firmware, software, and / or combinations thereof. These various implementations can include implementation in one or more computer programs that are executable and / or interpretable on a programmable system including at least one programmable processor, which can be special or general purpose, coupled to receive data and instructions from, and to transmit data and instructions to, a storage system, at least one input device, and at least one output device.

[0249] Computer programs used to implement the methods of the present application can be written in any combination of one or more programming languages. These computer programs can be provided to a processor of a general purpose computer, special purpose computer, or other programmable data processing apparatus to produce a machine, such that the computer program, when executed by the processor of the machine, implements the functions / acts specified in the flowcharts and / or block diagrams. The computer program can be executed entirely on a machine, partially on a machine, partially on a machine and partially on a remote machine or entirely on a remote machine or server.

[0250] In the context of the present application, the computer readable storage medium stores computer instructions for causing a processor to implement the filtering condition rewriting method provided by the present application when executed.

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

[0252] The computer readable storage medium can be a tangible medium that can contain or store the computer program for use by or in connection with an instruction execution system, apparatus, or device. The computer readable storage medium can include, but is not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any suitable combination of the foregoing. Alternatively, the computer readable storage medium can be a machine readable signal medium. More specific examples of the machine readable storage medium will include one or more lines of electrical connections, portable computer disks, hard disk drives, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or Flash memory), optical fibers, portable compact disc read-only memories (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.

[0253] To provide for interaction with a user, the systems and techniques described here can be implemented on an electronic device having a display device, e.g., a Cathode Ray Tube (CRT) or a Liquid Crystal Display (LCD) monitor, for displaying information to the user and a keyboard and a pointing device, e.g., a mouse or a trackball, by which the user can provide input to the electronic device. Other kinds of devices can be used to provide for interaction with a user as well; 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 acoustic, speech, or tactile input.

[0254] The systems and techniques described herein can be implemented in a computing system that includes a back end component, e.g., as a data server, or that includes a middleware component, e.g., an application server, or that includes a front end component, e.g., a user computer having a graphical user interface or a Web browser through which a user can interact with an implementation of the systems and techniques described herein, or any combination of such back end, middleware, or front end components. The components of the system can be interconnected by any form or medium of digital data communication, e.g., a communication network. Examples of communication networks include a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0255] The computing system can include clients and servers. A client and server are generally remote from each other and typically interact through a communication network. The relationship of client and server arises by virtue of computer programs running on the respective computers and having a client-server relationship to each other. A server can be a cloud server, also known as a cloud computing server or cloud host, which is a host product in the cloud computing service system, to solve the defects of large management difficulty and weak business scalability in traditional physical host and virtual private server (VPS) services.

[0256] It should be understood that the various forms of flow shown above can be re-ordered, added to, or deleted from without departing from the scope of the present disclosure. For example, the steps recited in the present disclosure can be executed in parallel, executed in sequence, or executed in different orders, as long as the desired results of the technical solutions of the present disclosure can be achieved, and the present disclosure is not limited herein.

[0257] The above detailed description does not constitute a limitation on the protection scope of the present application. 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 replacements, and improvements made within the spirit and principles of the present application shall be included in the protection scope of the present application.

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.

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 table name of the current lower-level partition table is the same as the table 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; 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.

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, The step of rewriting the filtering conditions based on the optimization indication information 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.

9. 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.

10. The method according to claim 9, 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.

11. 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 the optimization indication information to obtain the rewritten filtering conditions.

12. 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 according to any one of claims 1-10.

13. 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-10.

14. 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-10.

Images