Method of optimizing complex SQL statements using a region divided preferential SQL rewrite operation
A system, method, and computer-readable medium for optimizing complex SQL statements using a region divided preferential SQL rewrite operation. The region divided preferential SQL rewrite operation makes it possible for a user to inference a machine SQL statement rewrite operation to optimize the SQL statement with more or less focus on different regions of the SQL. This operation combines the strength of using heuristic knowledge and understanding of data to rewrite the region of the SQL statement where the problem originates with the precision and efficiency of a machine SQL statement rewrite operation. More specifically, optimizing a complex SQL statement includes a region division operation during which a SQL statement is divided into multiple regions, a user preference assignment process in which the user defines different attention level for different regions, and a region preferential rewrite operation in which the SQL statement is rewritten with consideration to the attention level defined for each region of the SQL statement.
1. A computer-implementable method, comprising:
performing a region division structured query language (SQL) statement rewrite operation on the SQL statement, the region division SQL statement rewrite operation identifying a plurality of SQL statement regions;
specifying a rewrite focus priority for a particular SQL statement region in the plurality of SQL statement regions, the rewrite focus priority providing an indication of a particular amount of resource to be expended on the particular SQL statement region during an optimization operation on the particular SQL statement region, the rewrite focus priority indicating that the optimization operation is to be performed on the particular SQL statement region before other SQLs statement region in the plurality of SQL statement regions;
performing a region preferential SQL statement optimization operation on the particular SQL statement region based on the rewrite focus priority indicating that the optimization operation is to be performed on the particular SQL statement region before other SQLs statement region in the plurality of SQL statement regions and based upon the indication of the particular amount of resource to be expended on the particular SQL statement region, the SQL statement optimization operation recursively performing a rewrite optimization on the particular SQL statement region according to the specified rewrite focus priority.
2. The method of claim 1 , wherein:
the plurality of SQL statement regions of the SQL statement are defined by query blocks and subquery blocks.
3. The method of claim 1 , wherein:
the plurality of SQL statement regions comprise boundaries defining a smaller portion of the SQL statement, the boundaries being applicable to a certain type of rewrite operation.
4. The method of claim 3 , wherein:
the certain type of rewrite operation comprises a FROM clause with a table list in which a join order is changed.
5. The method of claim 1 , wherein:
after the regions are identified, the method further comprises receiving an identification of a certain region in the SQL statement and instructing the optimization operation to focus on the certain regions to rewrite during the optimization operation.
6. The method of claim 1 , wherein:
the specifying is provided via a user-specified input where the user is able to inference how to rewrite the SQL statement.
7. A system, comprising:
a processor;
a data bus coupled to the processor; and
a non-transitory, computer-readable storage medium embodying computer program code, the non-transitory, computer-readable storage medium being coupled to the data bus, the computer program code interacting with a plurality of computer operations and comprising instructions executable by the processor to cause the system to perform operations comprising:
perform a region division structured query language (SQL) statement rewrite operation on the SQL statement, the region division SQL statement rewrite operation identifying a plurality of SQL statement regions;
specify a rewrite focus priority for a particular SQL statement region in the plurality of SQL statement regions, the rewrite focus priority providing an indication of the particular amount of resource to be expended on a particular SQL statement region during an optimization operation on the particular SQL statement region, the rewrite focus priority indicating that the optimization operation is to be performed on the particular SQL statement region before other SQLs statement region in the plurality of SQL statement regions;
perform a region preferential SQL statement optimization operation on the particular SQL statement region based on the rewrite focus priority indicating that the optimization operation is to be performed on the particular SQL statement region before other SQLs statement region in the plurality of SQL statement regions and based upon the indication of the particular amount of resource to be expended on the particular SQL statement region, the SQL statement optimization operation recursively performing a rewrite optimization on the particular SQL statement region according to the specified rewrite focus priority.
8. The system of claim 7 , wherein:
the plurality of SQL statement regions of the SQL statement are defined by query blocks and subquery blocks.
9. The system of claim 7 , wherein:
the plurality of SQL statement regions comprise boundaries defining a smaller portion of the SQL statement, the boundaries being applicable to a certain type of rewrite operation.
10. The system of claim 7 , wherein:
the certain type of rewrite operation comprises a FROM clause with a table list in which a join order is changed.
11. The system of claim 7 , wherein:
after the regions are identified, the operations further comprise receiving an identification of a certain region in the SQL statement and instructing the optimization operation to focus on the certain regions to rewrite during the optimization operation.
12. The system of claim 7 , wherein:
the specifying is provided via a user-specified input where the user is able to inference how to rewrite the SQL statement.
13. A non-transitory, computer-readable storage medium embodying computer program code, the computer program code comprising computer executable instructions that, when executed by a processor, cause a system to perform operations comprising:
performing a region division structured query language (SQL) statement rewrite operation on the SQL statement, the region division SQL statement rewrite operation identifying a plurality of SQL statement regions;
specifying a rewrite focus priority for a particular SQL statement region in the plurality of SQL statement regions, the rewrite focus priority providing an indication of a particular amount of resource to be expended on the particular SQL statement region during an optimization operation on the particular SQL statement region, the rewrite focus priority indicating that the optimization operation is to be performed on the particular SQL statement region before other SQLs statement region in the plurality of SQL statement regions;
performing a region preferential SQL statement optimization operation on the particular SQL statement region based on the rewrite focus priority indicating that the optimization operation is to be performed on the particular SQL statement region before other SQLs statement region in the plurality of SQL statement regions and based upon the indication of the particular amount of resource to be expended on the particular SQL statement region, the SQL statement optimization operation recursively performing a rewrite optimization on the particular SQL statement region according to the specified rewrite focus priority.
14. The non-transitory, computer-readable storage medium of claim 13 , wherein:
the plurality of SQL statement regions of the SQL statement are defined by query blocks and subquery blocks.
15. The non-transitory, computer-readable storage medium of claim 13 , wherein:
the plurality of SQL statement regions comprise boundaries defining a smaller portion of the SQL statement, the boundaries being applicable to a certain type of rewrite operation.
16. The non-transitory, computer-readable storage medium of claim 13 , wherein:
the certain type of rewrite operation comprises a FROM clause with a table list in which a join order is changed.
17. The non-transitory, computer-readable storage medium of claim 13 , wherein:
after the regions are identified, the operations further comprise receiving an identification of a certain region in the SQL statement and instructing the optimization operation to focus on the certain regions to rewrite during the optimization operation.
18. The non-transitory, computer-readable storage medium of claim 13 , wherein:
the specifying is provided via a user-specified input where the user is able to inference how to rewrite the SQL statement.