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

• US 20060106766A1
• Filed: 11/12/2004
• Published: 05/18/2006
• Est. Priority Date: 11/12/2004
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Jobson, Thomas A. Jr., Froehlich, Roy, Matula, Charles F., Kanyock, Edith A., Bloom, Howard S., Rosenberg, Arnold M.
• Granted Patent: US 8,185,518 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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;

creating a table that lists all possible combinations of the search criteria;

identifying a set of patterns among the all possible 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 that contain results of searches conducted using the SQL statement as joined with the set of temporary tables; and

combining 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:

a system for receiving a SQL statement having search criteria;

a system for creating a table that lists all possible combinations of the search criteria;

a system for identifying a set of patterns among the all possible 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 for generating a new set of temporary tables that contain results of searches conducted with the SQL statement as joined with the set of temporary tables; and

a system for combining 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 program product stored on a recordable medium for rewriting Structured Query Language (SQL) statements, which when executed, comprises:

program code for receiving a SQL statement having search criteria;

program code for creating a table that lists all possible combinations of the search criteria;

program code for identifying a set of patterns among the all possible 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 for generating a new set of temporary tables that contain results of searches conducted with the SQL statement as joined with the set of temporary tables; and

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

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

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

19. The program product 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 program product of claim 19, wherein the join comprises an inner join.

21. The program product 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 program product 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;

create a table that lists all possible combinations of the search criteria;

identify a set of patterns among the all possible combinations, and sort 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 generate a new set of temporary tables that contain results of searches conducted with the SQL statement as joined with the set of temporary tables; and

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

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

receive a SQL statement having search criteria;

create a table that lists all possible combinations of the search criteria;

identify a set of patterns among the all possible combinations, and sort 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 generate a new set of temporary tables that contain results of searches conducted with the SQL statement as joined with the set of temporary tables; and

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