Method, medium, product and device for optimizing database operation statements

Abstract

The application provides a database operation statement optimization method, medium, product and equipment. The method comprises the following steps: obtaining a database operation statement to be optimized, wherein the database operation statement comprises a target null judgment function, and the target null judgment function is an expression comprising a null value processing logic; analyzing the target null judgment function to determine the parameter type and operation logic of the target null judgment function; rewriting the target null judgment function into an equivalent conditional judgment expression according to the parameter type and operation logic; and replacing the target null judgment function in the database operation statement with the conditional judgment expression, thereby obtaining an optimized database operation statement. Through the method, the problems of large cardinality estimation error and inaccurate execution cost estimation caused by the use of the null judgment function in the existing database can be effectively solved, the optimizer can correctly identify the query condition and normally enable the index path to perform index scanning, and resource waste caused by full table scanning can be avoided.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method, medium, product, and device for optimizing database operation statements. Background Technology

[0002] In SQL query processing in databases, null value handling is a core part of data operation and conditional filtering. Null detection functions such as NVL, IFNULL, ISNULL, COALESCE, NVL2, and NULLIF can quickly implement logic such as null value replacement, returning the first non-null value, and determining equal null values. They are widely used in various business scenarios such as single-table queries, multi-table joins, and data filtering, and are a fundamental component of database query statements.

[0003] Current database optimizers have significant technical shortcomings in optimizing the execution of such null-checking functions. The database kernel's estimation models for the execution cost and result set cardinality of null-checking functions are not accurate enough, failing to adapt to the special operational logic of null value handling. This easily leads to large cardinality estimation errors, causing the optimizer's generated execution plan to deviate from the optimal solution. Furthermore, the optimizer sets overly strict constraints on the creation and activation of index paths. When the query conditions contain the aforementioned null-checking functions, the optimizer cannot recognize and utilize the index for scanning, and can only execute the query using a full table scan, directly resulting in a significant reduction in query efficiency.

[0004] Existing databases lack a systematic equivalent rewriting scheme for null value handling functions, failing to eliminate the obstacles these functions pose to cardinality estimation and index usage. In high-volume, high-concurrency database applications, these problems are amplified. Full table scans increase execution time and system resource consumption, severely limiting database query performance and business response speed, making it difficult to meet the actual needs of efficient data processing. Therefore, an optimization method for null value handling functions is urgently needed to overcome the bottlenecks of existing technologies. Summary of the Invention

[0005] In view of the above problems, the present invention proposes an optimization method, medium, product and device for database operation statements that overcomes or at least partially solves the above problems.

[0006] One object of this invention is to enable the rewritten database operation statements to be queried using indexes.

[0007] A further objective of this invention is to improve database query efficiency and reduce execution resource consumption.

[0008] Specifically, this invention provides a method for optimizing database operation statements, comprising: Obtain the database operation statement to be optimized. The database operation statement contains a target null check function, which is an expression containing null value handling logic. The target null detection function is analyzed to determine the parameter types and operational logic of the target null detection function; Based on the parameter type and operation logic, the target null function is rewritten into an equivalent conditional expression; By replacing the target null check function in the database operation statement with a conditional expression, an optimized database operation statement can be obtained.

[0009] Optionally, the parameter type includes dual-input parameters, which are a first input parameter and a second input parameter arranged in sequence; The operation logic includes a first operation logic, which returns the first non-empty value according to the order of the input parameters; When the parameter type is two input parameters and the operation logic is the first operation logic, the steps to rewrite the target empty function into an equivalent conditional expression according to the parameter type and operation logic include: Determine whether the target null function forms a comparison expression with the external comparison object; If not, the target null function is equivalently rewritten as a conditional expression combining the first and second decision branches by an OR operation. The first decision branch includes the first input parameter as a conditional expression and the constraint that the first input parameter is a non-null value. The second decision branch includes the constraint that the first input parameter is a null value and the second input parameter as a conditional expression. If so, the target null function is equivalently rewritten as a conditional judgment expression combining the third and fourth judgment branches through an OR operation. The third judgment branch includes an expression for comparing the first input parameter with the external comparison object and a constraint that the first input parameter is a non-null value. The fourth judgment branch includes a constraint that the first input parameter is a null value and an expression for comparing the second input parameter with the external comparison object.

[0010] Optionally, the step of determining whether the target null function constitutes a comparison expression with the external comparison object further includes: Determine whether the first input parameter has been confirmed to not contain null values; If it is determined that the first input parameter does not contain any null values, the first input parameter is directly used as the rewritten result of the target null detection function; If the first input parameter is not determined to be null, the step of determining whether the target null function constitutes a comparison operation expression with the external comparison object is executed.

[0011] Optionally, the parameter type may also include three or more sets of input parameters, which are arranged in a sequential order. The operation logic includes a second operation logic, which judges the input parameters in the order they appear and returns the first non-empty input parameter. If all input parameters are empty, it returns the last input parameter. When the parameter type is multiple sets of input parameters and the operation logic is the second operation logic, the steps to rewrite the target null function into an equivalent conditional judgment expression according to the parameter type and operation logic include: rewriting the target null function into a multi-branch sequential judgment conditional judgment expression that matches the number of input parameters according to the order of the input parameters. In the multi-branch sequential judgment conditional judgment expression, each branch corresponds to a non-null judgment of a set of input parameters. When the first non-null value of an input parameter appears, the corresponding input parameter is output to participate in subsequent operations. Conditional expressions for multi-branch sequential judgments include the CASE WHEN expression.

[0012] Optionally, the parameter type also includes three sets of input parameters, which include baseline input parameters, non-empty branch return parameters, and null value branch return parameters; The operation logic includes a third operation logic, which returns a parameter by either a non-empty branch or an empty branch based on the null value state of the baseline input parameter. When the parameter type has three sets of input parameters and the operation logic is the third operation logic, the steps to rewrite the target empty function into an equivalent conditional expression according to the parameter type and operation logic include: Determine whether the target null function forms a comparison operation expression with the external comparison object; if not, rewrite the target null function equivalently as a two-branch conditional judgment expression. The two-branch conditional judgment expression uses whether the reference input parameter is non-null as the judgment condition. When the reference input parameter is non-null, the parameter returned by the non-null branch is used as the conditional expression; when the reference input parameter is null, the parameter returned by the null branch is used as the conditional expression. If so, the target null function is equivalently rewritten as a two-branch conditional expression. The two-branch conditional expression uses whether the reference input parameter is non-null as the judgment condition. When the reference input parameter is non-null, the non-null branch returns the parameter and performs the corresponding comparison operation with the external comparison object; when the reference input parameter is null, the null branch returns the parameter and performs the corresponding comparison operation with the external comparison object. Two-branch conditional expressions include the CASE WHEN expression.

[0013] Optionally, the parameter type also includes two sets of comparison input parameters, namely a first comparison parameter and a second comparison parameter; The operation logic includes a fourth operation logic, which returns an empty value when the two sets of comparison input parameters are equal, and returns the first comparison parameter when the two sets of comparison input parameters are not equal. When the parameter type consists of two sets of comparison input parameters and the operation logic is the fourth operation logic, the steps to rewrite the target null function into an equivalent conditional expression based on the parameter type and operation logic include: Determine whether the target null function forms a comparison expression with the external comparison object; If not, the target empty function is equivalently rewritten as a two-branch conditional expression. The two-branch conditional expression uses whether the two sets of comparison input parameters are equal as the judgment condition. When the two sets of comparison input parameters are equal, it returns false. When the two sets of comparison input parameters are not equal, it returns the first comparison parameter as the conditional expression. If so, the target empty function is rewritten as a two-branch conditional expression. The two-branch conditional expression uses whether the two sets of comparison input parameters are equal as the judgment condition. When the two sets of comparison input parameters are equal, it returns false. When the two sets of comparison input parameters are not equal, the first comparison parameter is used to perform a comparison operation with the external comparison object.

[0014] Optionally, after the step of rewriting the target null function into an equivalent conditional expression based on the parameter type and operation logic, the following steps are also included: Determine whether the result of the rewritten conditional expression is always true; If the result of the rewritten conditional expression is always true, eliminate the rewritten conditional expression from the database operation statement.

[0015] According to another aspect of the present invention, a computer-readable storage medium is also provided, on which a computer program is stored, wherein the computer program, when executed by a processor, implements the steps of the optimization method for database operation statements described above.

[0016] According to another aspect of the present invention, a computer program product is also provided, comprising a computer program that, when executed by a processor, implements the steps of the optimization method for database operation statements described above.

[0017] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor executes the machine-executable program to implement the steps of the optimization method for database operation statements of any of the above.

[0018] The database operation statement optimization method of this invention involves obtaining the database operation statement to be optimized, which contains a target null check function. The target null check function is an expression containing null value handling logic. The target null check function is parsed to determine its parameter types and operational logic. Based on the parameter types and operational logic, the target null check function is rewritten into an equivalent conditional expression. The conditional expression replaces the target null check function in the database operation statement, resulting in the optimized database operation statement. This method effectively corrects the cardinality estimation error of the null check function by the database optimizer, making cost estimation and result set prediction more accurate, thus optimizing the execution plan generation logic from the root. Simultaneously, it removes the restriction on index path usage by the null check function, allowing queries to trigger index scans instead of full table scans, significantly reducing query time and system resource consumption.

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

[0020] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings: Figure 1 This is a flowchart illustrating a method for optimizing database operation statements according to an embodiment of the present invention; Figure 2 This is a schematic diagram of a computer program product according to an embodiment of the present invention; Figure 3 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and Figure 4 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

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

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

[0023] In the current database field, null value handling is a common technique for data operations and conditional filtering. Null detection functions such as NVL, IFNULL, ISNULL, COALESCE, NVL2, and NULLIF can quickly implement logic such as null value replacement, returning the first non-null value, and determining equal null values. They are widely used in various business scenarios such as single-table queries, multi-table joins, and data filtering, and are a fundamental component of database query statements.

[0024] Specifically: the function `coalesce(expr[, ...])` is generally used to return the first non-empty value; if all values ​​are null, it returns null. The function nullif(expr1, expr2) checks whether expr1 is equal to expr2. If they are equal, it returns null; otherwise, it returns expr1. The function `nvl2(expr, not_null_return_value, null_return_value)` returns `not_null_return_value` if `expr` is not null, and `null_return_value` if `expr` is null. The functions ifnull(expr1, expr2) and isnull(expr1, expr2) both return expr1 if expr1 is not null, and return expr2 otherwise. The function nvl(expr1, expr2) returns expr1 if expr1 is not null, and returns expr2 otherwise.

[0025] However, current database optimizers have significant shortcomings in optimizing this type of null check function: the database kernel cannot accurately estimate the execution cost and cardinality of the result set of the null check function, which can easily lead to large cardinality estimation errors; at the same time, the optimizer sets overly strict constraints on the creation and activation of index paths. When the query conditions contain null check functions, it cannot identify and utilize the index to perform a scan, and can only use a full table scan, which leads to increased query time and excessive system resource consumption. The performance problem is particularly prominent in scenarios with large amounts of data.

[0026] To address the issues of cardinality estimation distortion and the inability to leverage indexes to improve query performance caused by null checks in existing technologies, this invention proposes an optimization method for database operation statements. This method optimizes query performance by parsing and equivalently rewriting the null check function. Specifically, Figure 1 This is a flowchart illustrating a method for optimizing database operation statements according to an embodiment of the present invention, as shown below. Figure 1 As shown, the optimization method for the database operation statement includes at least the following steps S101 to S104.

[0027] Step S101: Obtain the database operation statement to be optimized. The database operation statement to be optimized generally contains a target null check function, which is usually an expression containing null value handling logic, such as NVL, IFNULL, ISNULL, COALESCE, NVL2, NULLIF, LNNVL, etc.

[0028] Database operation statements generally refer to SQL (Structured Query Language). SQL statements have multiple functions such as data manipulation and data definition. This language is interactive and provides great convenience to users. In this step, the database operation statements can generally be obtained by user input or automatically generated by the database program during operation due to business needs. Those skilled in the art can decide on the method of receiving database operation statements according to the actual situation.

[0029] Step S102 involves parsing the target null detection function to determine its parameter types and operational logic. The parameter types refer to the number of input parameters required by the null detection function and the meaning of each parameter, while the operational logic refers to the null value handling rules of the function.

[0030] Step S103: Based on the parameter types and operational logic, rewrite the target null-checking function into an equivalent conditional expression. According to the parsed parameter types and operational logic, the target null-checking function is rewritten into an equivalent conditional expression according to preset rules. These rewriting rules adapt to different parameter types and operational logics, ensuring that the execution results of the statements before and after rewriting are completely consistent.

[0031] Step S104: Replace the target null check function in the database operation statement with a conditional expression to obtain the optimized database operation statement. This optimized statement allows the database optimizer to accurately estimate cardinality and properly utilize indexes to execute queries, thereby improving execution efficiency.

[0032] Since the target null detection function has various parameter types and different operational logics, the following describes in detail the specific optimization process of various null detection functions with different embodiments: If the target null detection function has two input parameters (i.e., the first and second input parameters are arranged in sequence), and the operation logic is to return the first non-null value in the order of the input parameters, possible examples of this type of null detection function include NVL, IFNULL, ISNULL, COALESCE (two input parameters), LNNVL (two input parameters), etc. When rewriting this type of function, first determine whether the target null detection function forms a comparison expression with the external comparison object: If no comparison operation expression is formed, the function is equivalently rewritten as a conditional judgment expression of the first judgment branch OR the second judgment branch, where the first judgment branch is the first input parameter combined with the first input parameter not empty restriction, and the second judgment branch is the second input parameter combined with the first input parameter not empty restriction; An optional SQL example is shown in equation (1): SELECT * FROM t1 WHERE NVL(id, 2); Formula (1) The SQL statement shown in Equation (1) contains a null check function NVL(id,2), which prevents the use of the index during execution. By using the method of this invention, it is equivalently rewritten as a conditional expression of the first judgment branch OR the second judgment branch. The final SQL statement is shown in Equation (2): SELECT * FROM t1 WHERE (id IS NOT NULL AND id) OR (id IS NULL AND 2); Formula (2) The condition id IS NOT NULL in the SQL statement shown in Equation (2) can be directly recognized by the optimizer, thereby triggering an index scan and avoiding the large amount of data reading overhead caused by a full table scan. At the same time, the optimizer can accurately estimate the number of rows in the result set based on the modified expression, making the cost estimate more in line with the actual data situation, thereby selecting the optimal index path, connection method and other execution strategies, thereby improving query efficiency and reducing database resource consumption.

[0033] If a comparison operation expression is formed, the function is equivalently rewritten as a conditional judgment expression with the third judgment branch OR the fourth judgment branch. The third judgment branch is the comparison operation between the first input parameter and the external comparison object, combined with the constraint that the first input parameter is not empty. The fourth judgment branch is the comparison operation between the second input parameter and the external comparison object, combined with the constraint that the first input parameter is empty.

[0034] An optional SQL example is shown in equation (3): SELECT * FROM t1 WHERE NVL(id, 2) < 10; Formula (3) The SQL statement shown in Equation (3) contains the null check function NVL(id,2), which makes it impossible to utilize the index during execution and results in a large cardinality estimation error. By using the method of this invention, it is equivalently rewritten as a conditional judgment expression with a third judgment branch OR a fourth judgment branch. The final SQL statement is shown in Equation (4). SELECT * FROM t1 WHERE (id IS NOT NULL AND id < 10) OR (id IS NULLAND 2 < 10); Formula (4) The conditions id IS NOT NULL and id<10 in the SQL statement shown in Equation (4) can be directly recognized by the optimizer, thereby triggering an index scan and avoiding the large amount of data reading overhead caused by a full table scan. At the same time, the optimizer can accurately estimate the number of rows in the result set based on the modified expression, making the cost estimate more in line with the actual data situation, thereby selecting the optimal index path, connection method and other execution strategies, thereby improving query efficiency and reducing database resource consumption.

[0035] In some optional embodiments, before determining whether the target null-checking function constitutes a comparison expression with the external comparison object, the method may generally include: determining whether the first input parameter has been determined to not contain a null value; if the first input parameter has been determined to not contain a null value, directly using the first input parameter as the rewritten result of the target null-checking function; if the first input parameter has not been determined to not contain a null value, performing the step of determining whether the target null-checking function constitutes a comparison expression with the external comparison object. Some optional examples of determining that no null value exists may include: 1. The outer condition contains a strict operator for filtering, such as e1 op e2, where the op operator is a strict operator (e.g., pgsql can query the system table pg_proc.proisstrict). An optional SQL example is shown in equation (5): SELECT * FROM t1 WHERE NVL (id, age) AND id > 1 Formula (5) In the SQL statement shown in equation (5), the conditional clause has been limited to id>1 by the strict operator, so the first input parameter id is confirmed to not have a null value. Therefore, it is optimized to obtain the SQL statement shown in equation (6): SELECT * FROM t1 WHERE id AND id > 1 Formula (6) In addition, iterative optimization can be performed based on the characteristics of other expressions in the SQL statement, such as predicate expansion and predicate passing. In this embodiment, the SQL statement shown in equation (6) is iteratively optimized to eliminate redundant conditions. The result of the iterative optimization is shown in equation (7): SELECT * FROM t1 WHERE id > 1 Formula (7) The optimized SQL statement's conditions can be directly recognized by the optimizer, triggering an index scan and avoiding the large data reading overhead of a full table scan. At the same time, the optimizer can accurately estimate the number of rows in the result set based on the modified expression, making the cost estimate more in line with the actual data situation. This allows the optimizer to select the optimal index path, join method, and other execution strategies, thereby improving query efficiency and reducing database resource consumption.

[0036] 2. The outer layer contains a filtering condition with the first input parameter IS NOT NULL. An optional SQL example is shown in equation (8): SELECT * FROM t1 WHERE id IS NOT NULL AND NVL(id, 100) Formula (8) In the SQL statement shown in Equation (8), the outer condition has determined that the first input parameter does not have a null value by using id IS NOT NULL. By using the method of this invention, the first input parameter is directly used as the rewritten result of the target null function, and the final SQL statement is shown in Equation (9): SELECT * FROM t1 WHERE id IS NOT NULL AND id Formula (9) The SQL statement shown in Equation (9) eliminates redundant null function calls, simplifies the logical structure of the statement, and the optimizer no longer needs to estimate the cost of the function and generate the execution plan. It can directly trigger index scanning based on the condition id<10, further reducing the overhead of function parsing and execution. At the same time, it makes the execution plan more concise and efficient, improves the overall execution speed of the query, and reduces the resource consumption of CPU and memory.

[0037] In addition, the SQL statement shown in equation (9) can be further iteratively optimized to eliminate redundant conditions. The result of the iterative optimization is shown in equation (10): SELECT * FROM t1 WHERE id IS NOT NULL Formula (10) The optimized SQL statement's conditions can be directly recognized by the optimizer, triggering an index scan and avoiding the large data reading overhead of a full table scan. At the same time, the optimizer can accurately estimate the number of rows in the result set based on the modified expression, making the cost estimate more in line with the actual data situation. This allows the optimizer to select the optimal index path, join method, and other execution strategies, thereby improving query efficiency and reducing database resource consumption.

[0038] 3. The outer layer contains a strict function, such as func(e1), where func is a strict operator (e.g., pgsql can query the system table pg_proc.proisstrict); An optional SQL example is shown in equation (11): SELECT * FROM t1 WHERE func(id) AND NVL(id, 100) Formula (11) In the SQL statement shown in Equation (11), the outer condition has determined that the first input parameter does not have a null value through func(id). By using the method of this invention, the first input parameter is directly used as the rewritten result of the target null detection function, and the final SQL statement is shown in Equation (12): SELECT * FROM t1 WHERE id AND func(id) Formula (12) In addition, the SQL statement shown in equation (12) can be further iteratively optimized to eliminate redundant conditions. The result of the iterative optimization is shown in equation (13): SELECT * FROM t1 WHERE func(id) Formula (13) The optimized SQL statement's conditions can be directly recognized by the optimizer, triggering an index scan and avoiding the large data reading overhead of a full table scan. At the same time, the optimizer can accurately estimate the number of rows in the result set based on the modified expression, making the cost estimate more in line with the actual data situation. This allows the optimizer to select the optimal index path, join method, and other execution strategies, thereby improving query efficiency and reducing database resource consumption.

[0039] 4. The query statement is a single table and the column of the first input parameter has a not null constraint. This case is the same as the SQL statement shown in equation (8). If it is determined that the first input parameter has a not null constraint, the first input parameter can be directly used as the rewritten result of the target null function.

[0040] In some optional embodiments, the parameter type may also include three or more sets of input parameters, which are arranged in sequence; its operation logic may also include a second operation logic, which judges the input parameters in sequence and returns the first non-empty input parameter. If all input parameters are empty, the last input parameter is returned.

[0041] When there are multiple sets of input parameters and the operation logic is the second operation logic, the steps to rewrite the target null function into an equivalent conditional expression based on the parameter type and operation logic generally include: rewriting the target null function into a multi-branch sequential conditional expression matching the number of input parameters, according to the order of the input parameters. In the multi-branch sequential conditional expression, each branch corresponds to a non-null check of a set of input parameters. When the first non-null value of an input parameter appears, the corresponding input parameter is output and participates in subsequent operations. The multi-branch sequential conditional expression can generally be a CASE WHEN expression.

[0042] An optional SQL example is shown in equation (14): SELECT * FROM t2 WHERE COALESCE(col1, col2, col3) > 8 Formula (14) The SQL statement shown in Equation (14) contains the three-parameter COALESCE function. If the OR operation is used to combine them, too many judgment conditions will be introduced, which will lead to a performance degradation. By using the method of this invention, it is equivalently rewritten as a condition judgment expression of CASE WHEN multi-branch sequential judgment. The final SQL statement is shown in Equation (15): SELECT * FROM t2 WHERE CASE WHEN col1 IS NOT NULL THEN col1 > 8 WHENcol2 IS NOT NULL THEN col2 > 8 ELSE col3 > 8 END; Formula (15) The SQL statement shown in Equation (15) adopts the CASE WHEN sequential branch structure, which avoids the problem of index path failure caused by OR operation. The optimizer can identify the conditions of col1 IS NOT NULL and col2 IS NOT NULL in turn and try to use the index of the corresponding column for scanning. At the same time, the branch logic is clear, and the optimizer can accurately estimate the cardinality of the result set of each branch, thereby selecting the optimal execution order and index path. In the case of multiple parameters, the query efficiency can still be guaranteed, avoiding the overhead of full table scan under large data volume.

[0043] In some optional embodiments, the parameter type may also include three sets of input parameters, namely, a baseline input parameter, a non-empty branch return parameter, and a null value branch return parameter; the operation logic may also include a third operation logic, which returns a non-empty branch return parameter or a null value branch return parameter based on the null value state of the baseline input parameter.

[0044] When the parameter type has three sets of input parameters and the operation logic is the third operation logic, the steps to rewrite the target null function into an equivalent conditional expression based on the parameter type and operation logic generally include: determining whether the target null function constitutes a comparison operation expression with the external comparison object; if not, rewriting the target null function into a two-branch conditional expression, where the two-branch conditional expression uses whether the reference input parameter is non-null as the judgment condition. When the reference input parameter is non-null, the parameter returned by the non-null branch is used as the conditional expression; when the reference input parameter is null, the parameter returned by the null branch is used as the conditional expression. If the target null function constitutes a comparison operation expression with the external comparison object, rewriting the target null function into a two-branch conditional expression, where the two-branch conditional expression uses whether the reference input parameter is non-null as the judgment condition. When the reference input parameter is non-null, the parameter returned by the non-null branch is used to perform a corresponding comparison operation with the external comparison object; when the reference input parameter is null, the parameter returned by the null branch is used to perform a corresponding comparison operation with the external comparison object.

[0045] An optional SQL example is shown in equation (16): SELECT * FROM t1 WHERE NVL2(id, 1, 3) < 100; Formula (16) The SQL statement shown in Equation (16) contains a three-parameter NVL2 function, which makes it impossible to utilize indexes during execution and results in a large cardinality estimation error. By using the method of this invention, it is equivalently rewritten as a two-branch conditional expression, and the final SQL statement is shown in Equation (17): SELECT * FROM t1 WHERE CASE WHEN id IS NOT NULL THEN 1 < 100 ELSE 3 <100 END; Formula (17) The SQL statement shown in Equation (17) converts the original null check function into a standard Boolean OR branch expression. The optimizer can directly identify the condition id IS NOT NULL and try to use the corresponding index. At the same time, it can accurately estimate the number of rows in the result set based on the branch logic, making the cost estimate more accurate. Thus, it selects the optimal execution strategy, avoids the large amount of data reading caused by full table scan, improves query efficiency, and reduces the IO and CPU resource consumption of the database.

[0046] In some alternative embodiments, the parameter type may also include two sets of comparison input parameters, namely a first comparison parameter and a second comparison parameter; the operation logic may also include a fourth operation logic, which returns a null value when the two sets of comparison input parameters are equal, and returns the first comparison parameter when the two sets of comparison input parameters are not equal. When the parameter type consists of two sets of comparison input parameters and the operation logic is the fourth operation logic, the steps to rewrite the target null function into an equivalent conditional expression based on the parameter type and operation logic include: determining whether the target null function constitutes a comparison operation expression with the external comparison object; if not, rewriting the target null function into a two-branch conditional expression, where the two-branch conditional expression uses the equality of the two sets of comparison input parameters as the judgment condition, returning false when the two sets of comparison input parameters are equal, and returning the first comparison parameter as the conditional expression when the two sets of comparison input parameters are not equal; if yes, rewriting the target null function into a two-branch conditional expression, where the two-branch conditional expression uses the equality of the two sets of comparison input parameters as the judgment condition, returning false when the two sets of comparison input parameters are equal, and using the first comparison parameter to perform a comparison operation with the external comparison object when the two sets of comparison input parameters are not equal.

[0047] An optional SQL example is shown in equation (18): SELECT * FROM t3 WHERE NULLIF(col1, col2) > 10; Formula (18) The SQL statement shown in equation (18) contains a NULLIF function. Null values ​​participating in the calculation will cause the UNKNOWN problem and will not be able to utilize the index. By using the method of this invention, it is equivalently rewritten as a two-branch conditional expression, and the final SQL statement is shown in equation (19): SELECT * FROM t3 WHERE CASE WHEN col1 = col2 THEN false ELSE col1 >10 END; Formula (19) The SQL statement shown in Equation (19) transforms the null value return logic of the original null function into an explicit false return, avoiding the UNKNOWN result caused by null values ​​participating in the comparison operation, ensuring that the filtering result is completely equivalent to the original statement. At the same time, the optimizer can also accurately estimate the cardinality of the result set based on this condition, select the optimal execution strategy, improve query efficiency and reduce resource consumption.

[0048] In some alternative embodiments, after the step of rewriting the target null function into an equivalent conditional expression according to the parameter type and operation logic, the method further includes: determining whether the result of the rewritten conditional expression is always true; and if the result of the rewritten conditional expression is always true, eliminating the rewritten conditional expression from the database operation statement.

[0049] A specific SQL example is shown in equation (20): SELECT * FROM t1 WHERE NVL2(id, 1, 3) < 100; Formula (20) The SQL statement shown in equation (20) is equivalently rewritten to obtain equation (21): SELECT * FROM t1 WHERE CASE WHEN id IS NOT NULL THEN 1 < 100 ELSE 3 <100 END; Formula (21) Since the result of the judgment (21) is always true, the conditional judgment expression is eliminated by the method of the present invention, and the final SQL statement is as shown in the expression (22): SELECT * FROM t1; Equation (22) The SQL statement shown in Equation (22) eliminates redundant tautological condition judgments, simplifies the execution logic of the statement, and the optimizer no longer needs to parse and judge the conditions. It can directly perform a full table scan without filtering conditions, which reduces the CPU overhead of condition judgments. At the same time, it simplifies the process of generating the execution plan, improves the overall execution efficiency of the query, and reduces the resource consumption of the database.

[0050] Ultimately, this invention fundamentally solves the technical problems of large cardinality estimation errors, inability to utilize indexes, and poor query performance caused by null-checking functions such as NVL, IFNULL, ISNULL, COALESCE, NVL2, NULLIF, and LNNVL in existing technologies by parsing and equivalently rewriting the target null-checking functions in database operation statements. This invention can equivalently expand the null value handling logic encapsulated in the original function into conditional expressions that the optimizer can directly recognize. This allows the database optimizer to accurately estimate the number of rows in the result set, correct execution cost deviations, and effectively avoid suboptimal execution plans caused by incorrect estimations. Simultaneously, the rewritten expressions can directly trigger index scans, replacing the original full table scan method, significantly reducing disk I / O and data read overhead.

[0051] For null detection functions with different parameter types and operational logic, this invention provides comprehensive and compatible optimization rules, supporting various scenarios such as two-parameter, multi-parameter, three-parameter, and equivalent comparison. Through further optimizations such as deterministic non-null simplification, elimination of tautology conditions, and predicate expansion simplification, the query execution logic is continuously streamlined, reducing redundant calculations and function parsing overhead.

[0052] In database business scenarios with large data volumes and high concurrency, this invention can significantly improve query execution speed, reduce CPU and memory resource consumption, make the database system run more stably and respond more efficiently, and has strong practicality and wide applicability, which can significantly improve the overall processing capacity and operating performance of the database system.

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

[0054] It should be understood that in some embodiments, the components may be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods may be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0055] This embodiment also provides a computer program product 10, a computer-readable storage medium 20, and a computer device 30. Figure 2 This is a schematic diagram of a computer program product 10 according to an embodiment of the present invention. Figure 3 This is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention. Figure 4 This is a schematic diagram of a computer device 30 according to an embodiment of the present invention. The computer program product 10 includes a computer program 11, which, when executed by the processor 32, implements the steps of the database operation statement optimization method described above. A computer-readable storage medium 20 stores the computer program 11 thereon, which, when executed by the processor 32, implements the steps of the database operation statement optimization method described above. The computer device 30 may include a memory 31, a processor 32, and the computer program 11 stored on the memory 31 and running on the processor 32.

[0056] The computer program 11 used to perform the operations of this invention may be assembly instructions, Instruction Set Architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, integrated circuit configuration data, or source code or object code written in any combination of one or more programming languages ​​and procedural programming languages. The computer program 11 may execute entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network, including a Local Area Network (LAN) or Wide Area Network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider). In some embodiments, to perform aspects of this invention, electronic circuits, including, for example, programmable logic circuits, Field-Programmable Gate Arrays (FPGAs), or Programmable Logic Arrays (PLAs), may execute computer-readable program instructions to personalize the electronic circuits by utilizing state information from computer-readable program instructions.

[0057] For the purposes of this embodiment, computer program product 10 is a related product containing computer program 11. For the purposes of this embodiment, computer-readable storage medium 20 is a tangible device capable of holding and storing computer program 11, and can be any device capable of containing, storing, communicating, propagating, or transmitting program 11 for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of computer-readable storage medium 20 include: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable optical disc read-only memory (CD-ROM), digital versatile disc (DVD), memory stick, floppy disk, mechanical encoding device, and any suitable combination thereof.

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

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

[0060] Computer device 30 may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows external devices that can be connected to the computer device to input and output data. The network adapter / interface provides communication between the computer device and a network, typically represented as a communication network.

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

Claims

1. A method for optimizing database operation statements, comprising: Obtain the database operation statement to be optimized, wherein the database operation statement contains a target null detection function, and the target null detection function is an expression containing null value processing logic; The target null detection function is parsed to determine the parameter types and operational logic of the target null detection function; Based on the parameter type and the operation logic, the target null function is rewritten as an equivalent conditional judgment expression; The target null check function in the database operation statement is replaced by the conditional judgment expression to obtain the optimized database operation statement.

2. The method for optimizing database operation statements according to claim 1, wherein, The parameter type includes dual input parameters, which are a first input parameter and a second input parameter arranged in sequence. The operation logic includes a first operation logic, which returns the first non-empty value according to the order of the input parameters; When the parameter type is the dual-input parameter and the operation logic is the first operation logic, the step of rewriting the target null function into an equivalent conditional expression based on the parameter type and the operation logic includes: Determine whether the target null function forms a comparison operation expression with the external comparison object; If not, the target null function is equivalently rewritten as a conditional judgment expression combining the first judgment branch and the second judgment branch through an OR operation. The first judgment branch includes the first input parameter as a conditional expression and the constraint that the first input parameter is a non-null value. The second judgment branch includes the constraint that the first input parameter is a null value and the second input parameter as a conditional expression. If so, the target null function is equivalently rewritten as a conditional judgment expression combining the third and fourth judgment branches through an OR operation. The third judgment branch includes an expression for performing a comparison operation between the first input parameter and the external comparison object, as well as a constraint that the first input parameter is a non-null value. The fourth judgment branch includes a constraint that the first input parameter is a null value, as well as an expression for performing a comparison operation between the second input parameter and the external comparison object.

3. The method for optimizing database operation statements according to claim 2, wherein, Before the step of determining whether the target null function constitutes a comparison expression with the external comparison object, the following steps are also included: Determine whether the first input parameter has been determined to have no null values; If it has been determined that the first input parameter does not contain any null values, the first input parameter is directly used as the rewritten result of the target null detection function. If the first input parameter does not determine that there is no null value, the step of determining whether the target null function constitutes a comparison operation expression with the external comparison object is executed.

4. The method for optimizing database operation statements according to claim 1, wherein, The parameter type also includes three or more sets of input parameters, which are arranged in a sequential order. The operation logic includes a second operation logic, which judges the input parameters in the order they appear and returns the first non-empty input parameter. If all input parameters are empty, the last input parameter is returned. When the parameter type is the multiple sets of input parameters and the operation logic is the second operation logic, the step of rewriting the target null function into an equivalent conditional judgment expression according to the parameter type and the operation logic includes: rewriting the target null function into a multi-branch sequential judgment conditional judgment expression that matches the number of input parameters according to the order of the input parameters. In the multi-branch sequential judgment conditional judgment expression, each branch corresponds to a non-null judgment of a set of input parameters. When the first non-null value of an input parameter appears, the corresponding input parameter is output to participate in subsequent operations. The conditional expression for the multi-branch sequential judgment includes the case when expression.

5. The method for optimizing database operation statements according to claim 1, wherein, The parameter type also includes three sets of input parameters, namely, baseline input parameters, non-empty branch return parameters, and null value branch return parameters; The operation logic includes a third operation logic, which is to return the non-empty branch return parameter or the empty branch return parameter based on the empty value state of the baseline input parameter; When the parameter type is one of the three sets of input parameters and the operation logic is the third operation logic, the step of rewriting the target null function into an equivalent conditional expression based on the parameter type and the operation logic includes: Determine whether the target null function forms a comparison operation expression with the external comparison object; if not, rewrite the target null function equivalently as a two-branch conditional judgment expression. The two-branch conditional judgment expression uses whether the reference input parameter is non-null as the judgment condition. When the reference input parameter is non-null, the non-null branch returns the parameter as the conditional expression. When the reference input parameter is null, the null branch returns the parameter as the conditional expression. If so, the target null function is equivalently rewritten as a two-branch conditional judgment expression. The two-branch conditional judgment expression uses whether the reference input parameter is non-null as the judgment condition. When the reference input parameter is non-null, the non-null branch returns the parameter and performs a corresponding comparison operation with the external comparison object; when the reference input parameter is null, the null branch returns the parameter and performs a corresponding comparison operation with the external comparison object. The two-branch conditional expression includes the case when expression.

6. The method for optimizing database operation statements according to claim 1, wherein, The parameter type also includes two sets of comparison input parameters, namely the first comparison parameter and the second comparison parameter; The operation logic includes a fourth operation logic, which returns an empty value when the two sets of comparison input parameters are equal, and returns the first comparison parameter when the two sets of comparison input parameters are not equal. When the parameter type is two sets of the comparison input parameters and the operation logic is the fourth operation logic, the step of rewriting the target null function into an equivalent conditional judgment expression according to the parameter type and the operation logic includes: Determine whether the target null function forms a comparison operation expression with the external comparison object; If not, the target empty function is equivalently rewritten as a two-branch conditional judgment expression. The two-branch conditional judgment expression uses whether the two sets of comparison input parameters are equal as the judgment condition. When the two sets of comparison input parameters are equal, it returns false. When the two sets of comparison input parameters are not equal, it returns the first comparison parameter as the conditional expression. If so, the target empty function is equivalently rewritten as a two-branch conditional judgment expression. The two-branch conditional judgment expression uses whether the two sets of comparison input parameters are equal as the judgment condition. When the two sets of comparison input parameters are equal, it returns false. When the two sets of comparison input parameters are not equal, it uses the first comparison parameter to perform a comparison operation with the external comparison object.

7. The method for optimizing database operation statements according to claim 1, wherein, The step of rewriting the target null function into an equivalent conditional expression based on the parameter type and the operation logic further includes: Determine whether the result of the rewritten conditional expression is always true; If the result of the rewritten conditional expression is always true, the rewritten conditional expression in the database operation statement is eliminated.

8. A computer-readable storage medium having a computer program stored thereon, the computer program being executed by a processor to implement the steps of the method for optimizing database operation statements according to any one of claims 1 to 7.

9. A computer program product comprising a computer program that, when executed by a processor, implements the steps of the method for optimizing database operation statements according to any one of claims 1 to 7.

10. A computer device comprising a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor, when executing the machine-executable program, implements the steps of the method for optimizing database operation statements according to any one of claims 1 to 7.

Images