Using overlapping partitions of data for query optimization

• US 6,014,656 A
• Filed: 06/21/1996
• Issued: 01/11/2000
• Est. Priority Date: 06/21/1996
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Jenkins, Robert J., Hallmark, Gary
• Primary Examiner(s): Lintz, Paul R.
• Assistant Examiner(s): Havan, Thu-Thao
• Application Number:
  US08/673,714
• Time in Patent Office: 1,299 Days
• Field of Search: 707/2, 707/3, 707/4, 707/201, 707/203, 707/5, 707/6, 707/7, 707/8, 707/10, 382/283, 364/283, 364/974, 364/221, 364/232, 364/282, 364/949, 702/103, 702/104, 702/102, 705/35, 709/201, 400/110, 400/70
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24554 Unary operations; Data partitioning operations
• CPC Class Codes: G06F 16/24557 Efficient disk access during query execution
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database

Abstract

A method and apparatus for executing queries on a set of data that has been partitioned into a plurality of partitions based on a partitioning key is provided. A query is received that includes a reference to a second key. The second key is not part of the partitioning key but has a predetermined correlation with the partitioning key. This second key is referred to as an overlapping partition key. A subset of the plurality of partitions is selected to be scanned based on the reference to the second key and the predetermined correlation with the partitioning key. The query is then executed by scanning only those partitions of the plurality of partitions that belong to the subset of partitions. The overlapping partition key provides for reduced query execution time even when the partitioning key is not directly involved in the query. Specifically, the overlapping partition key permits a partial table scan in situations that would require a fill table scan with partitioning alone.

Claims

1. A method for executing queries that specify data from a set of data that has been partitioned into a plurality of partitions based on a first key, the method comprising the computer implemented steps of:

receiving a query that includes a reference to a second key, wherein said second key is not part of said first key but has a predetermined correlation with said first key;

selecting a subset of said plurality of partitions to scan based on said reference to said second key and said predetermined correlation with said first key; and

executing said query by scanning only those partitions of said plurality of partitions that belong to said subset of partitions.

2. The method of claim 1 further comprising the steps of:

determining a set of query predicates from said query;

adding said set of query predicates to a set of selection predicates;

adding one or more predicates to said set of selection predicates, said one or more predicates based on said predetermined correlation with said first key; and

using said set of selection predicates to select said subset of said plurality of partitions to scan.

3. The method of claim 2 further comprising the steps of:

after said step of adding one or more predicates to said set of selection predicates, transitively generating new predicates based on said set of selection predicates;

adding said new predicates to said set of selection predicates;

removing from said set of selection predicates those of said one or more predicates and said new predicates that cannot be evaluated in constant time;

removing from said set of selection predicates those of said new predicates that can produce an unknown result; and

wherein

said step of selecting a subset of said plurality of partitions to scan includes

evaluating constant predicates in said set of selection predicates, and

including in said subset those partitions corresponding to selection predicates having constant predicates that evaluate to a predetermined state.

4. The method of claim 2 further comprising the steps of:

excluding from said query plan those partitions of said plurality of partitions corresponding to selection predicates having constant predicates that evaluate to a predetermined state; and

including in a query plan only those partitions of said plurality of partitions corresponding to selection predicates having constant predicates that do not evaluate to said predetermined state.

5. The method of claim 1 further comprising the steps of:

receiving a request to modify a subset of data in said set of data, wherein said subset of data includes one or more values for said first key and one or more values for said second key;

determining whether said one or more values for said second key have said predetermined correlation with said one or more values for said first key;

if said one or more values for said second key have said predetermined correlation with said one or more values for said first key, then selecting a target partition based on said one or more values for said first key, and storing said subset of data in said target partition;

if said one or more values for said second key do not have said predetermined correlation with said one or more values for said first key, then generating an error message without adding said subset of data to said set of data.

6. The method of claim 1 further comprising the step of, for each partition of said plurality of partitions, generating data that indicates values of said second key that satisfy said predetermined correlation with values for said first key that correspond to said partition.

25. The method of claim 1 wherein the step of selecting the subset of said plurality of partitions to scan includes selecting at least two of said plurality of partitions.

7. A method of producing query plans for executing queries on a set of data that has been partitioned into a plurality of partitions based upon a first key, the method comprising the computer implemented steps of:

receiving a query;

selecting a subset of partitions to scan from said plurality of partitions, the selection being performed by

if said query includes a reference to a second key and does not refer to said first key, then determining said subset of partitions to scan based on said reference to said second key and a predetermined correlation between said second key and said first key, wherein said second key is not part of said first key; and

producing a query plan which includes only those partitions of said plurality of partitions that belong to said subset of partitions.

8. The method of claim 7 wherein said step of selecting a subset of partitions to scan further comprises the steps of:

if said query includes a reference to said first key and does not refer to said second key, then determining said subset of partitions to scan based on said reference to said first key and said first key; and

if said query includes a reference to both said first and second keys, then determining said subset of partitions to scan based on said reference to both said first and second keys and said predetermined correlation.

9. The method of claim 7 further comprising the steps of:

receiving a request to modify said set of data, wherein said request includes a subset of data having one or more values corresponding to one or more attributes from said first key and one or more values corresponding to one or more attributes from said second key; and

enforcing said predetermined correlation between said second key and said first key by

applying a first set of predicates to said subset of data to determine whether or not said subset of data complies with said predetermined correlation,

if said subset of data complies with said predetermined correlation, then allowing said request, and

if said subset of data does not comply with said predetermined correlation, then disallowing said request.

26. The method of claim 8 wherein said step of selecting the subset of partitions to scan further comprises the step of including all of said plurality of partitions in said subset of partitions to scan if said query does not refer to one of said first key or said second key.

10. A method for executing queries that specify data from a set of data that has been partitioned into a plurality of partitions based on a first key, the method comprising the computer implemented steps of:

receiving a query that includes a reference to a second key;

accessing one or more predicates, wherein said one or more predicates represent a predetermined correlation between said first key and said second key;

selecting a subset of said plurality of partitions to scan based on said reference to said second key and said one or more predicates; and

executing said query by scanning only those partitions of said plurality of partitions that belong to said subset of partitions.

11. The method of claim 10 wherein said one or more predicates correspond to one or more check constraints, said method further comprising the step of enforcing said predetermined correlation between said first key and said second key with said one or more check constraints, said one or more check constraints limiting values that can be stored in attributes of said second key.

12. The method of claim 10 further comprising the steps of:

adding a set of query predicates derived from said query to a set of selection predicates;

adding one or more predicates to said set of selection predicates, said one or more predicates based on said predetermined correlation with said first key; and

using said set of selection predicates to select said subset of said plurality of partitions to scan.

13. The method of claim 12 further comprising the steps of:

after said step of adding one or more predicates to said set of selection predicates, transitively generating new predicates based on said set of selection predicates;

adding said new predicates to said set of selection predicates;

removing from said set of selection predicates those of said one or more predicates and said new predicates that cannot be evaluated in constant time;

removing from said set of selection predicates those of said new predicates that can produce an unknown result; and

wherein

said step of selecting a subset of said plurality of partitions to scan includes evaluating constant predicates in said set of selection predicates, and

including in said subset those partitions corresponding to selection predicates having constant predicates that evaluate to a predetermined state.

14. The method of claim 12 wherein said step of executing said query further comprises the steps of:

including in a query plan only those partitions of said plurality of partitions corresponding to selection predicates having constant predicates that evaluate to a first predetermined state; and

excluding from said query plan those partitions of said plurality of partitions corresponding to selection predicates having constant predicates that evaluate to a second predetermined state.

15. A method for executing queries that request data from a set of data that has been partitioned into a plurality of partitions based on a first key, the method comprising the computer implemented steps of:

receiving a query that includes a reference to an attribute that is part of a second key, wherein said second key is not part of said first key but has a predetermined correlation with said first key;

accessing a set of values associated with said attribute;

selecting a subset of said plurality of partitions to scan based on said reference to said attribute and said set of values; and

executing said query by scanning only those partitions of said plurality of partitions that belong to said subset of partitions.

16. The method of claim 15 wherein said set of values includes at least a high value and a low value for said attribute for each partition in said plurality of partitions, each high value representing the highest value present in said attribute within the corresponding partition, each low value representing the lowest value present in said attribute within the corresponding partition.

17. The method of claim 16 wherein said set of values are stored as part of an index on said attribute.

18. A method for executing queries on a set of data that has been partitioned into a plurality of partitions based on a first key, wherein said first key includes one or more attributes, the method comprising the computer implemented steps of:

receiving a query that includes a reference to a value from one of said one or more attributes, wherein a first set of data containing said value is stored in a first partition of said plurality of partitions and a second set of data contaning said value is stored in a second partition of said plurality of partitions;

selecting a subset of said plurality of partitions to scan based on said reference, wherein said subset includes said first and second partitions; and

executing said query by scanning only those partitions of said plurality of partitions that belong to said subset of partitions.

19. The method of claim 18 further comprising the steps of:

adding a set of query predicates to a set of selection predicates;

generating new predicates based on said set of selection predicates and one or more correlation predicates, said one or more correlation predicates based on a predetermined correlation of a second key with said first key;

removing from said set of selection predicates those of said one or more predicates and said new predicates that cannot be evaluated in constant time;

removing from said set of selection predicates those of said new predicates that can produce an unknown result; and

using said set of selection predicates to select said subset of said plurality of partitions to scan.

20. The method of claim 19 further comprising the steps of:

excluding from said subset of said plurality of partitions to scan those partitions of said plurality of partitions corresponding to selection predicates having constant predicates that evaluate to a predetermined state; and

including in said subset of said plurality of partitions to scan those partitions of said plurality of partitions corresponding to selection predicates having constant predicates that do not evaluate to said predetermined state.

21. The method of claim 18 further comprising the step of, for each partition of said plurality of partitions, generating a set of predicates that indicate values for said first key that satisfy a predetermined correlation with values for said second key that correspond to said partition.

22. A method for executing queries on a set of data that has been partitioned into a plurality of partitions, the method comprising the computer implemented steps of:

receiving a query that includes a reference to a first key;

accessing data that indicates upper and lower boundary values for said plurality of partitions, wherein said upper and lower boundary values of each of said plurality of partitions are independent of the upper and lower boundary values of the other of said plurality of partitions; and

selecting a subset of said plurality of partitions upon which to execute said query based on said reference and said upper and lower boundary values of each of said plurality of partitions.

23. A computer system comprising:

a processor; and

a memory coupled to said processor, said memory having stored therein

a first set of data that has been partitioned into a plurality of partitions,

a second set of data indicating upper boundary values for each of said plurality of partitions,

a third set of data, separate from said second set of data, indicating lower boundary values for each of said plurality of partitions, and

sequences of instructions which, when executed by said processor, cause said processor to select a subset of said plurality of partitions to scan based on a set of query predicates and said upper and lower boundary values of said plurality of partitions.

24. A machine-readable medium having stored thereon data representing sequences of instructions, said sequences of instructions including sequences of instructions which, when executed by a processor, cause said processor to perform the steps of:

receiving a query on a set of data, wherein said set of data has been partitioned into a plurality of partitions based on a first key, wherein said query includes a reference to a second key, and wherein said second key is not part of said first key but has a predetermined correlation with said first key;

selecting a subset of said plurality of partitions to scan based on said reference to said second key and said predetermined correlation with said first key; and

executing said query by scanning only those partitions of said plurality of partitions that belong to said subset of partitions.

27. The machine-readable medium of claim 24 further comprising instructions for performing the steps of:

determining a set of query predicates from said query;

adding said set of query predicates to a set of selection predicates;

adding one or more predicates to said set of selection predicates, said one or more predicates based on said predetermined correlation with said first key; and

using said set of selection predicates to select said subset of said plurality of partitions to scan.

28. The machine-readable medium of claim 27 further comprising instructions for performing the steps of:

after said step of adding one or more predicates to said set of selection predicates, transitively generating new predicates based on said set of selection predicates;

adding said new predicates to said set of selection predicates;

removing from said set of selection predicates those of said one or more predicates and said new predicates that cannot be evaluated in constant time;

removing from said set of selection predicates those of said new predicates that can produce an unknown result; and

wherein

said step of selecting a subset of said plurality of partitions to scan includes

evaluating constant predicates in said set of selection predicates, and

including in said subset those partitions corresponding to selection predicates having constant predicates that evaluate to a predetermined state.

29. The machine-readable medium of claim 27 further comprising instructions for performing the steps of:

excluding from said query plan those partitions of said plurality of partitions corresponding to selection predicates having constant predicates that evaluate to a predetermined state; and

including in a query plan only those partitions of said plurality of partitions corresponding to selection predicates having constant predicates that do not evaluate to said predetermined state.

30. The machine-readable medium of claim 24 further comprising instructions for performing the steps of:

receiving a request to modify a subset of data in said set of data, wherein said subset of data includes one or more values for said first key and one or more values for said second key;

determining whether said one or more values for said second key have said predetermined correlation with said one or more values for said first key;

if said one or more values for said second key have said predetermined correlation with said one or more values for said first key, then selecting a target partition based on said one or more values for said first key, and storing said subset of data in said target partition;

if said one or more values for said second key do not have said predetermined correlation with said one or more values for said first key, then generating an error message without adding said subset of data to said set of data.

31. The machine-readable medium of claim 24 further comprising instructions for performing the step of, for each partition of said plurality of partitions, generating data that indicates values of said second key that satisfy said predetermined correlation with values for said first key that correspond to said partition.

32. The machine-readable medium of claim 24 wherein the step of selecting the subset of said plurality of partitions to scan includes selecting at least two of said plurality of partitions.

33. A machine-readable medium carrying one or more sequences of instructions for producing query plans for executing queries on a set of data that has been partitioned into a plurality of partitions based upon a first key, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

receiving a query;

selecting a subset of partitions to scan from said plurality of partitions, the selection being performed by

if said query includes a reference to a second key and does not refer to said first key, then determining said subset of partitions to scan based on said reference to said second key and a predetermined correlation between said second key and said first key, wherein said second key is not part of said first key; and

producing a query plan which includes only those partitions of said plurality of partitions that belong to said subset of partitions.

34. A machine-readable medium carrying one or more sequences of instructions for executing queries that specify data from a set of data that has been partitioned into a plurality of partitions based on a first key, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

receiving a query that includes a reference to a second key;

accessing one or more predicates, wherein said one or more predicates represent a predetermined correlation between said first key and said second key;

selecting a subset of said plurality of partitions to scan based on said reference to said second key and said one or more predicates; and

executing said query by scanning only those partitions of said plurality of partitions that belong to said subset of partitions.

35. A machine-readable medium carrying one or more sequences of instructions for executing queries that request data from a set of data that has been partitioned into a plurality of partitions based on a first key, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

receiving a query that includes a reference to an attribute that is part of a second key, wherein said second key is not part of said first key but has a predetermined correlation with said first key;

accessing a set of values associated with said attribute;

selecting a subset of said plurality of partitions to scan based on said reference to said attribute and said set of values; and

executing said query by scanning only those partitions of said plurality of partitions that belong to said subset of partitions.

36. A machine-readable medium carrying one or more sequences of instructions for executing queries on a set of data that has been partitioned into a plurality of partitions based on a first key, wherein said first key includes one or more attributes, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

receiving a query that includes a reference to a value from one of said one or more attributes, wherein a first set of data containing said value is stored in a first partition of said plurality of partitions and a second set of data containing said value is stored in a second partition of said plurality of partitions;

selecting a subset of said plurality of partitions to scan based on said reference, wherein said subset includes said first and second partitions; and

executing said query by scanning only those partitions of said plurality of partitions that belong to said subset of partitions.

37. A machine-readable medium carrying one or more sequences of instructions for executing queries on a set of data that has been partitioned into a plurality of partitions, wherein execution of the one or more sequences of instructions by one or more processors causes the one or more processors to perform the steps of:

receiving a query that includes a reference to a first key;

accessing data that indicates upper and lower boundary values for said plurality of partitions, wherein said upper and lower boundary values of each of said plurality of partitions are independent of the upper and lower boundary values of the other of said plurality of partitions; and

selecting a subset of said plurality of partitions upon which to execute said query based on said reference and said upper and lower boundary values of each of said plurality of partitions.