Performance data for query optimization of database partitions
First Claim
1. A method comprising:
- determining performance data for query optimization of a query directed to a database table comprising a plurality of partitions wherein the determining further comprises determining whether the query performed partition elimination;
presenting the performance data for the query optimization in a user interface, wherein the performance data comprises information regarding a subset of the plurality of partitions;
presenting an option in the user interface to create a non-spanning index for any partition in the subset that is unexpanded, wherein the non-spanning index comprises a key that is not present in all of the plurality of partitions that are unexpanded, wherein the plurality of partitions that are unexpanded are not represented in an optimization tree that represents a join operation of a plurality of tables, wherein the query optimizer optimizes the plurality of partitions that are expanded separately from all other of the plurality of partitions, wherein the query optimizer optimizes the plurality of partitions that are unexpanded together, wherein the non-spanning index forces the query optimizer to expand the partition in the subset that is unexpanded during optimization of the query; and
presenting a recommendation in the user interface to add a range partitioning constraint to one of the plurality of partitions, wherein the range partitioning constraint maps data to the plurality of partitions based on ranges of partition key values.
5 Assignments
0 Petitions
Accused Products
Abstract
A method, apparatus, system, and signal-bearing medium that, in an embodiment, determine performance data for query optimization of a query directed to a database table having multiple partitions. The performance data for the query optimization is presented in a user interface, and an option is provided in the user interface to create a non-spanning index for any of the partitions mentioned in the performance data. In various embodiments, determining the performance data may include: determining the partitions that were unexpanded by the query optimization, determining the partitions that had a runtime estimate that was different from an actual query runtime by a threshold, determining whether any of the partitions had expansion forced by a client request, determining whether the query performed partition elimination, determining whether any of the partitions has an associated index that is unused by the query and that also has a key that is not present in all of the partitions, and determining a constraint for partition elimination.
-
Citations
11 Claims
-
1. A method comprising:
-
determining performance data for query optimization of a query directed to a database table comprising a plurality of partitions wherein the determining further comprises determining whether the query performed partition elimination; presenting the performance data for the query optimization in a user interface, wherein the performance data comprises information regarding a subset of the plurality of partitions; presenting an option in the user interface to create a non-spanning index for any partition in the subset that is unexpanded, wherein the non-spanning index comprises a key that is not present in all of the plurality of partitions that are unexpanded, wherein the plurality of partitions that are unexpanded are not represented in an optimization tree that represents a join operation of a plurality of tables, wherein the query optimizer optimizes the plurality of partitions that are expanded separately from all other of the plurality of partitions, wherein the query optimizer optimizes the plurality of partitions that are unexpanded together, wherein the non-spanning index forces the query optimizer to expand the partition in the subset that is unexpanded during optimization of the query; and presenting a recommendation in the user interface to add a range partitioning constraint to one of the plurality of partitions, wherein the range partitioning constraint maps data to the plurality of partitions based on ranges of partition key values. - View Dependent Claims (2, 3, 4, 5, 6)
-
-
7. A method for configuring a computer, comprising:
-
configuring the computer to determine performance data for query optimization of a query directed to a database table comprising a plurality of partitions, wherein the configuring the computer to determine further comprises configuring the computer to determine whether the query performed partition elimination; configuring the computer to present the performance data for the query optimization in a user interface, wherein the performance data comprises information regarding a subset of the plurality of partitions; configuring the computer to present an option in the user interface to create a non-spanning index for any partition in the subset that is unexpanded, wherein the non-spanning index comprises a key that is not present in all of the plurality of partitions that are unexpanded, wherein the plurality of partitions that are unexpanded are not represented in an optimization tree that represents a join operation of a plurality of tables, wherein a query optimizer optimizes the plurality of partitions that are expanded separately from all other of the plurality of partitions, wherein the query optimizer optimizes the plurality of partitions that are unexpanded together, wherein the non-spanning index forces the query optimizer to expand the partition in the subset that is unexpanded during optimization of the query; and presenting a recommendation in the user interface to add a range partitioning constraint to one of the plurality of partitions, wherein the range partitioning constraint maps data to the plurality of partitions based on ranges of partition key values. - View Dependent Claims (8, 9, 10, 11)
-
Specification