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

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

• Current Assignee: X Corp. (f/k/a Twitter, Inc.) (X Holdings Corp.)
• Original Assignee: International Business Machines Corporation
• Inventors: Johnson, Dallas L., Froehlich, Roy, Matula, Charles F.
• Granted Patent: US 7,711,692 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24535 of sub-queries or views
• CPC Class Codes: G06F 16/24544 Join order optimisation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

Under the present invention, when a SQL statement having a full select portion that references a view containing a join operation (e.g., a LOJ operation) is received, the reference to the view will be moved out of the full select portion. Once moved, the view and its join operation will be resolved separately from the SQL statement. The results of this resolution will be placed in a temporary table. A reference to the temporary table is then inserted in the full select portion, and the SQL statement is thereafter performed.

Claims

1. A method for rewriting view statements in Structured Query Language (SQL) statements, comprising:

receiving a SQL statement with a full select portion having a reference to a view that contains a join operation;

moving the reference to the view out of the full select portion;

resolving the view and the join operation separately from the SQL statement, and creating a temporary table with a result of the resolving; and

inserting a reference to the temporary table in the full select portion.

2. The method of claim 1, wherein the join operation is a left outer join operation.

3. The method of claim 2, wherein the left outer join operation joins a plurality of tables into a single table.

4. The method of claim 1, wherein the full select portion defines search criteria for the SQL statement.

5. The method of claim 1, wherein the full select portion comprises a SQL predicate.

6. The method of claim 5, wherein the SQL predicate defines a boundary of the SQL statement.

7. The method of claim 1, wherein the method is computer-implemented.

8. A system for rewriting view statements in Structured Query Language (SQL) statements, comprising:

a system for receiving a SQL statement with a full select portion having a reference to a view that contains a join operation;

a system for moving the reference to the view out of the full select portion;

a system for resolving the view and the join operation separately from the SQL statement, and creating a temporary table with a result of the resolving; and

a system for inserting a reference to the temporary table in the full select portion and thereafter performing the SQL statement.

9. The system of claim 8, wherein the join operation is a left outer join operation.

10. The system of claim 9, wherein the left outer join operation joins a plurality of tables into a single table.

11. The system of claim 8, wherein the full select portion defines search criteria for the SQL statement.

12. The system of claim 8, wherein the full select portion comprises a SQL predicate.

13. The system of claim 12, wherein the SQL predicate defines a boundary of the SQL statement.

14. A program product stored on a recordable medium for rewriting view statements in Structured Query Language (SQL) statements, which when executed, comprises:

program code for receiving a SQL statement with a full select portion having a reference to a view that contains a join operation;

program code for moving the reference to the view out of the full select portion;

program code for resolving the view and the join operation separately from the SQL statement, and creating a temporary table with a result of the resolving; and

program code for inserting a reference to the temporary table in the full select portion and thereafter performing the SQL statement.

15. The program product of claim 14, wherein the join operation is a left outer join operation.

16. The program product of claim 15, wherein the left outer join operation joins a plurality of tables into a single table.

17. The program product of claim 14, wherein the full select portion defines search criteria for the SQL statement.

18. The program product of claim 14, wherein the full select portion comprises a SQL predicate.

19. The program product of claim 18, wherein the SQL predicate defines a boundary of the SQL statement.

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

providing a computer infrastructure being operable to;

receive a SQL statement with a full select portion having a reference to a view that contains a join operation;

move the reference to the view out of the full select portion;

resolve the view and the join operation separately from the SQL statement, and create a temporary table with a result of the resolution; and

insert a reference to the temporary table in the full select portion and thereafter perform the SQL statement.

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

receive a SQL statement with a full select portion having a reference to a view that contains a join operation;

move the reference to the view out of the full select portion;

resolve the view and the join operation separately from the SQL statement, and create a temporary table with a result of the resolution; and

insert a reference to the temporary table in the full select portion and thereafter perform the SQL statement.