Method and apparatus for eliminating partitions of a database table from a join query using implicit limitations on a partition key value

• US 20070027860A1
• Filed: 07/28/2005
• Published: 02/01/2007
• Est. Priority Date: 07/28/2005
• Status: Abandoned Application

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Kethireddy, Shantan, Bestgen, Robert, Tenner, Jeffrey
• Application Number:
  US11/191,470
• Publication Number:
  US 20070027860A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24524 Access plan code generation and invalidation; Reuse of access plans
• CPC Class Codes: G06F 16/24544 Join order optimisation

Abstract

A database facility supports database join queries in a database environment having at least one database table divided into multiple partitions based on a partition key value. The facility determines whether the values in a table joined to the partitioned table place an implicit limitation on the partition key, and eliminates from query evaluation any partitions which do not satisfy the implicit limitation. Preferably, the database uses a star schema organization, in which implicit limitations in a relatively small dimension table are used to eliminate partitions in a relatively large fact table.

Claims

1. A method for executing a database join query in a computer system, comprising the computer-executed steps of:

identifying first and second tables of said join query, said first table comprising a plurality of partitions, each record of said first table containing a respective partition key value;

automatically determining whether data in said second table places an implicit limitation on the values of said partition key which satisfy said join query;

responsive to said step of automatically determining whether data in said second table places an implicit limitation on the values of said partition key which satisfy said join query, automatically eliminating from evaluation at least one partition which does not contain any values of said partition key satisfying said implicit limitation; and

automatically evaluating said join query only against partitions of said first table not eliminated by said automatically eliminating step.

2. The method for executing a database join query of claim 1,

wherein said partition key is a surrogate key used to reference records in said second table.

3. The method for executing a database join query of claim 1, wherein said database uses a star schema organization, wherein said first table is a fact table and said second table is a dimension table.

4. The method for executing a database join query of claim 1, wherein said step of automatically determining whether data in said second table places an implicit limitation on the values of said partition key which satisfy said join query comprises computer-executed steps of:

automatically constructing a reduced table from said second table using at least one logical condition of said join query; and

automatically comparing partition key values in said reduced table to said partitions to determine, with respect to each partition, whether at least one record in said reduced table contains a partition key value corresponding to the respective partition.

5. The method for executing a database query of claim 4, wherein said step of automatically constructing a reduced table from said second table comprises automatically determining whether to construct said reduced table using at least one heuristic criterion.

6. The method for executing a database query of claim 5, wherein said at least one heuristic criterion comprises at least one criterion from the set consisting of:

(a) the size of said first table;

(b) the size of said second table;

(c) an estimated cardinality of said partition key in said reduced table; and

(d) an estimated cardinality of said partition key in said first table.

7. The method for executing a database query of claim 1, wherein said step of automatically determining whether data in said second table place an implicit limitation on the values of said partition key is performed by an optimizer in advance of executing said query, said optimizer constructing a query optimization strategy which eliminates from evaluation at least one partition which does not contain any values of said partition key satisfying said implicit limitations.

8. A computer program product for executing a database join query comprising:

a plurality of computer-executable instructions recorded on signal-bearing media, wherein said instructions, when executed by at least one computer system, cause the at least one computer system to perform the steps of;

receiving a join query against information in a database, said join query joining at least two tables, including a first table having a plurality of partitions and a second table, each record of said first table containing a respective partition key value;

determining whether data in said second table places an implicit limitation on the values of said partition key which satisfy said join query;

responsive to said step of determining whether data in said second table places an implicit limitation on the values of said partition key which satisfy said join query, eliminating from evaluation at least one partition which does not contain any values of said partition key satisfying said implicit limitation; and

evaluating said join query only against partitions of said first table not eliminated by said automatically eliminating step.

9. The computer program product of claim 8,

wherein said partition key is a surrogate key used to reference records in said second table.

10. The computer program product of claim 8, wherein said database uses a star schema organization, wherein said first table is a fact table and said second table is a dimension table.

11. The computer program product of claim 8, wherein said step of determining whether data in said second table places an implicit limitation on the values of said partition key which satisfy said join query comprises computer-executed steps of:

constructing a reduced table from said second table using at least one logical condition of said join query; and

comparing partition key values in said reduced table to said partitions to determine, with respect to each partition, whether at least one record in said reduced table contains a partition key value corresponding to the respective partition.

12. The computer program product of claim 11, wherein said step of constructing a reduced table from said second table comprises determining whether to construct said reduced table using at least one heuristic criterion.

13. The computer program product of claim 12, wherein said at least one heuristic criterion comprises at least one criterion from the set consisting of:

(a) the size of said first table;

(b) the size of said second table;

(c) an estimated cardinality of said partition key in said reduced table; and

(d) an estimated cardinality of said partition key in said first table.

14. A computer system, comprising:

at least one processor;

a data storage for storing a database, said database containing a plurality of database tables, including a partitioned database table having a plurality of partitions, each record of said partitioned database table containing a respective partition key value;

a database management facility embodied as a plurality of instructions executable on said at least one processor, said database management facility executing queries against data in said database, at least some of said queries being join queries joining said partitioned database table and at least one other said database table;

wherein said database management facility automatically determines, with respect to each of a plurality of said join queries joining said partitioned database table and at least one other said database table, whether data in the at least one other said database table of the respective join query places a respective implicit limitation on partitions of said partitioned database table which contain records satisfying the respective join query; and

wherein, responsive to determining that data in the at least one other said database table of the respective join query places a respective implicit limitation on partitions of said partitioned database table which contain records satisfying the respective join query, said database management facility automatically evaluates the respective join query only against partitions of said partitioned database table containing one or more respective records satisfying the respective implicit limitation.

15. The computer system of claim 14,

wherein said partition key is a surrogate key used to reference records in said at least one other said database table.

16. The computer system of claim 14, wherein said database uses a star schema organization, wherein said partitioned database table is a fact table and said at least one other said database table is at least one dimension table.

17. The computer system of claim 14, wherein said database management facility comprises a query optimizer for constructing optimized query strategies for a plurality of queries, and a query engine for executing said optimized query strategies, said query optimizer determining, with respect to each of a plurality of said join queries joining said partitioned database table and at least one other said database table, whether data in the at least one other said database table of the respective join query places a respective implicit limitation on partitions of said partitioned database table which contain records satisfying the respective join query, and constructing a query execution strategy directing said query engine to evaluate the respective join query only against selective pre-determined partitions of said partitioned database table containing one or more respective records satisfying the respective implicit limitation.

18. The computer system of claim 14, wherein said database management facility determines whether data in the at least one other said database table of the respective join query places a respective implicit limitation on partitions of said partitioned database table which contain records satisfying the respective join query by constructing a reduced table from said at least one other said database table using at least one logical condition of the respective join query, and comparing partition key values in said reduced table to said partitions to determine, with respect to each partition, whether at least one record in the reduced table contains a partition key value corresponding to the respective partition.

19. The computer system of claim 18, wherein said database management facility selectively constructs a reduced table from said at least one other said database table responsive to at least one heuristic criterion.

20. The computer system of claim 19, wherein said at least one heuristic criterion comprises at least one criterion from the set consisting of:

(a) the size of said first table;

(b) the size of said second table;

(c) an estimated cardinality of said partition key in said reduced table; and

(d) an estimated cardinality of said partition key in said first table.