Partial query caching

• US 7,676,453 B2
• Filed: 04/22/2004
• Issued: 03/09/2010
• 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
• Primary Examiner(s): Vo; Tim T.
• Assistant Examiner(s): Morrison; Jay A
• Application Number:
  US10/830,510
• Publication Number:
  US 20050240570A1
• Time in Patent Office: 2,147 Days
• Field of Search: 707/3
• 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 computing a query at a database server comprising:

generating a plurality of first execution plans at the database server, wherein each first execution plan of said plurality of first execution plans is for executing a first query, wherein said each first execution plan includes a first plurality of plan operators, each plan operator of said first plurality of plan operators defining operations that are performed by the database server to execute said first query, execution of each plan operator of said first plurality of plan operators generating a first results set;

generating a plurality of first signatures by generating, for each plan operator of the first pluralities of plan operators of said plurality of first execution plans, a first signature;

generating a plurality of first results sets by executing each plan operator in said first pluralities of plan operators to generate a respective first results set for said each plan operator;

storing each first results set of said plurality of first results sets, wherein a first results set of a particular plan operator is stored in association with the respective first signature of said particular plan operator;

after storing the plurality of first results sets in association with said first signatures said database server receiving a second query from a user;

generating a second plan for executing the second query at the database server, wherein the second execution plan includes a second plurality of plan operators that include at least one second plan operator, each plan operator of said second plurality of plan operators defining operations that are performed by the database server to execute said second query;

generating a second signature for said at least one second plan operator of said second plurality of plan operators;

using said second signature, searching signatures of said plurality of first signatures to determine whether at least a subset of a particular first results set of said plurality of first results sets is usable as a second results set of said second operator; and

upon determining that at least a subset of said particular first results set of said plurality of first results sets is usable as a second results set of said second operator, computing a second results set for the second query based on the subset of the particular first results set that was stored.

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

3. The method of claim 1, wherein the plan operator of said first pluralities of plan operators that is associated with said particular first results set, at least the subset of which is usable as said second results set, is identical to the second plan operator.

4. The method of claim 1, wherein the plan operator of said first pluralities of plan operators that is associated with said particular first results set, at least the subset of which is usable as said second results set, is not identical to the second plan operator.

5. The method of claim 1, wherein the plan operator of said first pluralities of plan operators that is associated with said particular first results set, at least the subset of which is usable as said second results set, is similar to the second plan operator.

6. The method of claim 1, wherein the plan operator of said first pluralities of plan operators that is associated with said particular first results set, at least the subset of which is usable as said second results set, is identical to the second plan operator, but the first results set and the second results set are different.

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

generating a third results set by executing the third plan operator, wherein the third results set is not stored.

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

9. The method of claim 1, wherein a difference exists between the plan operator of said the first pluralities of plan operators that is associated with said particular first results set, at the subset of which is usable as said second results set, and the second plan operator, and the step of computing includes at least modifying the first results set based on the difference.

19. The method of claim 1, wherein receiving a second query from the user includes receiving the second query from a different user.

21. The method of claim 1, further comprising computing the subset of the first results set by applying a filter to the first results set that was stored, wherein the subset is generated based on a condition contained in the second query.

23. The method of claim 1, wherein determining further comprises searching for first signatures that correspond to plan operators that constitute larger portions of the query first.

24. The method of claim 8, further comprising determining whether the first results set is greater than a threshold age;

upon determining that the first results set is greater than the threshold age, (a) removing the first results set from storage or (b) removing the first results set from consideration to use the first results set as part of the second results set.

25. The method of claim 1, wherein plan operators are at least one of:

a group plan operator, a join plan operator, a filter plan operator, or a scan table plan operator.

10. A machine-readable storage medium storing 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:

generating a plurality of first execution plans at the database server, wherein each first execution plan of said plurality of first execution plans is for executing a first query, wherein said each first execution plan includes a first plurality of plan operators, each plan operator of said first plurality of plan operators defining operations that are performed by the database server to execute said first query, execution of each plan operator of said first plurality of plan operators generating a first results set;

generating a plurality of first signatures by generating, for each plan operator of the first pluralities of plan operators of said plurality of first execution plans, a first signature;

generating a plurality of first results sets by executing each plan operator in said first pluralities of plan operators to generate a respective first results set for said each plan operator;

storing each first results set of said plurality of first results sets, wherein a first results set of a particular plan operator is stored in association with the respective first signature of said particular plan operator;

after storing the plurality of first results sets in association with said first signatures, said database server receiving a second query from a user;

generating a second plan for executing the second query at the database server, wherein the second execution plan includes a second plurality of plan operators that include at least one second plan operator, each plan operator of said second plurality of plan operators defining operations that are performed by the database server to execute said second query;

generating a second signature for said at least one second plan operator of said second plurality of plan operators;

using said second signature, searching signatures of said plurality of first signatures to determine whether at least a subset of a particular first results set of said plurality of first results sets is usable as a second results set of said second operator; and

upon determining that at least a subset of said particular first results set of said plurality of first results sets is usable as a second results set of said second operator, computing a second results set for the second query based on the subset of the particular first results set that was stored.

11. The machine-readable storage medium of claim 10, wherein the first signature of a particular plan operator is generated based on operations performed by said particular plan operator.

12. The machine-readable storage medium of claim 10, wherein the plan operator of said first pluralities of plan operators that is associated with said particular first results set, at least the subset of which is usable as said second results set, is identical to the second plan operator.

13. The machine-readable storage medium of claim 10, wherein the plan operator of said first pluralities of plan operators that is associated with said particular first results set, at least the subset of which is usable as said second results set, is not identical to the second plan operator.

14. The machine-readable storage medium of claim 10, wherein the plan operator of said first pluralities of plan operators that is associated with said particular first results set, at least the subset of which is usable as said second results set, is similar to the second plan operator.

15. The machine-readable storage medium of claim 10, wherein the plan operator of said first pluralities of plan operators that is associated with said particular first results set, at least the subset of which is usable as said second results set, is identical to the second plan operator, but the first results set and the second results set are different.

16. The machine-readable storage medium of claim 10, wherein a first execution plan of the plurality of first execution plans includes a third plan operator, and further comprises:

generating a third results set by executing the third plan operator, wherein the third results set is not stored.

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

18. The machine-readable storage medium of claim 10, wherein a difference exists between the plan operator of said first pluralities of plan operators that is associated with said particular first results set, at least the subset of which is usable as said second results set, and the second plan operator, and the step of computing includes at least modifying the first results set based on the difference.

20. The machine-readable storage medium of claim 10, wherein receiving a second query from the user includes receiving the second query from a different user.

22. The machine-readable storage medium of claim 10, further comprising computing the subset of the first results set by applying a filter to the first results set that was stored, wherein the subset is generated based on a condition contained in the second query.

26. The machine-readable storage medium of claim 10, wherein determining further comprises searching for first signatures that correspond to plan operators that constitute larger portions of the query first.

27. The machine-readable storage medium of claim 17, further comprising determining whether the first results set is greater than a threshold age;

upon determining that the first results set is greater than the threshold age, (a) removing the first results set from storage or (b) removing the first results set from consideration to use the first results set as part of the second results set.

28. The machine-readable storage medium of claim 10, wherein plan operators are at least one of:

a group plan operator, a join plan operator, a filter plan operator, or a scan table plan operator.