Method, system and program product for rewriting structured query language (SQL) statements

• US 8,185,518 B2
• Filed: 11/12/2004
• Issued: 05/22/2012
• Est. Priority Date: 11/12/2004
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bloom, Howard S., Froehlich, Roy, Jobson, Thomas A. Jr., Kanyock, Edith A., Matula, Charles F., Rosenberg, Arnold M.
• Primary Examiner(s): Cottingham, John R.
• Assistant Examiner(s): Reyes, Mariela
• Application Number:
  US10/987,346
• Publication Number:
  US 20060106766A1
• Time in Patent Office: 2,748 Days
• Field of Search: 707/714, 707/737
• US Class Current: 707/714
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

Under the present invention, a SQL statement having search criteria is received. Upon receipt, a table that lists all possible combinations of the search criteria is created. From the table, a set of patterns among the possible combinations is identified. Based on these patterns, the table is then sorted. Once sorted, the table is divided into a set of temporary tables based on the set of patterns/sorting operation. The set of temporary tables are then individually joined with the SQL statement and separate searches are conducted. A new set of temporary tables is then generated and populated with results of the separate searches. These result tables are then unioned/combined into a single result table.

Claims

1. A method for rewriting Structured Query Language (SQL) statements:

receiving a SQL statement having search criteria that include conditions that pertain to a plurality of fields in a plurality of database tables;

creating a table from the SQL statement that lists all possible combinations of the search criteria in the SQL statement, every combination reflecting a subset of information used to determine combined results;

identifying a set of patterns among the all possible combinations of the SQL statement based on the fields referenced in the combinations, and sorting the table based on the set of patterns;

dividing the table into a set of temporary tables based on the sorting;

joining the set of temporary tables with the SQL statement, and generating a new set of temporary tables, each table consisting of results of a search conducted using a separate portion of the SQL statement as joined with the set of temporary tables; and

combining all results from the new set of temporary tables into a single result table.

2. The method of claim 1, wherein the search criteria comprise a plurality of search objects joined by at least one search expression.

3. The method of claim 2, wherein the at least one search expression comprises a Boolean search expression.

4. The method of claim 1, wherein the joining step comprises individually joining each of the set of temporary tables with the SQL statement, and wherein the generating step comprises generating a single new temporary table for each single temporary table joined with the SQL statement.

5. The method of claim 4, wherein the joining comprises an inner join.

6. The method of claim 1, wherein the dividing step comprises dividing the table into a temporary table for each of the set of patterns that is identified.

7. The method of claim 6, wherein the dividing step rewrites the SQL statement as separate indices.

8. The method of claim 1, further comprising searching the final result table.

9. A system for rewriting Structured Query Language (SQL) statements, comprising:

a processor; and

a memory, the memory having;

a system for receiving a SQL statement having search criteria that include conditions that pertain to a plurality of fields in a plurality of database tables;

a system for creating a table from the SQL statement that lists all possible combinations of the search criteria in the SQL statement, every combination reflecting a subset of information used to determine combined results;

a system for identifying a set of patterns among the all possible combinations of the SQL statement based on the fields referenced in the combinations, and sorting the table based on the set of patterns;

a system for dividing the table into a set of temporary tables based on the set of patterns;

a system for joining the set of temporary tables with the SQL statement, and generating a new set of temporary tables, each table containing results of a search conducted using a separate portion of the SQL statement as joined with the set of temporary tables; and

a system for combining all results from the new set of temporary tables into a single result table.

10. The system of claim 9, wherein the search criteria comprise a plurality of search objects joined by at least one search expression.

11. The system of claim 10, wherein the at least one search expression comprises a Boolean search expression.

12. The system of claim 9, wherein the system for joining individually joins each of the set of temporary tables with the SQL statement, and wherein the system for generating generates a single new temporary table for each single temporary table joined with the SQL statement.

13. The system of claim 12, wherein the join comprises an inner join.

14. The system of claim 9, wherein the system for dividing divides the table into a temporary table for each of the set of patterns that is identified.

15. The system of claim 14, wherein the system for dividing rewrites the SQL statement as separate indices.

16. A computer system having program product stored thereon for rewriting Structured Query Language (SQL) statements, which when executed, comprises:

program code for receiving a SQL statement having search criteria that include conditions that pertain to a plurality of fields in a plurality of database tables;

program code for creating a table from the SQL statement that lists all possible combinations of the search criteria in the SQL statement, every combination reflecting a subset of information used to determine combined results;

program code for identifying a set of patterns among the all possible combinations of the SQL statement based on the fields referenced in the combinations, and sorting the table based on the set of patterns;

program code for dividing the table into a set of temporary tables based on the set of patterns;

program code for joining the set of temporary tables with the SQL statement, and generating a new set of temporary tables, each table containing results of a search conducted using a separate portion of the SQL statement as joined with the set of temporary tables; and

program code for combining all results from the new set of temporary tables into a single result table.

17. The computer system of claim 16, wherein the search criteria comprise a plurality of search objects joined by at least one search expression.

18. The computer system of claim 17, wherein the at least one search expression comprises a Boolean search expression.

19. The computer system of claim 16, wherein the program code for joining individually joins each of the set of temporary tables with the SQL statement, and wherein the program code for generating generates a single new temporary table for each single temporary table joined with the SQL statement.

20. The computer system of claim 19, wherein the join comprises an inner join.

21. The computer system of claim 16, wherein the program code for dividing divides the table into a temporary table for each of the set of patterns that is identified.

22. The computer system of claim 21, wherein the program code for dividing rewrites the SQL statement as separate indices.

23. A method for deploying an application for rewriting Structured Query Language (SQL) statements, comprising:

providing a computer infrastructure being operable to;

receive a SQL statement having search criteria that include conditions that pertain to a plurality of fields in a plurality of database tables;

create a table from the SQL statement that lists all possible combinations of the search criteria in the SQL statement, every combination reflecting a subset of information used to determine combined results;

identify a set of patterns among the all possible combinations of the SQL statement based on the fields referenced in the combinations, and sorting the table based on the set of patterns;

divide the table into a set of temporary tables based on the set of patterns;

join the set of temporary tables with the SQL statement, and generating a new set of temporary tables, each table containing results of a search conducted using a separate portion of the SQL statement as joined with the set of temporary tables; and

combine all results from the new set of temporary tables into a single result table.

24. Computer software embodied in a data storage medium for rewriting Structured Query Language (SQL) statements, the computer software comprising instructions to cause a processor of a computer system to perform the following functions:

receive a SQL statement having search criteria;

create a table from the SQL statement that lists all possible combinations of the search criteria in the SQL statement;

identify a set of patterns among the all possible combinations of the SQL statement, and sorting the table based on the set of patterns;

divide the table into a set of temporary tables based on the set of patterns;

join the set of temporary tables with the SQL statement, and generating a new set of temporary tables, each table containing results of a search conducted using a separate portion of the SQL statement as joined with the set of temporary tables; and

combine all results from the new set of temporary tables into a single result table.