A method and system for automatically correcting unhealthy statements in a database

Abstract

This invention relates to the field of distributed database technology, specifically providing a method for automatically correcting unhealthy database statements, comprising the following steps: S1. First, generating the type of hint, including two methods associated with the SQL statement; S2. Second, the storage method of the hint: Query_Hint_Table is used to store query-specific external hints, and Global_Hint_Table is used to store the system and workload levels of the plan and the plan hints; S3. Finally, regarding optimization and execution engine, the hint information is obtained from the tables, and an optimization scheme is better selected based on the statement hints. Compared with existing technologies, this invention can reduce the number of unhealthy statements, help the database execute statements and perform query business more efficiently, reduce the time the database occupies CPU, improve database performance, enhance the overall query efficiency of the database, increase the execution speed of statements, and speed up database queries.

Description

Technical Field

[0001] This invention relates to the field of distributed database technology, specifically providing a method and system for automatically correcting unhealthy statements in a database. Background Technology

[0002] Currently, when executing statements, the database follows an optimization process, calculating based on cost models and estimated row counts to select a relatively optimized plan. However, there is a problem with mechanical, fixed judgments; a single expression cannot adapt to all optimization filtering conditions.

[0003] Therefore, some statements may not be executed in the optimal plan, leading to reduced execution efficiency. We call these statements unhealthy statements, and the purpose of this patent is to address how to handle these unhealthy statements. Summary of the Invention

[0004] This invention addresses the shortcomings of the prior art by providing a highly practical method for automatically correcting unhealthy statements in a database.

[0005] A further technical objective of this invention is to provide a reasonably designed, safe, and applicable automatic correction system for unhealthy statements in a database.

[0006] The technical solution adopted by this invention to solve its technical problem is:

[0007] An automatic correction method for unhealthy statements in a database includes the following steps:

[0008] S1. First, generate the type of hint and associate the hint with the SQL statement in two ways: one is an embedded hint that is commented in the query text, and the other is an external hint generated by ZAP as the serialization format.

[0009] S2. Secondly, the storage method of the hint: Query_Hint_Table is used to save query-specific external hints, and Global_Hint_Table will be used to save the system and workload levels of the plan and the plan hint;

[0010] S3. Finally, regarding optimization and the execution engine, we obtain hint information from the table and choose a better optimization scheme based on the suggestions in the statements.

[0011] Furthermore, in step S1, there are also hints at different granularities, including statement-level hints, workload-level hints, and system-level hints;

[0012] For statement-level hints, there are different types of partial hints in the subplan, namely cardinality hints, join order, join hints, and access method hints.

[0013] Furthermore, a complete statement hint is achieved by combining four parts: cardinality hint, connection order, connection hint, and access method hint. For each type of hint, the representation is defined according to the association method, type, and scope.

[0014] Furthermore, in step S2, the statement information and related hint information are stored in the corresponding hint_tree structure. The hint_tree of the statement can clearly record the relevant hint information involved in the statement.

[0015] Furthermore, in step S3, at the statement level, the plan hints provide statistics or planning suggestions to the database kernel. By applying the plan hints at the statement level, better plans can be obtained for the statements, or the total time to generate good plans can be reduced.

[0016] An automatic database unhealthy statement correction system first generates the type of hint and associates the hint with the SQL statement in two ways: the first is an embedded hint commented in the query text, and the second is an external hint generated by ZAP as the serialization format.

[0017] Secondly, there's the storage method for hints. Query_Hint_Table is used to store query-specific external hints, while Global_Hint_Table will be used to store system and workload levels for plans and plan hints.

[0018] Finally, regarding optimization and the execution engine, we obtain hint information from the table and, based on the suggestions in the statements, can better select optimization solutions.

[0019] Furthermore, the two methods of providing hints associated with SQL statements also include hints with different granularities: statement-level hints, workload-level hints, and system-level hints.

[0020] For statement-level hints, we consider different types of partial hints for subplans, namely cardinality hints, join order, join hints, and access method hints.

[0021] Furthermore, a complete statement hint is achieved by combining four parts: cardinality hint, connection order, connection hint, and access method hint. For each type of hint, the representation is defined according to the association method, type, and scope.

[0022] Furthermore, the Global_Hint_Table will be used to store the system and workload levels of plans and plan hints, storing statement information and related hint information in the corresponding hint_tree structure. The hint_tree of a statement can record the relevant hint information involved in the statement.

[0023] Furthermore, after selecting an optimization scheme, at the statement level, plan hints provide statistical information or planning suggestions to the database kernel. By applying plan hints at the statement level, statements can obtain better plans or reduce the total time to generate good plans.

[0024] Compared with existing technologies, the automatic correction method and system for unhealthy statements in a database according to the present invention have the following outstanding advantages:

[0025] This invention provides an optimization suggestion for statement execution, which can reduce the number of unhealthy statements, help the database execute statements and perform query business more efficiently, reduce the time the database occupies CPU, improve database performance, enhance the overall query efficiency of the database, increase the execution speed of statements, and speed up database queries. Attached Figure Description

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

[0027] Appendix Figure 1 This is a flowchart illustrating a method for automatically correcting unhealthy statements in a database. Detailed Implementation

[0028] To enable those skilled in the art to better understand the present invention, the present invention will be further described in detail below with reference to specific embodiments. Obviously, the described embodiments are merely some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0029] The following is a preferred embodiment:

[0030] like Figure 1 As shown in this embodiment, an automatic correction method for unhealthy database statements includes the following steps:

[0031] S1. First, generate the type of hint, and associate the hint with the SQL statement in two ways: one is an embedded hint that is commented in the query text, and the other is an external hint generated by ZAP as the serialization format.

[0032] Furthermore, we consider hints with different granularities, including statement-level hints, workload-level hints, and system-level hints. For statement-level hints, we consider different types of partial hints for subplans: cardinality hints, join order hints, join hints, and access method hints. Additionally, we implement complete statement hints through combinations of these four partial hints. For each type of hint, we define its representation based on its association method (embedded / external), type (partial / full), and scope (statement / workload / system).

[0033] S2. Secondly, the storage method of the hint: Query_Hint_Table is used to save query-specific external hints, and Global_Hint_Table will be used to save the system and workload levels of the plan and the plan hint;

[0034] We store the statement information and related hint information into the corresponding hint_tree structure. The hint_tree clearly records the relevant hint information involved in the statement.

[0035] S3. Finally, regarding optimization and the execution engine, we obtain hint information from the table and choose a better optimization solution based on the suggestions in the statements.

[0036] Adjusting system optimization strategies, etc. At the statement level, plan hints can provide the database kernel with more accurate statistics or better planning suggestions. By applying plan hints at the statement level, we can obtain better plans for these statements or reduce the total time to generate good plans. Either way, our goal is to improve the overall optimization and execution performance of the target statements.

[0037] Based on the above method, the database unhealthy statement automatic correction system in this embodiment first generates the type of hint and then associates the hint with two methods of SQL statement. The first is an embedded hint that is commented in the query text, and the second is an external hint generated by ZAP as the serialization format.

[0038] Two methods for associating hints with SQL statements, including hints with different granularities: statement-level hints, workload-level hints, and system-level hints;

[0039] For statement-level hints, we consider different types of partial hints for subplans, namely cardinality hints, join order, join hints, and access method hints.

[0040] A complete hint for a statement is achieved by combining four parts: cardinality hint, connection order hint, connection hint, and access method hint. For each type of hint, the representation is defined according to the association method, type, and scope.

[0041] Secondly, regarding the storage method of hints, Query_Hint_Table is used to store query-specific external hints, while Global_Hint_Table will be used to store the system and workload levels of plans and plan hints. Global_Hint_Table will be used to store the system and workload levels of plans and plan hints, storing statement information and related hint information into the corresponding hint_tree structure. The relevant hint information involved in the statement can be recorded through the statement's hint_tree.

[0042] Finally, regarding optimization and the execution engine, hints are retrieved from tables, and based on these hints, better optimization strategies are selected. The system's intelligent optimization strategies are adjusted. At the statement level, plan hints can provide the database kernel with more accurate statistics or better planning suggestions. By applying plan hints at the statement level, we can obtain better plans for these statements or reduce the total time spent generating good plans. Either way, our goal is to improve the overall optimization and execution performance of the target statements.

[0043] During the execution of SQL statements, unhealthy statements are identified through calculation. At this point, we process the unhealthy statements, generating different hints, some related to basic table reads and others to joins, and store them in the catalog.

[0044] Table 1 below is a general classification of hint types, divided into two main categories: tables and indexes. Tables are further divided into join types and regular types, while indexes are divided into three types, as shown in Table 1 below:

[0045]

[0046] The hint information recording table, as shown in Table 2, stores statement information and related hint_tree information. Each statement can have a corresponding hint information stored in the table. During subsequent statement optimization, the hint information in this table can be read to provide helpful suggestions for optimization. Table 2 is as follows:

[0047]

[0048] The specific embodiments described above are merely specific examples of the present invention. The patent protection scope of the present invention includes, but is not limited to, the specific embodiments described above. Any appropriate changes or substitutions made by a person skilled in the art that conform to the claims of the present invention regarding an automatic correction method and system for unhealthy statements in a database should fall within the patent protection scope of the present invention.

[0049] Although embodiments of the invention have been shown and described, it will be understood by those skilled in the art that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the appended claims and their equivalents.

Claims

1. A method for automatically correcting unhealthy statements in a database, characterized in that, It has the following steps: S1. First, generate the type of hint and associate the hint with the SQL statement in two ways: one is an embedded hint that is commented in the query text, and the other is an external hint generated by ZAP as the serialization format. S2. Secondly, the storage method of the hint: Query_Hint_Table is used to save external hints for queries, and Global_Hint_Table will be used to save system and workload levels of plans and plan hints, as well as hints of different granularities, including statement-level hints, workload-level hints and system-level hints. Within statement-level hints, subplans are further divided into different types of partial hints, namely cardinality hints, join order, join hints, and access method hints. S3. Obtain the hint information from the table and select the optimization scheme based on the prompts in the statement.

2. The method for automatically correcting unhealthy statements in a database according to claim 1, characterized in that, A complete hint for a statement is achieved by combining four parts: cardinality hint, connection order hint, connection hint, and access method hint. For each type of hint, the representation is defined according to the association method, type, and scope.

3. The method for automatically correcting unhealthy statements in a database according to claim 2, characterized in that, In step S2, the statement information and related hint information are stored in the corresponding hint_tree structure. The hint_tree of the statement can clearly record the relevant hint information involved in the statement.

4. The method for automatically correcting unhealthy statements in a database according to claim 3, characterized in that, In step S3, at the statement level, the planning hint provides statistics or planning suggestions to the database kernel. By applying the planning hint at the statement level, better plans can be obtained for the statements, or the total time to generate good plans can be reduced.

5. A database unhealthy statement automatic correction system, characterized in that, First, generate the type of hint, and associate the hint with the SQL statement in two ways: one is an embedded hint that is commented in the query text, and the other is an external hint generated by ZAP as the serialization format. Secondly, regarding the storage method of hints, Query_Hint_Table is used to store external query hints, while Global_Hint_Table will be used to store system and workload levels of plans and plan hints, as well as hints of different granularities, including statement-level hints, workload-level hints, and system-level hints. Within statement-level hints, subplans are further divided into different types of partial hints, namely cardinality hints, join order, join hints, and access method hints. Retrieve the hint information from the table, and select an optimization scheme based on the prompts in the statement.

6. The database unhealthy statement automatic correction system according to claim 5, characterized in that, A complete hint for a statement is achieved by combining four parts: cardinality hint, connection order hint, connection hint, and access method hint. For each type of hint, the representation is defined according to the association method, type, and scope.

7. The database unhealthy statement automatic correction system according to claim 6, characterized in that, The SQL statement information and related hint information are stored in the corresponding hint_tree structure. The hint_tree of the statement can record the relevant hint information involved in the statement.

8. The database unhealthy statement automatic correction system according to claim 7, characterized in that, At the statement level, planning hints provide statistics or planning suggestions to the database kernel. By applying planning hints at the statement level, better plans can be generated for statements, or the total time to generate good plans can be reduced.

Images