Performance data for query optimization of database partitions

• US 20060271504A1
• Filed: 05/26/2005
• Published: 11/30/2006
• Est. Priority Date: 05/26/2005
• Status: Active Grant

• Current Assignee: Twitter Inc (X Holdings Corp.)
• Original Assignee: International Business Machines Corporation
• Inventors: Bestgen, Robert Joseph, Kethireddy, Shantan, Tenner, Jeffrey Wayne, Anderson, Mark John
• Granted Patent: US 7,734,615 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures

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.

Claims

1. A method comprising:

determining performance data for query optimization of a query directed to a database table comprising a plurality of partitions;

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; and

presenting an option in the user interface to create a non-spanning index for any partition in the subset.

2. The method of claim 1, wherein the determining further comprises:

determining whether any of the plurality of partitions was unexpanded by the query optimization.

3. The method of claim 1, wherein the determining further comprises:

determining whether any of the plurality of partitions had a runtime estimate that was different from an actual query runtime by a threshold.

4. The method of claim 1, wherein the determining further comprises:

determining whether any of the plurality of partitions had expansion forced by a client request.

5. The method of claim 1, wherein the determining further comprises:

determining whether the query performed partition elimination.

6. The method of claim 1, wherein the determining further comprises:

determining whether any of the plurality of 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 plurality of partitions.

7. The method of claim 1, wherein the determining further comprises:

determining a constraint for partition elimination.

8. A signal-bearing medium encoded with instructions, wherein the instructions when executed comprise:

determining performance data for query optimization of a query directed to a database table comprising a plurality of partitions;

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; and

presenting an option in the user interface to create a non-spanning index for any partition in the subset.

9. The signal-bearing medium of claim 8, wherein the determining further comprises:

determining whether any of the plurality of partitions was unexpanded by the query optimization.

10. The signal-bearing medium of claim 8, wherein the determining further comprises:

determining whether any of the plurality of partitions had a runtime estimate that was different from an actual query runtime by a threshold.

11. The signal-bearing medium of claim 8, wherein the determining further comprises:

determining whether any of the plurality of partitions had expansion forced by a client request.

12. The signal-bearing medium of claim 8, wherein the determining further comprises:

determining whether the query performed partition elimination.

13. The signal-bearing medium of claim 8, wherein the determining further comprises:

determining whether any of the plurality of 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 plurality of partitions.

14. The signal-bearing medium of claim 8, wherein the determining further comprises:

determining a constraint for partition elimination.

15. 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;

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; and

configuring the computer to present an option in the user interface to create a non-spanning index for any partition in the subset.

16. The method of claim 15, wherein the configuring the computer to determine further comprises:

configuring the computer to determine whether any of the plurality of partitions was unexpanded by the query optimization.

17. The method of claim 15, wherein the configuring the computer to determine further comprises:

configuring the computer to determine whether any of the plurality of partitions had a runtime estimate that was different from an actual query runtime by a threshold.

18. The method of claim 15, wherein the configuring the computer to determine further comprises:

configuring the computer to determine whether any of the plurality of partitions had expansion forced by a client request.

19. The method of claim 15, wherein the configuring the computer to determine further comprises:

configuring the computer to determine whether the query performed partition elimination.

20. The method of claim 15, wherein the configuring the computer to determine further comprises:

configuring the computer to determine whether any of the plurality of 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 plurality of partitions; and

configuring the computer to determine a constraint for partition elimination.