A control method, apparatus, device, and storage medium of one implementation

Abstract

This disclosure provides a method, apparatus, device, and storage medium for controlling execution modes. The method includes: determining the execution events and corresponding execution modes for each subquery statement in a first SQL statement; determining whether the first subquery statement contains a preset hint statement; if so, determining the target execution event and corresponding target execution mode of the first subquery statement based on the hint statement; adjusting the cost estimate of the execution mode corresponding to the target execution event to obtain the adjusted cost estimate for each execution mode, and determining it as input to the optimizer; and triggering the execution of the target execution event based on the target execution mode. This disclosure enables finer-grained control of the execution mode corresponding to a single execution event by adjusting the cost estimates of each execution mode after determining the target execution mode based on the hint statement, thereby improving control flexibility.

Description

Technical Field

[0001] This disclosure relates to the field of database technology, and in particular to a control method, apparatus, device and storage medium for an execution mode. Background Technology

[0002] With the continuous development of database technology, the application of database systems is becoming increasingly widespread, and the importance of database system performance is becoming more and more prominent. Among them, the optimizer largely determines the performance of a database system. For example, CBO (Cost-Based Optimizer) is a cost-based optimizer that can rely on statistical information and cost models to calculate a corresponding cost estimate for each execution method, and select the execution method with the minimum cost estimate as the optimal execution method.

[0003] Currently, due to the diversity of customer query scenarios, manual intervention in the selection of execution methods is sometimes necessary in related technologies. When manual intervention is required, the common solution is for the database system to provide global parameters for global control. For example, setting a global parameter can prevent the CBO optimizer from selecting a certain execution method. However, these technologies can only provide global control and cannot control the execution method corresponding to individual execution events with finer granularity, resulting in insufficient control flexibility.

[0004] Therefore, how to control the execution mode of a single execution event with finer granularity, thereby improving the flexibility of control, is a technical problem that urgently needs to be solved. Summary of the Invention

[0005] To address the aforementioned technical problems, this disclosure provides a method for controlling the execution mode, which enables finer-grained control over the execution mode corresponding to a single execution event, thereby improving the flexibility of control.

[0006] Firstly, this disclosure provides a method for controlling the execution mode, the method comprising:

[0007] Determine the execution events corresponding to each subquery statement in the first structured query language (SQL) statement, as well as the execution methods corresponding to each execution event;

[0008] Determine whether the first subquery statement contains a preset hint statement; wherein the hint statement is used to specify the target execution event and the target execution method corresponding to the target execution event;

[0009] If it is determined that the first subquery statement contains the hint statement, then the target execution event of the first subquery statement and the target execution method corresponding to the target execution event are determined based on the hint statement;

[0010] The cost estimate of the execution method corresponding to the target execution event is adjusted to obtain the adjusted cost estimate for each execution method; wherein, the adjusted cost estimate corresponding to the target execution method is the minimum value among the adjusted cost estimates of the execution methods corresponding to the target execution event.

[0011] The adjusted cost estimates corresponding to each execution mode are determined as the input to the optimizer; wherein, the optimizer is used to trigger the execution of the target execution event based on the target execution mode.

[0012] In one optional implementation, the hint statement carries a data table identifier and an execution method identifier. The data table identifier is used to identify the event executed against the data table corresponding to the data table, and the execution method identifier is used to specify the target execution method corresponding to the event.

[0013] The step of determining the target execution event of the first subquery statement and the target execution method corresponding to the target execution event based on the hint statement includes:

[0014] The target execution event of the first subquery statement is determined based on the data table identifier carried by the hint statement, and the target execution method corresponding to the target execution event is determined based on the execution method identifier.

[0015] In one optional implementation, the execution mode identifier includes an execution mode keyword and the number of parallel processes;

[0016] Determining the target execution method corresponding to the target execution event based on the execution method identifier includes:

[0017] Based on the execution method keyword and the number of parallel processes, the target execution method corresponding to the target execution event is determined.

[0018] In one optional implementation, the target execution event includes a parallel scan event performed on the data table corresponding to the data table identifier.

[0019] In one optional implementation, the execution method identifier includes the order relationship between the execution method keyword and the identifiers of each data table;

[0020] Determining the target execution method corresponding to the target execution event based on the execution method identifier includes:

[0021] Based on the sequential relationship between the execution method keywords and the identifiers of each data table, the target execution method corresponding to the target execution event is determined.

[0022] In one optional implementation, the target execution event includes a join order determination event for the data table corresponding to the data table identifier.

[0023] In one optional implementation, adjusting the cost estimate of the execution method corresponding to the target execution event to obtain the adjusted cost estimate for each execution method includes:

[0024] The cost estimate of the target execution method corresponding to the target execution event is adjusted to 0, and used as the adjusted cost estimate of the target execution method;

[0025] or,

[0026] The cost estimates of all execution methods other than the target execution method in the execution methods corresponding to the target execution event are increased by a preset value to obtain the adjusted cost estimates corresponding to each execution method; wherein, the preset value is larger than the order of magnitude of the cost estimate of any execution method.

[0027] Secondly, this disclosure provides a control device for an execution mode, the device comprising:

[0028] The first determining module is used to determine the execution events corresponding to each subquery statement in the first structured query language (SQL) statement and the execution methods corresponding to each execution event.

[0029] The second determining module is used to determine whether the first sub-query statement contains a preset hint statement; wherein the hint statement is used to specify the target execution event and the target execution method corresponding to the target execution event;

[0030] The third determining module is used to determine the target execution event of the first subquery statement and the target execution method corresponding to the target execution event based on the hint statement if it is determined that the first subquery statement contains the hint statement.

[0031] An adjustment module is used to adjust the cost estimate of the execution method corresponding to the target execution event, so as to obtain the adjusted cost estimate for each execution method; wherein the adjusted cost estimate corresponding to the target execution method is the minimum value among the adjusted cost estimates of the execution methods corresponding to the target execution event.

[0032] An execution module is used to determine the adjusted cost estimates corresponding to each execution mode as input to the optimizer; wherein, the optimizer is used to trigger the execution of the target execution event based on the target execution mode.

[0033] Thirdly, this disclosure provides a computer-readable storage medium storing instructions that, when executed on a terminal device, cause the terminal device to implement the above-described method.

[0034] Fourthly, this disclosure provides an apparatus comprising: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the computer program, implements the method described above.

[0035] The technical solution provided in this disclosure has at least the following advantages compared with the prior art:

[0036] This disclosure provides a method for controlling execution mode. First, it determines the execution events and execution modes corresponding to each subquery statement in a first SQL statement. Then, it determines whether the first subquery statement contains a preset hint statement. If the first subquery statement contains a hint statement, it determines the target execution event and the target execution mode corresponding to the target execution event based on the hint statement. Next, it adjusts the cost estimate of the execution mode corresponding to the target execution event to obtain the adjusted cost estimate for each execution mode. These adjusted cost estimates are then used as input to the optimizer, triggering the execution of the target execution event based on the target execution mode. Therefore, this disclosure can achieve finer-grained control over the execution mode corresponding to a single execution event by adjusting the cost estimates of each execution mode after determining the target execution mode based on the hint statement, thereby improving control flexibility. Attached Figure Description

[0037] The accompanying drawings, which are incorporated in and form a part of this specification, illustrate embodiments consistent with this disclosure and, together with the description, serve to explain the principles of this disclosure.

[0038] To more clearly illustrate the technical solutions in the embodiments of this disclosure or the prior art, the accompanying drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0039] Figure 1 A flowchart of a control method for an execution mode provided in an embodiment of this disclosure;

[0040] Figure 2 A flowchart of a control method for another execution mode provided in this disclosure embodiment;

[0041] Figure 3 This is a schematic diagram of the structure of a control device for an execution method provided in an embodiment of the present disclosure;

[0042] Figure 4 This is a schematic diagram of the structure of a control device for an execution method provided in an embodiment of the present disclosure. Detailed Implementation

[0043] To better understand the above-mentioned objectives, features, and advantages of this disclosure, the solutions disclosed herein will be further described below. It should be noted that, unless otherwise specified, the embodiments and features described herein can be combined with each other.

[0044] Numerous specific details are set forth in the following description in order to provide a full understanding of this disclosure, but this disclosure may also be implemented in other ways different from those described herein; obviously, the embodiments in the specification are only some, and not all, of the embodiments of this disclosure.

[0045] With the continuous development of database technology, the application of database systems is becoming increasingly widespread, and the importance of database system performance is becoming more and more prominent. Among them, the optimizer largely determines the performance of a database system. For example, CBO (Cost-Based Optimizer) is a cost-based optimizer that can rely on statistical information and cost models to calculate a corresponding cost estimate for each execution method, and select the execution method with the minimum cost estimate as the optimal execution method.

[0046] Currently, due to the diversity of customer query scenarios, manual intervention in the selection of execution methods is sometimes necessary in related technologies. When manual intervention is required, the common solution is for the database system to provide global parameters for global control. For example, setting a global parameter can prevent the CBO optimizer from selecting a certain execution method. However, these technologies can only provide global control and cannot control the execution method corresponding to individual execution events with finer granularity, resulting in insufficient control flexibility.

[0047] Therefore, how to control the execution mode of a single execution event with finer granularity, thereby improving the flexibility of control, is a technical problem that urgently needs to be solved.

[0048] To address this, this disclosure provides a method for controlling execution mode. First, it determines the execution events and execution modes corresponding to each subquery statement in a first SQL statement. Then, it determines whether the first subquery statement contains a preset hint statement. If the first subquery statement contains a hint statement, it determines the target execution event and the target execution mode corresponding to the target execution event based on the hint statement. Next, it adjusts the cost estimate of the execution mode corresponding to the target execution event to obtain the adjusted cost estimate for each execution mode. These adjusted cost estimates are then used as input to the optimizer, triggering the execution of the target execution event based on the target execution mode. Therefore, this disclosure allows for finer-grained control of the execution mode corresponding to a single execution event by adjusting the cost estimates of each execution mode after determining the target execution mode based on the hint statement, thereby improving control flexibility.

[0049] Based on this, the present disclosure provides a method for controlling the execution mode, referring to... Figure 1 The following is a flowchart of a control method for an execution mode provided in an embodiment of this disclosure. The method includes:

[0050] S101: Determine the execution events corresponding to each subquery statement in the first structured query language (SQL) statement, as well as the execution methods corresponding to each execution event.

[0051] The execution control method provided in this disclosure can be applied to database systems, such as SQL (Structured Query Language) databases and Oracle databases.

[0052] In practical applications, a database mainly consists of three parts: a parser, an optimizer, and an executor. First, the parser parses the input SQL statement. Then, the optimizer transforms the parsed result according to optimization rules, determines the execution events, and generates the corresponding execution methods for each event. Finally, the executor executes each execution event. The optimizer includes a cost-based cost-benefit (CBO) optimizer, which relies on statistical information and a cost model to calculate a cost estimate for each execution method and selects the execution method with the lowest cost estimate as the final execution method for that execution event.

[0053] In this embodiment of the disclosure, the first SQL statement may include data query statements (such as SELECT statements), data manipulation statements (such as INSERT statements, UPDATE statements, DELETE statements), etc. An SQL statement may include one or more subquery statements, a subquery statement may correspond to one or more execution events, and an execution event may correspond to one or more execution methods.

[0054] In this embodiment of the disclosure, if it is determined that the first SQL statement includes multiple subquery statements, then the sub-execution events corresponding to the first subquery statement and the execution methods corresponding to each sub-execution event are further determined. The first subquery statement may correspond to one or more sub-execution events, and a sub-execution event may correspond to one or more execution methods.

[0055] For example, taking the first SQL statement `select * from a join b` as an example, this first SQL statement contains a subquery statement. This subquery statement corresponds to three execution events: the first execution event is a scan of table a, the second execution event is a scan of table b, and the third execution event is a join between tables a and b. Then, the optimizer transforms the query according to optimization rules, generating corresponding execution methods for each execution event. For example, the execution methods generated for the first execution event may include full table scan (e.g., seqscan), index scan (e.g., indexscan), bitmap scan (e.g., bitmapscan), etc.; the execution methods generated for the second execution event may include full table scan, index scan, bitmap scan, etc.; and the execution methods generated for the third execution event may include nested join (e.g., nestloop), hash join (e.g., hashjoin), sort-merge join (e.g., mergejoin), etc.

[0056] S102: Determine whether the first subquery statement contains a preset hint statement.

[0057] The hint statement is used to specify the target execution event and the target execution method corresponding to the target execution event.

[0058] In this embodiment of the disclosure, a subquery statement may contain one or more preset hint statements. A hint statement is used to specify a target execution event and the corresponding target execution method. Hint statements can exist anywhere within the subquery statement. The format of a hint statement generally begins with three consecutive characters " / *+" and ends with three consecutive characters "* / ", for example, the format of a hint statement is / *+hint1* / . If the format of a hint statement in a subquery statement is incorrect, the hint statement is treated as a normal comment statement and does not affect the execution of the subquery statement.

[0059] In this embodiment of the disclosure, after parsing the first subquery statement by the parser, it can be determined whether the first subquery statement contains a preset hint statement. A correctly formatted hint statement can be used to specify the target execution event corresponding to the first subquery statement and the target execution method corresponding to the target execution event in the execution events and execution methods corresponding to each execution event determined in S101 above.

[0060] In one optional implementation, the hint statement carries a data table identifier and an execution method identifier. The data table identifier is used to identify the event to be executed on the data table corresponding to the data table, and the execution method identifier is used to specify the target execution method corresponding to the event.

[0061] In this embodiment of the disclosure, the data table identifier carried by the hint statement can be the name of a data table, and may include one or more, used to identify the event executed on the data table corresponding to the data table name. The execution method identifier carried by the hint statement may include the execution method keyword, the number of parallel processes, and the order relationship between the various data table identifiers, used to specify the target execution method corresponding to the event. For example, the hint statement included in the first subquery statement may be / *+execution method keyword A(a)* / ; / *+execution method keyword A(a)execution method keyword B(b)* / ; etc.

[0062] S103: If it is determined that the first subquery statement contains a hint statement, then the target execution event of the first subquery statement and the target execution method corresponding to the target execution event are determined based on the hint statement.

[0063] In this embodiment of the disclosure, based on the above S102, if it is determined that the first subquery statement contains a hint statement, then the target execution event and the target execution method corresponding to the target execution event of the first subquery statement are determined based on the hint statement. For example, based on the above example, taking the first SQL statement `select * from a join b` as an example, if it is determined that the first subquery statement contains a hint statement, such as the hint statement being ` / *+execution method keyword A(a) execution method keyword B(ab)* / `, then the target execution event and the target execution method corresponding to the target execution event of the first subquery statement are determined to include the first target execution event: scanning table a according to execution method A, and the second target execution event: joining table a and table b according to execution method B. The second execution event, scanning table b, is not the target execution event of the first subquery statement.

[0064] In one optional implementation, the hint statement carries a data table identifier and an execution method identifier. When the execution method identifier includes an execution method keyword, the target execution event of the first subquery statement is determined based on the data table identifier carried by the hint statement, and the target execution method corresponding to the target execution event is determined based on the execution method identifier (i.e., the execution method keyword).

[0065] In this embodiment of the disclosure, the data table identifier carried by the hint statement can be the name of a data table, and may include one or more tables. Based on the table name, the target execution event of the first subquery statement can be determined. Specifically, if the data table identifier carried by the hint statement includes the name of a single data table, the target execution event is the target event for execution on that data table, such as performing a scan operation on table a, performing a scan operation on table b, etc.; if the data table identifier carried by the hint statement includes the names of multiple data tables, the target execution event is the target event for execution on multiple data tables, such as performing a join operation on tables a and tables b, etc.

[0066] In this embodiment of the disclosure, the execution mode identifier carried by the hint statement can be an execution mode keyword, which can then be used to determine the target execution mode corresponding to the target execution event. For example, the execution mode keyword can be a keyword for scanning mode, a keyword for joining mode, etc., and the target execution mode corresponding to the determined target execution event can be a target scanning mode, a target joining mode, etc.

[0067] For example, taking the first SQL statement `select * from a join b`, if the first subquery statement contains a hint statement (such as ` / *+indexscan(a)* / `), then based on the table identifier (the table name of table a) carried by the hint statement, it is determined that the target execution event of the first subquery statement is to perform a scan operation on table a. Furthermore, based on the execution mode keyword (the keyword `indexscan` for scan mode), it can be determined from multiple scan modes that the target execution mode corresponding to this target execution event is the index scan mode. Thus, it is determined that when executing the first SQL statement, an index scan mode needs to be used to scan table a.

[0068] For example, if the table identifier carried by the hint statement includes the names of multiple tables, such as if the first subquery statement contains a hint statement (e.g., / *+mergejoin(ab)* / ), then based on the table identifiers carried by the hint statement (the table names of table a and table b), the target execution event of the first subquery statement is determined to be a join operation on tables a and b. Furthermore, based on the execution mode keyword (the join mode keyword mergejoin), the target execution mode corresponding to this target execution event can be determined to be the permutation-merge join mode among multiple join modes. Thus, it is determined that when executing the first SQL statement, the permutation-merge join mode should be used to join tables a and b.

[0069] In one optional implementation, when the execution mode identifier includes an execution mode keyword and the number of parallel processes, the target execution mode corresponding to the target execution event is determined based on the execution mode keyword and the number of parallel processes.

[0070] In this embodiment of the disclosure, the execution mode identifier carried by the hint statement can be an execution mode keyword and the number of parallel processes. Therefore, the target execution mode corresponding to the target execution event can be determined based on the execution mode keyword and the number of parallel processes. For example, the execution mode keyword can be a keyword for parallel scanning (such as represented by the English word "parallel"). Based on the parallel scanning mode keyword and the number of parallel processes S, the target execution mode corresponding to the target execution event can be determined to be starting S processes for parallel scanning, etc.

[0071] In one optional implementation, the target execution event of the first subquery statement determined based on the data table identifier carried by the hint statement includes a parallel scan event performed on the data table corresponding to the data table identifier.

[0072] In this embodiment, based on the table name and execution mode keyword carried in the hint statement, the target execution event of the first subquery statement can be determined to be a parallel scan event targeting that table. It should be noted that the parallel scan mode can coexist with various ordinary scan modes, such as a full table parallel scan of table a, an index parallel scan of table a, etc. After determining a full table scan event targeting table a, if the execution mode keyword carried in the hint statement is determined to be a keyword for the parallel scan mode (such as `parallel`), then it can be determined that a full table parallel scan will be performed on table a.

[0073] For example, taking the first SQL statement `select * from a join b` as an example, if the first subquery statement contains a hint statement (such as ` / *+parallel(a 10)* / `), then based on the table identifier (the table name of table a) carried by the hint statement and the execution mode keyword (the keyword `parallel` for parallel scan mode), the target execution event of the first subquery statement is determined to be a scan operation on table a. Among various scan modes, after determining that a full table scan is used for table a, based on the execution mode keyword and the number of parallel processes, the target execution mode corresponding to the target execution event is determined to be starting 10 processes for parallel scan. Thus, it is determined that when executing the first SQL statement, 10 processes need to be started simultaneously to perform a full table parallel scan on table a.

[0074] In one optional implementation, when the execution method identifier includes the order relationship between the execution method keyword and each data table identifier, the target execution method corresponding to the target execution event is determined based on the order relationship between the execution method keyword and each data table identifier.

[0075] In this embodiment of the disclosure, the execution method identifier carried by the hint statement can be the order relationship between the execution method keyword and the identifiers of each data table. Therefore, the target execution method corresponding to the target execution event can be determined based on the order relationship between the execution method keyword and the identifiers of each data table. For example, the execution method keyword can be a keyword that determines the join order (such as represented by the English word "leading"). Based on the order relationship between the keyword determining the join order and the identifiers of each data table, the target execution method corresponding to the target execution event can be determined to be a multi-table join according to the determined order relationship between the data tables.

[0076] In one optional implementation, the target execution event of the first subquery statement determined based on the data table identifier carried by the hint statement includes the event for determining the join order of the data table execution corresponding to the data table identifier.

[0077] In this embodiment of the disclosure, based on the table name and execution mode keyword carried by the hint statement, the target execution event of the first subquery statement can be determined as a join order determination event for multiple data tables. The sequential relationship between the various data table identifiers included in the execution mode identifier can be indicated by punctuation.

[0078] In one optional implementation, assuming three data tables a, b, and c, when joining any two tables, the joining order can include joining table a with table b (ab), joining table b with table a (ba), joining table a with table c (ac), joining table c with table a (ca), joining table b with table c (bc), and joining table c with table b (cb). Here, the first table joined is the outer table, and the second is the inner table. If the execution mode identifier carried by the hint statement includes the execution mode keyword and the order of the table identifiers as leading (abc), it indicates that the join operation is performed in the order of joining table a with table b first, and then joining the result with table c. However, it does not restrict the inner and outer tables when joining any two tables; that is, when table a and table b are joined first, table a can be the outer table (ab), or table b can be the outer table (ba).

[0079] In this embodiment of the disclosure, taking the first SQL statement `select * from a join b join c` as an example, if the first subquery statement contains a hint statement (such as ` / *+leading(abc)* / `), then based on the table identifiers carried in the hint statement (the table names of table a, table b, and table c) and the execution method keyword (the keyword `leading` that determines the join order), the target execution event of the first subquery statement is determined to be a multi-table join according to the determined order relationship between the various tables. Based on the keyword that determines the join order and the order relationship between the various table identifiers, the target join order corresponding to the target execution event is determined to be that table a and table b are joined first, but it is not restricted whether table a is joined with table b or table b is joined with table a. Then, the obtained join result is joined with table c. Similarly, it is not restricted whether the join result is joined with table c or table c is joined with the join result.

[0080] In one optional implementation, if the execution mode identifier carried by the hint statement includes the execution mode keyword and the order of each data table identifier as leading(((ab)c)), it means that the join operation is performed in the following order: first join table a with table b, with table a as the outer table, and then join table c with the join result, with table c as the inner table.

[0081] In this embodiment of the disclosure, taking the first SQL statement select*from a join b join c as an example, if the first subquery statement contains a hint statement (such as / *+leading(((ab)c))* / ), based on the keyword for determining the join order and the order relationship between the identifiers of each data table, the target join order corresponding to the target execution event is determined as follows: table a is first joined with table b as the outer table to obtain the join result, and then table c is joined with the join result as the inner table.

[0082] S104: Adjust the cost estimate of the execution method corresponding to the target execution event to obtain the adjusted cost estimate for each execution method.

[0083] Among them, the adjusted cost estimate corresponding to the target execution method is the minimum value among the adjusted cost estimates of the execution methods corresponding to the target execution event.

[0084] In this embodiment of the disclosure, for all execution methods corresponding to the target execution event, the CBO optimizer can calculate a corresponding cost estimate for each execution method. After determining the target execution event of the first sub-query statement and the target execution method corresponding to the target execution event based on the above S103, the cost estimates of all execution methods corresponding to the target execution event are adjusted to obtain the adjusted cost estimates corresponding to each execution method, and the adjusted cost estimate corresponding to the target execution method is the minimum among the adjusted cost estimates of all execution methods corresponding to the target execution event. The specific implementation methods for adjusting the cost estimates of the execution methods to make the adjusted cost estimate of the target execution method the minimum among the adjusted cost estimates of all execution methods corresponding to the target execution event include various methods, such as adjusting the cost estimate of the target execution method corresponding to the target execution event to the minimum, or adjusting the cost estimates of other execution methods besides the target execution method to the maximum, etc.

[0085] In one optional implementation, the cost estimate of the target execution method corresponding to the target execution event is adjusted to 0, and used as the adjusted cost estimate of the target execution method.

[0086] In this embodiment, after determining the target execution event of the first sub-query statement and the target execution method corresponding to the target execution event based on S103 above, the cost estimate corresponding to the target execution method is adjusted to 0, while the cost estimates of other execution methods corresponding to the target execution event remain unchanged. This ensures that among the adjusted cost estimates for each execution method, the adjusted cost estimate corresponding to the target execution method is the minimum. It should be noted that the cost estimates calculated by the CBO optimizer for each execution method are all values ​​greater than 0.

[0087] For example, based on the above example, suppose the execution method generated for the target execution event (such as scanning table a) can include full table scan, index scan, and bitmap scan. The CBO optimizer calculates a corresponding cost estimate for each; for example, the cost estimate for a full table scan is 10, the cost estimate for an index scan is 12, and the cost estimate for a bitmap scan is 14. If it is determined that the target execution method for the target execution event of scanning table a is an index scan, then the cost estimate for the index scan can be adjusted from 12 to 0, while the cost estimates for the full table scan and bitmap scan remain unchanged, making the adjusted cost estimate for the index scan the minimum value.

[0088] In another optional implementation, the cost estimates of all execution methods other than the target execution method in the execution methods corresponding to the target execution event are increased by a preset value to obtain the adjusted cost estimates corresponding to each execution method.

[0089] The magnitude of the preset value is greater than the magnitude of the cost estimate for any execution method.

[0090] In this embodiment of the disclosure, after determining the target execution event of the first sub-query statement and the target execution method corresponding to the target execution event based on the above S103, the cost estimates of all execution methods other than the target execution method can be increased by a preset value. The magnitude of the preset value increase needs to be greater than the magnitude of the cost estimate of any execution method, so as to ensure that the adjusted cost estimate corresponding to the target execution method is the minimum among the adjusted cost estimates corresponding to each execution method.

[0091] It should be noted that the aforementioned increased preset value can be one order of magnitude larger than the cost estimate of any execution method, or it can be several orders of magnitude larger. In this embodiment of the disclosure, there is no limitation on how many orders of magnitude the aforementioned preset value is larger than the cost estimate of any execution method.

[0092] For example, based on the above example, suppose the execution methods generated for the target execution event (such as joining tables a and b) can include nested join, hash join, and sort-merge join. The CBO optimizer calculates a corresponding cost estimate for each, for example, the cost estimate for nested join is 10, the cost estimate for hash join is 12, and the cost estimate for sort-merge join is 14. If the target execution method for the target execution event of joining tables a and b is determined to be a sort-merge join, then the cost estimate of 14 for sort-merge join can be kept unchanged. Furthermore, the cost estimates for other execution methods can be increased by a preset value (such as 100000000000). This would increase the cost estimate of nested join (10) by 100000000000 and the cost estimate of hash join (12) by 100000000000, making the cost estimate for sort-merge join the minimum.

[0093] S105: Determine the adjusted cost estimates corresponding to each execution mode as the input to the optimizer.

[0094] The optimizer is used to trigger target execution events based on the target execution method.

[0095] In this embodiment, based on S104 above, adjusted cost estimates corresponding to each execution method are obtained. The adjusted cost estimate corresponding to the target execution method is the minimum among the adjusted cost estimates of the execution methods corresponding to the target execution event. Using the adjusted cost estimates corresponding to each execution method as input to the optimizer, and based on the characteristic that the CBO optimizer selects the execution method with the minimum cost estimate as the final execution method for the execution event, the target execution method can be selected by the CBO optimizer as the final execution method for the target execution event. Furthermore, the executor executes the target execution event based on the target execution method and outputs the execution result.

[0096] In the execution mode control method provided in this disclosure, firstly, the execution events corresponding to each subquery statement in the first SQL statement and the execution modes corresponding to each execution event are determined. Then, it is determined whether the first subquery statement contains a preset hint statement. If it is determined that the first subquery statement contains a hint statement, the target execution event of the first subquery statement and the target execution mode corresponding to the target execution event are determined based on the hint statement. Furthermore, the cost estimate of the execution mode corresponding to the target execution event is adjusted to obtain the adjusted cost estimate corresponding to each execution mode. The adjusted cost estimates are then used as inputs to the optimizer to trigger the execution of the target execution event based on the target execution mode. Therefore, this disclosure embodiment can, after determining the target execution mode based on the hint statement, adjust the cost estimates of each execution mode to achieve more granular control over the execution mode corresponding to a single execution event, thereby improving control flexibility.

[0097] Based on the above embodiments, this disclosure also provides a method for controlling the execution mode, such as... Figure 2 The diagram shows a flowchart of a control method for another execution mode provided in this embodiment, which includes the following steps:

[0098] S201: Determine the execution events corresponding to each subquery statement in the first SQL statement and the execution methods corresponding to each execution event.

[0099] In this embodiment of the disclosure, after parsing the first SQL statement, the subquery statements contained in the first SQL statement can be parsed out, and the execution events corresponding to the first subquery statements and the execution methods corresponding to each execution event can be determined. For example, if the first subquery statement is `select / *+indexscan(a)mergejoin(ab)* / from a join b`, the execution events corresponding to the first subquery statement can be determined to include: first execution event: scanning table a; second execution event: scanning table b; and third execution event: joining tables a and b. Furthermore, the execution methods corresponding to the first execution event can be determined to include: full table scan (seqscan), index scan (indexscan), and bitmap scan (bitmapscan); the execution methods corresponding to the second execution event can include: full table scan, index scan, and bitmap scan; and the execution methods corresponding to the third execution event can include: nested loop join (nestloop), hash join (hashjoin), and sorted merge join (mergejoin).

[0100] S202: Calculate the cost estimates for each execution method corresponding to the execution event of the first subquery statement.

[0101] In this embodiment of the disclosure, based on the CBO optimizer, a corresponding cost estimate can be calculated for each execution mode. Based on the above example, after calculation, it is assumed that the cost estimate for the full table scan corresponding to the first execution event is 10, the cost estimate for the index scan is 12, and the cost estimate for the bitmap scan is 14; the cost estimate for the full table scan corresponding to the second execution event is 2, the cost estimate for the index scan is 4, and the cost estimate for the bitmap scan is 6; the cost estimate for the nested loop join corresponding to the third execution event is 10, the cost estimate for the hash join is 12, and the cost estimate for the sort-merge join is 14.

[0102] S203: Determine whether the first subquery statement contains a preset hint statement. If yes, execute S204; otherwise, execute S205.

[0103] The hint statement is used to specify the target execution event and the target execution method corresponding to the target execution event.

[0104] In this embodiment of the disclosure, the hint statement carries a data table identifier and an execution method identifier. The data table identifier can be the name of one or more data tables, and the execution method identifier can include execution method keywords, the number of parallel processes, and the order relationship between the various data table identifiers. The execution method keywords included in the execution method identifier can be seqscan, indexscan, hashjoin, mergejoin, etc., and this embodiment of the disclosure does not impose any restrictions on the naming of the execution method keywords.

[0105] In this embodiment of the disclosure, based on the above example, if the first execution event and the third execution event are respectively the target execution events specified by the hint statement contained in the first subquery statement, then S204 is executed; while if the second execution event is not the target execution event, then S205 is executed.

[0106] S204: Determine the target execution event of the first subquery statement and the target execution method corresponding to the target execution event based on the hint statement.

[0107] In this embodiment of the disclosure, based on the above example, the first subquery statement contains a first hint statement of / *+indexscan(a)* / , the determined first target execution event is the first execution event: scanning table a, and the target execution method corresponding to the first target execution event is performing an index scan on table a. The first subquery statement contains a second hint statement of / *+mergejoin(ab)* / , the determined second target execution event is the third execution event: joining table a and table b, and the target execution method corresponding to the second target execution event is performing a sorted merge join on table a and table b.

[0108] S205: For non-target execution events, determine the corresponding execution method based on the optimizer.

[0109] In this embodiment, based on the above-described S203, it is determined that the second execution event, scanning table b, is not the target execution event. The execution methods corresponding to the second execution event include full table scan, index scan, and bitmap scan. Furthermore, based on the above-described S202, the estimated cost for the full table scan corresponding to the second execution event is calculated to be 2, the estimated cost for the index scan is 4, and the estimated cost for the bitmap scan is 6. Then, based on the characteristic of the CBO optimizer selecting the execution method with the lowest estimated cost as the final execution method for that execution event, the final execution method for the second execution event is determined to be a full table scan of table b. The execution result is output, and the process ends.

[0110] S206: Adjust the cost estimate of the execution method corresponding to the target execution event to obtain the adjusted cost estimate for each execution method.

[0111] Among them, the adjusted cost estimate corresponding to the target execution method is the minimum value among the adjusted cost estimates of the execution methods corresponding to the target execution event.

[0112] In this embodiment of the disclosure, based on the cost estimates corresponding to each execution method calculated in S202 above, and the target execution method corresponding to the target execution event determined in S204, the cost estimates of the execution method corresponding to the target execution event are adjusted. For example, for the first target execution event, when performing an index scan on table a, the cost estimate corresponding to the index scan of the first target execution event can be adjusted from 12 to 0, while the cost estimate corresponding to the full table scan remains unchanged at 10, and the cost estimate corresponding to the bitmap scan remains unchanged at 14. This ensures that the adjusted cost estimate corresponding to the index scan is at its minimum.

[0113] In this embodiment of the disclosure, when sorting and merging tables a and b for the second target execution event, the cost estimate corresponding to the sorting and merging connection for the second target execution event can be kept unchanged at 14, while the cost estimate 10 corresponding to the nested loop connection can be increased by 10000000000, and the cost estimate 12 corresponding to the hash connection can be increased by 10000000000, thus making the cost estimate corresponding to the sorting and merging connection the minimum value.

[0114] S207: Determine the adjusted cost estimates corresponding to each execution mode as the input to the optimizer.

[0115] The optimizer is used to trigger target execution events based on the target execution method.

[0116] The specific process of embodiment S207 of this disclosure has been described in detail in embodiment S105 above, and will not be repeated here.

[0117] In the execution mode control method provided in this disclosure, firstly, the execution events corresponding to each subquery statement in the first SQL statement and the execution modes corresponding to each execution event are determined. Then, it is determined whether the first subquery statement contains a preset hint statement. If it is determined that the first subquery statement contains a hint statement, the target execution event of the first subquery statement and the target execution mode corresponding to the target execution event are determined based on the hint statement. Furthermore, the cost estimate of the execution mode corresponding to the target execution event is adjusted to obtain the adjusted cost estimate corresponding to each execution mode. The adjusted cost estimates are then used as inputs to the optimizer to trigger the execution of the target execution event based on the target execution mode. Therefore, this disclosure embodiment can, after determining the target execution mode based on the hint statement, adjust the cost estimates of each execution mode to achieve more granular control over the execution mode corresponding to a single execution event, thereby improving control flexibility.

[0118] Based on the same inventive concept as the above embodiments, this disclosure also provides a control device for an execution mode, referencing... Figure 3 This is a schematic diagram of the structure of a control device for an execution mode provided in an embodiment of the present disclosure. The control device 300 for the execution mode includes:

[0119] The first determining module 301 is used to determine the execution events corresponding to each subquery statement in the first structured query language (SQL) statement and the execution methods corresponding to each execution event.

[0120] The second determining module 302 is used to determine whether the first sub-query statement contains a preset hint statement; wherein, the hint statement is used to specify the target execution event and the target execution method corresponding to the target execution event;

[0121] The third determining module 303 is used to determine the target execution event of the first subquery statement and the target execution method corresponding to the target execution event based on the hint statement if it is determined that the first subquery statement contains the hint statement.

[0122] The adjustment module 304 is used to adjust the cost estimate of the execution method corresponding to the target execution event to obtain the adjusted cost estimate for each execution method; wherein, the adjusted cost estimate corresponding to the target execution method is the minimum value among the adjusted cost estimates of the execution methods corresponding to the target execution event.

[0123] The execution module 305 is used to determine the adjusted cost estimates corresponding to each execution mode as the input of the optimizer; wherein, the optimizer is used to trigger the execution of the target execution event based on the target execution mode.

[0124] In one optional implementation, the hint statement carries a data table identifier and an execution method identifier. The data table identifier is used to identify the event executed against the data table corresponding to the data table, and the execution method identifier is used to specify the target execution method corresponding to the event.

[0125] The third determining module 303 includes:

[0126] The first determining submodule is used to determine the target execution event of the first subquery statement based on the data table identifier carried by the hint statement, and to determine the target execution method corresponding to the target execution event based on the execution method identifier.

[0127] In one optional implementation, the execution mode identifier includes an execution mode keyword and the number of parallel processes;

[0128] The first determining submodule includes:

[0129] The second determining submodule is used to determine the target execution method corresponding to the target execution event based on the execution method keyword and the number of parallel processes.

[0130] In one optional implementation, the target execution event includes a parallel scan event performed on the data table corresponding to the data table identifier.

[0131] In one optional implementation, the execution method identifier includes the order relationship between the execution method keyword and the identifiers of each data table;

[0132] The first determining submodule includes:

[0133] The third determination submodule is used to determine the target execution method corresponding to the target execution event based on the sequential relationship between the execution method keyword and the identifiers of each data table.

[0134] In one optional implementation, the target execution event includes a join order determination event for the data table corresponding to the data table identifier.

[0135] In one optional implementation, the adjustment module 304 includes:

[0136] The first adjustment submodule is used to adjust the cost estimate of the target execution method corresponding to the target execution event to 0, as the adjusted cost estimate of the target execution method;

[0137] or,

[0138] The second adjustment submodule is used to increase the cost estimates of all execution methods other than the target execution method in the execution methods corresponding to the target execution event by a preset value, so as to obtain the adjusted cost estimates corresponding to each execution method respectively; wherein, the preset value is larger than the magnitude of the cost estimate of any execution method.

[0139] In the execution mode control device provided in this embodiment, firstly, the execution events corresponding to each subquery statement in the first SQL statement and the execution modes corresponding to each execution event are determined. Then, it is determined whether the first subquery statement contains a preset hint statement. If it is determined that the first subquery statement contains a hint statement, the target execution event of the first subquery statement and the target execution mode corresponding to the target execution event are determined based on the hint statement. Furthermore, the cost estimate of the execution mode corresponding to the target execution event is adjusted to obtain the adjusted cost estimate corresponding to each execution mode. The adjusted cost estimates are then used as inputs to the optimizer to trigger the execution of the target execution event based on the target execution mode. Therefore, this embodiment can, after determining the target execution mode based on the hint statement, adjust the cost estimates of each execution mode to achieve finer-grained control over the execution mode corresponding to a single execution event, thereby improving control flexibility.

[0140] In addition to the methods and apparatus described above, this disclosure also provides a computer-readable storage medium storing instructions that, when executed on a terminal device, cause the terminal device to implement the control method for the execution mode described in this disclosure.

[0141] In addition, this disclosure also provides a control device 400 for an execution mode, see [link to relevant documentation]. Figure 4 As shown, it may include:

[0142] The device includes a processor 401, a memory 402, an input device 403, and an output device 404. The number of processors 401 in the control device for the execution mode can be one or more. Figure 4 Taking a processor as an example. In some embodiments of this disclosure, the processor 401, memory 402, input device 403, and output device 404 can be connected via a bus or other means, wherein, Figure 4 Taking the example of a connection between China and Israel via a bus.

[0143] The memory 402 can be used to store software programs and modules. The processor 401 executes various functional applications and data processing of the control device by running the software programs and modules stored in the memory 402. The memory 402 may mainly include a program storage area and a data storage area. The program storage area may store the operating system, at least one application program required for a function, etc. In addition, the memory 402 may include high-speed random access memory, and may also include non-volatile memory, such as at least one disk storage device, flash memory device, or other volatile solid-state storage device. The input device 403 can be used to receive input digital or character information, and generate signal inputs related to user settings and function control of the control device.

[0144] Specifically in this embodiment, the processor 401 loads the executable files corresponding to the processes of one or more applications into the memory 402 according to the following instructions, and the processor 401 runs the applications stored in the memory 402, thereby realizing the various functions of the control device of the above execution method.

[0145] It should be noted that, in this document, relational terms such as "first" and "second" are used merely to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0146] The above description is merely a specific embodiment of this disclosure, enabling those skilled in the art to understand or implement it. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of this disclosure. Therefore, this disclosure is not to be limited to the embodiments described herein, but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A method for controlling an execution mode, characterized in that, The method includes: Determine the execution events corresponding to each subquery statement in the first structured query language (SQL) statement, as well as the execution methods corresponding to each execution event; Determine whether the first subquery statement contains a preset hint statement; wherein, the hint statement is used to specify the target execution event and the target execution method corresponding to the target execution event, the hint statement carries a data table identifier and an execution method identifier, the data table identifier is used to identify the event executed against the data table corresponding to the data table identifier, and the execution method identifier is used to specify the target execution method corresponding to the event; If it is determined that the first subquery statement contains the hint statement, then the target execution event of the first subquery statement is determined based on the data table identifier carried by the hint statement, and the target execution method corresponding to the target execution event is determined based on the execution method identifier; The cost estimate of the target execution method corresponding to the target execution event is adjusted to 0, and used as the adjusted cost estimate of the target execution method; Alternatively, the cost estimates of all execution methods other than the target execution method in the execution methods corresponding to the target execution event are increased by a preset value to obtain the adjusted cost estimates corresponding to each execution method; wherein the preset value is greater than the magnitude of the cost estimate of any execution method; wherein the adjusted cost estimate corresponding to the target execution method is the minimum value among the adjusted cost estimates of the execution methods corresponding to the target execution event; The adjusted cost estimates corresponding to each execution method are determined as the input of the optimizer. Based on the characteristic that the optimizer selects the execution method with the smallest cost estimate as the final execution method corresponding to the target execution event, the target execution method is selected by the optimizer as the final execution method corresponding to the target execution event; wherein, the optimizer is used to trigger the execution of the target execution event based on the target execution method.

2. The method according to claim 1, characterized in that, The execution method identifier includes the execution method keyword and the number of parallel processes; Determining the target execution method corresponding to the target execution event based on the execution method identifier includes: Based on the execution method keyword and the number of parallel processes, the target execution method corresponding to the target execution event is determined.

3. The method according to claim 2, characterized in that, The target execution events include parallel scan events performed on the data table corresponding to the data table identifier.

4. The method according to claim 1, characterized in that, The execution method identifier includes the order relationship between the execution method keyword and the identifiers of each data table; Determining the target execution method corresponding to the target execution event based on the execution method identifier includes: Based on the sequential relationship between the execution method keywords and the identifiers of each data table, the target execution method corresponding to the target execution event is determined.

5. The method according to claim 4, characterized in that, The target execution event includes a join order determination event for the data table corresponding to the data table identifier.

6. A control device for an execution mode, characterized in that, The device includes: The first determining module is used to determine the execution events corresponding to each subquery statement in the first structured query language (SQL) statement and the execution methods corresponding to each execution event. The second determining module is used to determine whether the first subquery statement contains a preset hint statement; wherein, the hint statement is used to specify the target execution event and the target execution method corresponding to the target execution event, the hint statement carries a data table identifier and an execution method identifier, the data table identifier is used to identify the event executed against the data table corresponding to the data table identifier, and the execution method identifier is used to specify the target execution method corresponding to the event; The third determining module is used to determine the target execution event of the first subquery statement based on the data table identifier carried by the hint statement if it is determined that the first subquery statement contains the hint statement, and to determine the target execution method corresponding to the target execution event based on the execution method identifier. The first adjustment submodule is used to adjust the cost estimate of the target execution method corresponding to the target execution event to 0, as the adjusted cost estimate of the target execution method; Alternatively, the second adjustment submodule is used to increase the cost estimates of all execution methods other than the target execution method in the execution methods corresponding to the target execution event by a preset value, so as to obtain the adjusted cost estimates corresponding to each execution method respectively; wherein, the preset value is greater than the magnitude of the cost estimate of any execution method; wherein, the adjusted cost estimate corresponding to the target execution method is the minimum value among the adjusted cost estimates of the execution methods corresponding to the target execution event; An execution module is used to determine the adjusted cost estimates corresponding to each execution mode as input to the optimizer. Based on the characteristic that the optimizer selects the execution mode with the minimum cost estimate as the final execution mode corresponding to the target execution event, the optimizer selects the target execution mode as the final execution mode corresponding to the target execution event. The optimizer is used to trigger the execution of the target execution event based on the target execution mode.

7. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores instructions that, when executed on a terminal device, cause the terminal device to perform the method as described in any one of claims 1-5.

8. A device, characterized in that, include: A memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the computer program, implements the method as described in any one of claims 1-5.

Images