Method and apparatus for dynamically associating different query execution strategies with selective portions of a database table
First Claim
1. A method for executing a database query in a computer system, comprising the computer-executed steps of:
- automatically selecting one or more candidate partitions of a partitioned database table for determining separate query execution strategies to be used for evaluating said database query, said partitioned database table having multiple previously defined partitions, each partition having a common definitional structure and comprising a respective disjoint subset of records of said partitioned database table, said candidate partitions comprising at least one and fewer than all previously defined partitions of said partitioned database table, said automatically selecting one or more candidate partitions step being performed without determining whether execution of said database query can benefit from using a separate query execution strategy for evaluating said database query against any said previously defined partition;
wherein said step of automatically selecting one or more candidate partitions of a partitioned database table comprises using at least one criterion from the set of criteria consisting of;
(a) at least one parameter indicating the existence of at least one local index for a partition of said database table;
(b) at least one parameter indicating volatility of a partition of said database table; and
(c) at least one parameter indicating a relative size of a partition of said database table; and
responsive to automatically selecting one or more candidate partitions of a partitioned database table, automatically generating a respective separate query execution strategy for evaluating said database query against each candidate partition of said candidate partitions of said partitioned database table, and automatically generating a union of a respective results set of each said separate query execution strategy, to execute more efficiently than a common query execution strategy for evaluating said database query against all partitions of said partitioned database table.
1 Assignment
0 Petitions
Accused Products
Abstract
A query facility for database queries dynamically determines whether selective portions of a database table are likely to benefit from separate query execution strategies, and constructs an appropriate separate execution strategies accordingly. Preferably, the database contains at least one relatively large table comprising multiple partitions, each sharing the definitional structure of the table and containing a different respective discrete subset of the table records. The query facility compares metadata for different partitions to determine whether sufficiently large differences exist among the partitions, and in appropriate cases selects one or more partitions for separate execution strategies. Preferably, partitions are ranked for separate evaluation using a weighting formula which takes into account: (a) the number of indexes for the partition, (b) recency of change activity, and (c) the size of the partition.
-
Citations
16 Claims
-
1. A method for executing a database query in a computer system, comprising the computer-executed steps of:
-
automatically selecting one or more candidate partitions of a partitioned database table for determining separate query execution strategies to be used for evaluating said database query, said partitioned database table having multiple previously defined partitions, each partition having a common definitional structure and comprising a respective disjoint subset of records of said partitioned database table, said candidate partitions comprising at least one and fewer than all previously defined partitions of said partitioned database table, said automatically selecting one or more candidate partitions step being performed without determining whether execution of said database query can benefit from using a separate query execution strategy for evaluating said database query against any said previously defined partition; wherein said step of automatically selecting one or more candidate partitions of a partitioned database table comprises using at least one criterion from the set of criteria consisting of;
(a) at least one parameter indicating the existence of at least one local index for a partition of said database table;
(b) at least one parameter indicating volatility of a partition of said database table; and
(c) at least one parameter indicating a relative size of a partition of said database table; andresponsive to automatically selecting one or more candidate partitions of a partitioned database table, automatically generating a respective separate query execution strategy for evaluating said database query against each candidate partition of said candidate partitions of said partitioned database table, and automatically generating a union of a respective results set of each said separate query execution strategy, to execute more efficiently than a common query execution strategy for evaluating said database query against all partitions of said partitioned database table. - View Dependent Claims (2, 3, 4, 5, 6, 7)
-
-
8. A computer program product for database query optimization comprising:
-
a plurality of computer-executable instructions recorded on non-transitory computer-readable media, wherein said instructions, when executed by at least one computer system, cause the at least one computer system to perform; receiving a database query against information in a partitioned database table having multiple previously defined partitions, each said partition having a common definitional structure and comprising a respective disjoint subset of records of said partitioned database table; selecting one or more candidate partitions of said partitioned database table for determining separate query execution strategies to be used for evaluating said database query, said one or more candidate partitions comprising at least one and fewer than all said previously defined partitions of said partitioned database table, said selecting one or more candidate partitions of said partitioned database table being performed without determining a hypothetical query execution strategy for separately evaluating said database query against any said previously defined partition; wherein said selecting one or more candidate partitions of said partitioned database table comprises using at least one criterion from the set of criteria consisting of;
(a) at least one parameter indicating the existence of at least one local index for a portion of said partitioned database table;
(b) at least one parameter indicating volatility of a portion of said partitioned database table; and
(c) at least one parameter indicating a relative size of a portion of said partitioned database table; andresponsive to selecting one or more candidate partitions of said partitioned database table, generating a respective separate query execution strategy for evaluating said database query against each candidate partition of said candidate partitions of said partitioned database table, and automatically generating a union of a respective results set of each said separate query execution strategy, to execute more efficiently than a common query execution strategy for evaluating said database query against all partitions of said partitioned database table. - View Dependent Claims (9, 10, 11, 12, 13)
-
-
14. A computer system, comprising:
-
at least one processor; a data storage for storing a database, said database containing a partitioned database table having a plurality of partitions, each said partition having a common definitional structure and comprising a respective disjoint subset of records of said partitioned database table; a database management facility embodied as a plurality of instructions executable on said at least one processor, said database management facility including a query engine which executes logical queries against said database including logical queries against said partitioned database table and a query optimizer for generating execution strategies for executing logical queries against said database; wherein said database management facility automatically selects, with respect to each of a plurality of said logical queries against said partitioned database table, a respective one or more candidate partitions of said partitioned database table for determining separate query execution strategies to be used for evaluating the respective logical query, each said respective one or more candidate partitions comprising at least one and fewer than all partitions of said database table, each said respective one or more candidate partitions being selected without determining a hypothetical query execution strategy for separately evaluating the respective database query against any said partition; wherein said database management facility automatically selects a respective one or more candidate partitions by using at least one criterion from the set of criteria consisting of;
(a) at least one parameter indicating the existence of at least one local index for a partition of said partitioned database table;
(b) at least one parameter indicating volatility of a partition of said partitioned database table; and
(c) at least one parameter indicating a relative size of a partition of said partitioned database table; andwherein, responsive to selecting a respective one or more candidate partitions of said partitioned database table, said database management facility automatically generates multiple separate query execution strategies for evaluating the respective logical query, each of said multiple separate query execution strategies being used for evaluating the respective logical query against a different respective disjoint non-empty set of partitions of said partitioned database table, and automatically generates a union of a respective results set of each said separate query execution strategy, to execute more efficiently than a common query execution strategy for evaluating said database query against all partitions of said partitioned database table. - View Dependent Claims (15, 16)
-
Specification