Method for reorganizing a set of database partitions

• US 7,454,449 B2
• Filed: 12/20/2005
• Issued: 11/18/2008
• Est. Priority Date: 12/20/2005
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Plow, Gregory Maurice, Pourmirzaie, Farrokh E., Shiomi, Tatsuyuki
• Primary Examiner(s): Alam; Shahid Al
• Assistant Examiner(s): TRAN, ANHTAI V
• Application Number:
  US11/313,089
• Publication Number:
  US 20070143380A1
• Time in Patent Office: 1,064 Days
• Field of Search: 707/206, 715/968
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: Y10S 707/968 Partitioning
• CPC Class Codes: Y10S 707/972 Partitioning
• CPC Class Codes: Y10S 707/99953 Recoverability
• CPC Class Codes: Y10S 707/99957 Garbage collection
• CPC Class Codes: Y10S 715/968 interface for database querying and retrieval

Abstract

A method is disclosed for reorganizing a set of database partitions based on a ranked set of database partitions. The method includes a step to determine a fragmentation indicator for each database partition within a set of database partitions. A step ranks the set of database partitions according to a rank criteria. The rank criteria includes, at a minimum, the fragmentation indicator. A step reorganizes a subset of the database partitions in the ranked set of database partitions. The subset is selected, at least in part, by a rank order for the ranked set of database partitions. The subset may be selected such that ranked database partitions satisfying a reorganization threshold are included in the subset.

Claims

1. A method for reorganizing a set of database partitions, the method comprising:

determining a fragmentation indicator for each database partition within a set of database partitions;

ranking the set of database partitions according to a rank criteria comprising at least the fragmentation indicator;

defining a subset of database partitions, the database partitions satisfying a dynamic reorganization threshold;

recalculating the subset of database partitions in response to a user changing the dynamic reorganization threshold, the dynamic reorganization threshold changed in response to a user adjusting a threshold indicator by way of a user interface;

displaying by way of a user interface one or more indicators representing database partitions that are members of the recalculated subset of database partitions in response to the user adjusting the threshold indicator, and displaying updated resource requirements based on the recalculated subset of database partitions; and

reorganizing the subset of the database partitions in the ranked set of database partitions wherein the subset is defined at least in part by a rank order for the ranked set of database partitions.

2. The method of claim 1, further comprising presenting the ranked set of database partitions to a user for evaluation and prompting the user for the reorganization threshold in response to the evaluation.

3. The method of claim 1, further comprising recommending a reorganization threshold to a user.

4. The method of claim 1, wherein the reorganization threshold has a type selected from the group consisting of a number of ranked database partitions and a percentage of ranked database partitions.

5. The method of claim 1, further comprising estimating a quantity of computing resources required to reorganize the subset of database partitions.

6. The method of claim 1, further comprising estimating a performance improvement that will result from reorganizing the subset of database partitions.

7. The method of claim 1, wherein reorganizing database partitions further comprises reorganizing the subset of the database partitions in rank order by reorganizing the highest ranked database partition first and the lowest ranked database partition last.

8. The method of claim 1, wherein determining a fragmentation indicator further comprises evaluating a fragmentation function for each database partition within the set of database partitions.

9. The method of claim 8, wherein the fragmentation function comprises determining whether a database partition comprises at least a predetermined amount of free space and determining whether the database partition comprises at least a predetermined number of overflow blocks.

10. The method of claim 1, further comprising changing the subset of the database partitions by adding or removing a database partition from the subset in response to user input.

11. The method of claim 1, wherein the subset of database partitions comprises all of the database partitions in the ranked set of database partitions.

12. A method for reorganizing a set of database partitions for a client, the method comprising:

providing a reorganization utility comprising;

an evaluation module that determines a fragmentation indicator for each database partition within a set of database partitions;

a ranking module that ranks the database partitions according to a rank criteria comprising at least the fragmentation indicator;

a presentation module that presents the ranked set of database partitions to a user for evaluation, the presentation module displaying by way of a user interface one or more indicators representing database partitions that are members of a recalculated subset of database partitions, in response to the user adjusting a threshold indicator by way of the user interface, the user interface displaying updated resource requirements based on the recalculated subset of database partitions;

an input module that prompts the user for a dynamic reorganization threshold in response to the evaluation, the dynamic reorganization threshold defining a subset of database partitions, the subset of database partitions dynamically recalculated in response to a user changing the dynamic reorganization threshold, the dynamic reorganization threshold changed in response to a user adjusting the threshold indicator by way of the user interface;

a reorganization module that reorganizes a subset of database partitions in the ranked set of database partitions that satisfy the dynamic reorganization threshold;

executing the reorganization utility against a set of database partitions;

evaluating a ranked set of database partitions provided by the reorganization utility; and

providing the dynamic reorganization threshold to the reorganization utility in response to the evaluation, such that the reorganization utility reorganizes the subset of database partitions of the ranked set of database partitions.

13. The method of claim 12, wherein the reorganization utility further comprises an estimation module configured to estimate a quantity of computing resources required to reorganize database partitions in the ranked set of database partitions that satisfy the reorganization threshold and estimate a performance improvement that will result from reorganizing the database partitions in the ranked set of database partitions that satisfy the reorganization threshold.