Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
2000-11-15
2004-12-21
Robinson, Greta (Department: 2177)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000, C707S793000
Reexamination Certificate
active
06834290
ABSTRACT:
BACKGROUND OF THE INVENTION
1. Field of the Invention
This invention relates in general to the reorganization of data, and in particular to developing a cost-effective reorganization plan for reorganizing data of a database.
2. Description of the Related Art
A database is generally a collection of information organized such that computer programs can quickly access desired portions of the information. The information is typically referred to as data and the computer program enabling a user to enter, modify, delete, or otherwise organize select data in the database, is often called a database management system (DBMS). Generally, DBMSs govern the internal organization of data. For example, the terms “relational,” “network,” “flat,” “hierarchical,” and the like, all refer to ways differing DBMSs organize data. Often, these different internal structures affect how quickly and flexibly data can be extracted from a database. The foregoing notwithstanding, most modern, general-purpose moderate to high-volume databases are designed based on what is known in the art as a Relational Model, and are generally referred to as relational DBMSs. Because the instant invention is relevant to, among other things, a wide number of database platforms, including database management systems (DBMS) and relational DBMSs (RDBMS) systems, these platforms and systems will hereinafter simply be referred to as “DBMS.”
With the advent of highly sophisticated DBMSs, such as those offered from Oracle Corporation, IBM, or the like, users are demanding virtually non-interrupted access from virtually anywhere to applications and data. Moreover, users are demanding that accesses occur at peak or near peak performance levels. However, as users or applications, hereinafter referred to as “applications,” continually modify the data stored in a particular database, the performance of that database is often adversely affected. For example, as objects of a database grow and shrink in size, the database becomes fragmented, resulting in the database having a large footprint with excessive free space. Moreover, the changing objects may generate chained rows, resulting in a need for multiple disk accesses to acquire desired data. Thus, fragmentation and chaining generally increase data access times and decrease database performance. In order to maintain the accessibility and performance of a particular database, administrators often need to reorganize the data contained therein.
One solution for reorganizing data within a database involves use of storage areas reserved for DBMS use rather than operating system storage. The memory areas are generally referred to as a scratchpad area of memory or scratchpad storage. According to the solution, the data of an original database is completely copied to the scratchpad storage, then copied back to the original location of the original database. The process of copying the data allows the DBMS to remove chained rows and fragmentation from objects and to reduce fragmentation of unallocated storage in the DBMS.
The foregoing reorganization solution suffers from a variety of drawbacks. For example, in order to completely copy each object of the original database to the scratchpad storage, the DBMS needs a scratchpad storage at least as large as the original database. As the size of databases grow, the practicality and likelihood that large enough scratchpad storage will be available decreases. In addition, movement of data within a DBMS is generally very costly in terms of time. Because the foregoing reorganization solution copies data to the scratchpad storage, and then back to the original database, the amount of data that will be moved is twice the size of the original database. Similar to the foregoing, as the size of databases increase, the cost in terms of reorganization time increases at double the rate.
In addition to the foregoing drawbacks, the reorganization solution reorganizes entire databases, irrespective of whether certain objects within the database are even in need of reorganization. For example, while some objects within a database may have experienced dramatic changes in the size of the data stored therein, other objects within the database may not have had data added, modified, or deleted since allocation or the last reorganization. Therefore, some objects of the database may be in great need of reorganization, while others are not.
SUMMARY OF THE INVENTION
Based on the above, a need exists for a faster, less-costly, reorganization solution. In addition, a need exists for a reorganization solution that accounts for the varying need of reorganization among varying objects within a particular database. Accordingly, the present invention includes a reorganization planner that develops a reorganization plan by employing concepts of benefits and costs to the reorganization of objects of a database. According to another embodiment, the reorganization planner, when feasible, performs in-place reorganization of objects. According to one embodiment, in-place reorganization may advantageously reduce the cost of reorganization by at least half.
In addition, according to one embodiment of the invention, the reorganization planner develops the reorganization plan by scheduling the order of reorganization of the objects within a database according to a benefit-cost ratio. Moreover, according to additional aspects of an embodiment of the invention, the reorganization plan includes a benefit threshold, such that objects below the benefit threshold are not recommended for reorganization. The foregoing reorganization planner advantageously allows a system administrator to properly prioritize objects to be reorganized.
According to one embodiment, maintenance operations, such as, for example, reorganizing objects, often make various parts of the database inaccessible to the general user population for a given period of time. That period of time is typically referred to as the batch processing window, and often occurs at night or on the weekends. Thus, by allowing the system administrator to properly prioritize, for example, the reorganization of objects, the reorganization application advantageously achieves much greater benefits during a given batch processing window than could be accomplished through arbitrarily choosing objects to reorganize. The foregoing greater benefit translates into increased database performance (in terms of greater data throughput and faster query response time) to the general user population subsequent to the batch processing window.
Therefore, one aspect of the invention includes a method of developing a plan to reorganize a tablespace that increases the benefit derived from the reorganization while decreasing the cost associated with the reorganization. The method comprises determining an object benefit for each of multiple objects of a tablespace, where each object benefit is associated with the reorganization of one of the multiple objects. The method also includes determining an object cost for each of the multiple objects, where each object cost is associated with the reorganization of one of the multiple objects. The method also includes, for each object, generating a benefit-cost indicator which accounts for object benefit and the object cost associated with the reorganization of the object, and developing a plan for reorganizing the tablespace based on at least the benefit-cost indicator.
Another aspect of the invention includes a method of generating a plan for reorganizing a database. The method comprises determining the benefits and costs of reorganizing one or more objects within a database, and ordering the reorganization of the one or more objects based on the benefits and costs, thereby reorganizing objects having a higher benefit and a correspondingly lower cost before reorganizing objects having a lower benefit and a correspondingly higher cost.
Another aspect of the invention includes a reorganization planning system. The reorganization planning system includes one or more database files storing one or more tablespaces having one or mor
Aronoff Eyal M.
Doering Ross
Pugh Thomas
Dodds, Jr. Harold E.
Knobbe Martens Olson & Bear LLP
Quest Software, Inc.
Robinson Greta
LandOfFree
System and method for developing a cost-effective... does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with System and method for developing a cost-effective..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and System and method for developing a cost-effective... will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3313963