Rewriting table functions as SQL strings

• US 7,680,862 B2
• Filed: 04/18/2005
• Issued: 03/16/2010
• Est. Priority Date: 04/18/2005
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Chong, Eugene Inseok, Das, Souripriya, Eadon, George, Srinivasan, Jagannathan
• Primary Examiner(s): Ly; Cheyne D
• Application Number:
  US11/108,242
• Publication Number:
  US 20060235837A1
• Time in Patent Office: 1,793 Days
• Field of Search: 707 1- 5, 707/100, 707/103.R, 707/203, 707/206, 707/10, 707/102, 707/104.1, 707/200, 707/7, 707/20, 707/204, 709/203, 709/222, 709/223, 709/224, 709/228, 709/201, 715/513, 715/514, 715/141, 715/530
• US Class Current: 707/753
• CPC Class Codes: G06F 16/24534 Query rewriting; Transformation

Abstract

The TABLE function mechanism available in a RDBMS is used to integrate RDF models into SQL queries. The table function invocation takes parameters including an RDF pattern, an RDF model, and an RDF rule base and returns result rows to the SQL query that contain RDF triples resulting from the application of the pattern to the triples of the model and the triples inferred by applying the rule base to the model. The RDBMS includes relational representations of the triples and the rules. Optimizations include indexes and materialized views of the representations of the triples, precomputed inferred triples, and a method associated with the TABLE function that rewrites the part of the SQL query that contains the TABLE function invocation as an equivalent SQL string. The latter technique is generally applicable to TABLE functions.

Claims

1. A relational database management system of the type wherein a table function returns a set of result rows which are represented in an SQL statement by a container for the table function, the relational database management system executing on a processor that has access to a storage device and

the relational database management system having the improvement comprising:

a rewrite method in the storage device, the rewrite method being associated with the table function returning an SQL string which does not include the table function and which when executed will return a set of result rows which is equivalent to the set of result rows returned by the table function,

the relational database system executing the rewrite method prior to executing the container to obtain the SQL string and thereupon rewriting the SQL statement such that the container for the table function is replaced by the returned SQL string.

2. The relational database management system set forth in claim 1 wherein:

the table function has a parameter; and

the rewrite method uses the parameter in producing the returned SQL string.

3. The relational database management system set forth in claim 2 wherein:

the rewrite method determines from the parameter whether it is possible to produce the SQL string and provides an indication when it is not possible; and

the relational database management system responds to the indication by not rewriting the SQL statement.

4. The relational database management system set forth in claim 1 wherein:

the rewrite method determines whether it is possible to produce the SQL string and provides an indication when it is not possible; and

the relational database system responds to the indication by not rewriting the SQL statement.

5. The relational database management system set forth in claim 4 further comprising:

a runtime execution method that is associated with the table function, the relational database management system executing the runtime execution method when the container is executed if the rewrite method has determined that it is not possible to produce the SQL string.

6. The relational database management system set forth in claim 1 further comprising:

a runtime execution method that is associated with the table function, the rewrite method being optional and the relational database management system executing the runtime execution method when the container is executed if the rewrite method is not present.

7. The relational database management system set forth in claim 1 wherein:

the relational database system further optimizes the rewritten SQL statement.

8. The relational database management system set forth in claim 1 wherein:

the rewrite method is optional; and

if there is no rewrite method, the relational database system does not rewrite the SQL statement.

9. The relational database management system set forth in claim 1 wherein:

the table function and the rewrite method are provided by the relational database management system.

10. The relational database management system set forth in claim 1 wherein:

the table function and the rewrite method are provided by a user of the relational database management system.

11. The relational database management system set forth in claim 1 wherein:

the container is a TABLE clause.

12. The relational database management system set forth in claim 1 wherein:

the container is a parameterized view that is defined using the table function.

13. A storage device that is accessible to a processor, the storage device being characterized in that:

the storage device contains code which, when executed by the processor, implements a relational database management system of the type wherein a table function returns a set of result rows which are represented in an SQL statement by a container for the table function,

the relational database management system having the improvement comprising;

a rewrite method, the rewrite method being associated with the table function and returning an SQL string which does not include the table function and which when executed will return a set of result rows which is equivalent to the set of result rows returned by the table function,

the relational database system executing the rewrite method prior to executing the container to obtain the SQL string and thereupon rewriting the SQL statement such that the container for the table function is replaced by the returned SQL string.

14. A method executed by a relational database management system of the type wherein a table function returns a set of result rows which are represented in an SQL statement by a container for the table function, the relational database management system executing on a processor that has access to a storage device and

the method comprising the steps of:

receiving the SQL statement;

making an SQL string which does not include the table function and which, when executed, will return a set of result rows which is equivalent to the set of result rows returned by the container function; and

rewriting the SQL statement such that the container is replaced with the SQL string prior to executing the SQL statement.

15. The method set forth in claim 14 wherein:

the table function has a parameter; and

the parameter is used in the step of making the SQL string.

16. The method set forth in claim 15 wherein

the parameter is used in the step of making the SQL string to determine whether it is possible to make the SQL string and

the method further comprises the steps of:

providing an indication when it is not possible to produce the SQL string; and

responding to the indication by not performing the step of rewriting the SQL statement.

17. The method set forth in claim 14 wherein the method further comprises the steps of:

providing an indication when it is not possible to produce the SQL string; and

responding to the indication by not performing the step of rewriting the SQL statement.

18. The method set forth in claim 17 further comprising the step of:

executing the table function when the SQL statement is executed.

19. The method set forth in claim 14 wherein

the step of making the SQL string is optional and

the method further comprises the step performed when the step of making the SQL string is not performed of:

executing the table function when the SQL statement is executed.

20. The method set forth in claim 14 further comprising the step of:

optimizing the SQL statement after the SQL statement has been rewritten.

21. The method set forth in claim 14 wherein:

the step of making the SQL string is optional; and

if the step of making the SQL string is not performed, the step of rewriting the SQL statement is also not performed.

22. The method set forth in claim 14 wherein:

in the step of receiving the SQL statement, the container is a TABLE clause.

23. The method set forth in claim 14 wherein:

in the step of receiving the SQL statement, the container is a parameterized view that is defined using the table function.

24. A storage device that is accessible to a processor, the storage device being characterized in that:

the storage device contains code which, when executed by the processor, implements a method executed by a relational database management system of the type wherein a table function returns a set of result rows which are represented in an SQL statement by a container for the table function,

the method comprising the steps of;

receiving the SQL statement;

making an SQL string which does not include the table function and which t, when executed, will return a set of result rows which is equivalent to the set of result rows returned by the container function; and

rewriting the SQL statement such that the container is replaced with the SQL string prior to executing the SQL statement.