Partial query caching

• US 20050240570A1
• Filed: 04/22/2004
• Published: 10/27/2005
• Est. Priority Date: 04/22/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Ozbutun, Cetin
• Granted Patent: US 7,676,453 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

When a query is performed an execution plan is formed, which is made up of a plurality of plan operators. Before executing any given plan operator, a storage area is checked. If a results set has been stored corresponding to the plan operator, then the cached results set is used instead of the executing the plan operator. If no results set exists that corresponds to the particular plan operator, the plan operator is executed, and the results set that is output may be stored for use by another plan operator of another execution plan.

Claims

1. A method for processing requests to a database servers comprising:

receiving a first query at the database server from a user;

generating a first plan for executing the first query via the database server, wherein the plan includes a first plurality of plan operators that include at least a first plan operator that outputs a first results set;

storing the first results set;

receiving a second query from a user;

generating a second plan for executing the second query via the database server, wherein the second execution plan includes a second plurality of plan operators that include at least a second plan operator that corresponds to the first plan operator;

determining that at least a portion of the first results set may be used as a second results set of said second operator; and

computing the second query based on the first results set that was stored.

2. The method of claim 1, wherein the step of storing the first set of results includes associating a signature with the first results set.

3. The method of claim 2, wherein the signature is generated based on operations performed by the first plan operator.

4. The method of claim 2, wherein the step of determining includes at least comparing a signature of the second plan operator with stored signatures of a plurality of plan operators to determine if each of the plurality of plan operators is the second plan operator.

5. The method of claim 1, wherein the first plan operator and the second plan operator are identical.

6. The method of claim 1, where the first plan operator and the second plan operator are not identical.

7. The method of claim 1, where the first plan operator and the second plan operator are similar.

8. The method of claim 1, wherein the first plan operator and the second plan operator are identical, but the first results set and the second results set are different.

9. The method of claim 1, wherein the first execution plan includes a third plan operator, the method further comprises:

outputting a third results set in response to the third plan operator, wherein the third results set is not stored.

10. The method of claim 1, wherein the step of determining includes at least determining when the first results set was computed.

11. The method of claim 1, wherein a difference exist between the first plan operator and the second plan operator, and the step of computing includes at least modifying the first results set based on the difference.

12. A machine-readable medium carrying one or more sequences of instructions, which when executed by one or more processors, causes the one or more processors to perform a method comprising the steps of:

receiving a first query at the database server from a user;

generating a first plan for executing the first query via the database server, wherein the plan includes a first plurality of plan operators that include at least a first plan operator that outputs a first results set;

storing the first results set;

receiving a second query from a user;

generating a second plan for executing the second query via the database server, wherein the second execution plan includes a second plurality of plan operators that include at least a second plan operator that corresponds to the first plan operator;

determining that at least a portion of the first results set may be used as a second results set of said second operator; and

computing the second query based on the first results set that was stored.

13. The machine-readable medium of claim 12, wherein the step of storing the first set of results includes associating a signature with the first results set.

14. The machine-readable medium of claim 13, wherein the signature is generated based on operations performed by the first plan operator.

15. The machine-readable medium of claim 13, wherein the step of determining includes at least comparing a signature of the second plan operator with stored signatures of a plurality of plan operators to determine if each of the plurality of plan operators is the second plan operator.

16. The machine-readable medium of claim 12, wherein the first plan operator and the second plan operator are identical.

17. The machine-readable medium of claim 12, where the first plan operator and the second plan operator are not identical.

18. The machine-readable medium of claim 12, where the first plan operator and the second plan operator are similar.

19. The machine-readable medium of claim 12, wherein the first plan operator and the second plan operator are identical, but the first results set and the second results set are different.

20. The machine-readable medium of claim 12, wherein the first execution plan includes a third plan operator, the method further comprises:

outputting a third results set in response to the third plan operator, wherein the third results set is not stored.

21. The machine-readable medium of claim 12, wherein the step of determining includes at least determining when the first results set was computed.

22. The machine-readable medium of claim 12, wherein a difference exist between the first plan operator and the second plan operator, and the step of computing includes at least modifying the first results set based on the difference.