Systems and methods for reorganizing a database object

• US 7,970,748 B2
• Filed: 08/26/2010
• Issued: 06/28/2011
• Est. Priority Date: 11/15/1999
• Status: Expired due to Fees

• Current Assignee: Quest Software, Inc.
• Original Assignee: Quest Software, Inc.
• Inventors: Romine, William, Doering, Ross
• Primary Examiner(s): Le; Debbie
• Application Number:
  US12/869,138
• Publication Number:
  US 20100318503A1
• Time in Patent Office: 306 Days
• Field of Search: None
• US Class Current: 707/704
• CPC Class Codes: G06F 16/288 Entity relationship models
• CPC Class Codes: G06F 16/289 Object oriented databases

Abstract

The invention is a reorganization system having a reorganization application, a database management system, and one or more database files. The reorganization application communicates with the database management system and the one or more database files to reorganize an object, such as, for example, a table or index, while providing one or more clients substantially continuous access to the object. The reorganization application employs one or more trigger locks to synchronize the data in an original table and a reorganized table. The one or more trigger locks block select data modification operations while allowing one or more read-only operations and structural modification operations.

Claims

1. A method of reorganizing a database object while providing clients continuous access to data stored in the database object, the method comprising:

by a computer system comprising computer hardware;

reorganizing data of an original object by copying the data to a reorganized object;

applying a first partial lock to the original object, the first partial lock blocking select data modification operations against the original object while allowing other operations against the original object;

applying a second partial lock to the reorganized object, the second partial lock blocking select data modification operations against the reorganized object while allowing other operations during the reorganization such that the reorganized object remains continuously accessible during the reorganization;

substituting the reorganized object for the original object such that the reorganized object remains accessible while substituting the reorganized object for the original object.

2. The method of claim 1, additionally comprising following said substituting, removing the second partial lock, wherein additional more-restrictive locks to the original object are not needed during reorganization of the original object, thereby providing clients of the original object continuous access to the data during the reorganization through at least the other operations allowed by the first partial lock.

3. The method of claim 1, wherein the other operations allowed by at least one of the first and second partial locks comprise one or more structural modification operations.

4. The method of claim 1, wherein the other operations allowed by at least one of the first and second partial locks comprise one or more read-only operations.

5. The method of claim 1, wherein the original object includes a first object name, and wherein said substituting the reorganized object for the original object further comprises:

assigning the original object a second object name; and

assigning the reorganized object the first object name.

6. The method of claim 1, wherein said copying the data of the original object to the reorganized object further comprises removing at least one of chained rows and fragmentation from the data.

7. The method of claim 1, wherein said applying the first and second partial locks comprises generating one or more structured query language (SQL) statements.

8. A method of reorganizing an object in a database, the method comprising:

by a computer system comprising computer hardware;

reorganizing an original object by copying data from the original object to a reorganized object;

applying a first partial lock to the original object, the first partial lock blocking select data modification operations against the original object;

applying a second partial lock to the reorganized object, the second partial lock blocking data modification operations from modifying the reorganized object; and

substituting the reorganized object with the original object such that the reorganized object remains readable while substituting the reorganized object for the original object.

9. The method of claim 8, wherein the first partial lock allows one or more read-only operations.

10. The method of claim 8, wherein the first partial lock allows one or more structural modification operations.

11. The method of claim 10, wherein the one or more structural modification operations include consecutive data definition language operations.

12. The method of claim 8, additionally comprising:

during said copying, maintaining a record of modifications made to the data of the original object; and

following said copying, applying the modifications against the reorganized object.

13. The method of claim 8, wherein the original object comprises a table.

14. The method of claim 8, further comprising maintaining one or more relationships between the original object and one or more child objects during said applying the first and second partial locks and said substituting.

15. A reorganization system, comprising:

at least one database file having an object of data and a log file;

a database management system (DBMS) executing on a computing device, the DBMS communicating with the at least one database file; and

a reorganization module communicating with the DBMS to access the object and communicating with the at least one database file to access the log file, wherein the reorganization module is configured to,

copy the data of the object to a reorganized object,

apply a first partial lock to the object, thereby blocking select data modification language operations while allowing at least read-only operations against the object,

apply a second partial lock to the reorganized object, thereby blocking select data modification language operations while allowing at least read-only operations against the reorganized object, and

substitute the reorganized object for the object wherein the reorganized object remains readable when the reorganization module substitutes the reorganized object for the object.

16. The reorganization system of claim 15, wherein the object comprises a table.

17. The reorganization system of claim 15, wherein the object comprises an index.

18. The reorganization system of claim 15, wherein the reorganization module further employs multiple threads operating in parallel to copy the data and apply the first and second partial locks.

19. The reorganization system of claim 15, wherein the second partial lock further allows one or more data definition language operations.

20. The reorganization system of claim 15, wherein the reorganization module resides on the computing device.