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

• US 7,711,692 B2
• Filed: 11/12/2004
• Issued: 05/04/2010
• 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.
• Primary Examiner(s): Mizrahi; Diane
• Application Number:
  US10/987,342
• Publication Number:
  US 20060106765A1
• Time in Patent Office: 1,999 Days
• Field of Search: 717/137, 717/140, 707 1- 6, 707/102, 340/408
• US Class Current: 707/718
• 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 computer implemented method for rewriting view statements in Structured Query Language (SQL) statements, the method comprising the steps of:

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

in response to the full select portion having a reference to a view that contains a join operation, the computer moving the reference to the view out of the full select portion;

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

the computer inserting a reference to the temporary table, instead of the result of the resolving, 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. A computer system for rewriting view statements in Structured Query Language (SQL) statements, the computer system comprising:

a central processing unit, a random access memory, and a computer readable storage media;

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

second program instructions, responsive to the full select portion having a reference to a view that contains a join operation, to move the reference to the view out of the full select portion;

third program instructions to resolve the view and the join operation separately from the SQL statement, and create a temporary table with a result of the third program instructions resolving the view and the join operation separately from the SQL statement; and

fourth program instructions to insert a reference to the temporary table, instead of the result of the third program instructions resolving the view and the join operation separately from the SQL statement, in the full select portion and thereafter perform the SQL statement; and

wherein

the first, second, third and fourth program instructions are stored on the computer readable storage media for execution by the central processing unit via the random access memory.

8. The computer system of claim 7 wherein the second program instructions are responsive to the full select portion having a reference to a view that does not contain a join operation to avoid moving the reference to the view out of the full select portion.

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

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

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

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

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

14. A computer program product for rewriting view statements in Structured Query Language (SQL) statements, the computer program product comprising:

a computer readable storage medium;

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

second program code, responsive to the full select portion having a reference to a view that contains a join operation, for moving the reference to the view out of the full select portion;

third 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

fourth program code for inserting a reference to the temporary table, instead of the results of the third program code resolving the view and the join operation separately from the SQL statement, in the full select portion and thereafter performing the SQL statement; and

wherein

the first, second, third and fourth program code are stored on the computer readable storage medium for execution by a CPU.

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

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

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

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

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

20. The computer program product of claim 14 wherein the second program code is responsive to the full select portion having a reference to a view that does not contains a join operation, to avoid moving the reference to the view out of the full select portion.

21. A computer implemented method for rewriting view statements in Structured Query Language (SQL) statements, the method comprising the steps of:

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

the computer responding to the full select portion having a reference to a view that contains a join operation, by moving the reference to the view out of the full select portion;

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

the computer inserting a reference to the temporary table, instead of the results of the resolving step, in the full select portion and subsequently executing the SQL statement with a central processing unit.