Method and apparatus for reorganizing an active DBMS table

• US 6,122,640 A
• Filed: 09/22/1998
• Issued: 09/19/2000
• Est. Priority Date: 09/22/1998
• Status: Expired due to Term

• Current Assignee: CA, Inc. (d/b/a CA Technologies) (Broadcom, Inc.)
• Original Assignee: PLATINUM Technology IP, Inc. (Broadcom, Inc.)
• Inventors: Pereira, Hilton M.
• Primary Examiner(s): Amsbury, Wayne
• Assistant Examiner(s): PARDO, THUY N
• Application Number:
  US09/159,073
• Time in Patent Office: 728 Days
• Field of Search: 707/8, 707/100, 707/101, 707/103, 707/200, 707/201, 707/202
• US Class Current: 707/648
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

Reorganization of database tables is performed while the tables being reorganized are available for normal OLTP activity. A trigger is setup to record OLTP activity on a source table to be reorganized. The source table is momentarily locked to establish an SCN checkpoint and determine the location of data blocks allocated for the source table. A copy of the table is created (new table) and blocks untouched/modified since the timestamp are unloaded from the source table and inserted into the new table. Transactions occurring since the timestamp are performed on the new table until no more transactions since the timestamp remain in the trigger record. The new table is switched with the source table (renamed as the source table), and the original source table is dropped.

Claims

1. A method of on-line reorganization of a source table, comprising the steps of:

establishing a timestamp identifying a point in time and a state of said source table at said point in time;

making a reorganized copy of said source table by,

creating a new object for maintaining said reorganized copy, unloading data from blocks of said source table that are unmodified since said timestamp, and

loading the data into said new object;

applying transactions occurring after said timestamp to the reorganized table; and

switching said source table with the reorganized table.

2. The method according to claim 1, further comprising the steps of:

employing a trigger on said source table for logging entries of transactions on said source table in a transaction table;

obtaining a lock on said source table to prevent transactions from occurring to said source table;

deleting entries logged by said trigger prior to obtaining said lock on said source table;

unlocking said source table; and

performing said step of making a reorganized copy of said source table at said timestamp;

wherein said steps of employing a trigger and obtaining a lock are performed prior to said step of establishing a timestamp.

3. The method according to claim 2, wherein:

said step of applying transactions comprises the steps of,

obtaining an exclusive lock on said source table,

applying transactions, from said transaction table, corresponding to blocks of said source table previously unloaded, to the data loaded into said new object,

unloading data from blocks of said source table involved in transactions since said timestamp and not previously unloaded, and loading it into said new object, and

releasing said exclusive lock on said source table.

4. The method according to claim 2, wherein:

said step of applying transactions comprises the steps of,

obtaining an exclusive lock on said source table,

deleting data previously loaded into said new object and corresponding to blocks identified in said transaction table,

unloading modified data of said source table from blocks previously unloaded and identified in said transaction table, and loading the modified data into said new object,

unloading data from blocks of said source table involved in transactions since said timestamp and not previously unloaded, and loading it into said new object, and

releasing said exclusive lock on said source table.

5. The method according to claim 1, wherein said source table is a database table.

6. The method according to claim 5, wherein:

said step of creating a new object comprises creating a new database table object for maintaining said reorganized copy;

said step of unloading data comprises

reading blocks of table information from the unloaded data blocks of said source table untouched since said timestamp; and

said step of loading the data comprises,

extracting row information from the blocks read, and

storing said row information in the new database table object.

7. The method according to claim 6, wherein:

said step of creating a new database table object comprises the steps of,

retrieving a database Data Definition Language (DDL) schema for the source database table, and

creating said new database table object utilizing said DDL; and

said step of creating a reorganized copy further comprises the step of establishing an indexing system to reference locations where said row information is retrieved from the source database table and stored in said new database table object.

8. A method of on-line reorganization of a source table, comprising the steps of:

employing a trigger on said source table for logging entries of transactions on said source table in a transaction table;

obtaining a lock on said source table to prevent transactions from occurring to said source table;

deleting entries logged by said trigger prior to obtaining said lock on said source table;

establishing a timestamp identifying a point in time and a state of said source table at said point in time;

unlocking said source table;

creating a reorganized copy of said source table at said timestamp;

applying transactions occurring after said timestamp to the reorganized table; and

switching said source table with the reorganized table, wherein;

said steps of employing a trigger and obtaining a lock are performed prior to said step of establishing a timestamp; and

said step of applying transactions comprises the steps of,

obtaining an exclusive lock on said source table,

applying remaining entries in said transaction log to the reorganized table, and

determining if any additional transactions are pending as a result of said exclusive lock, and if additional transactions are pending, performing the steps of,

releasing said exclusive lock on said source table, and

repeating said steps of obtaining an exclusive lock, applying remaining entries, and determining until no more additional transactions are pending.

9. The method according to claim 8, wherein:

said step of establishing a timestamp comprises,

determining a System Change Number for use as said timestamp, said System Change Number providing a unique identifier for any transaction occurring to any one of said source table and other tables or objects maintained in conjunction with said source table.

10. A computer readable medium having computer instructions stored thereon that, when loaded into a computer, cause the computer to perform the steps of:

establishing a timestamp identifying a point in time and a state of said source table at said point in time;

making a reorganized copy of said source table by,

creating a new object for maintaining said reorganized copy,

unloading data from blocks of said source table that are unmodified since said timestamp, and

loading the data into said new object;

applying transactions occurring after said timestamp to the reorganized table; and

switching said source table with the reorganized table.

11. The computer readable medium according to claim 10, wherein said instructions stored thereon further cause the computer to perform the steps of:

employing a trigger on said source table for logging entries of transactions on said source table in a transaction table;

obtaining a lock on said source table to prevent transactions from occurring to said source table;

deleting entries logged by said trigger prior to obtaining said lock on said source table;

unlocking said source table;

performing said step of making a reorganized copy of said source table at said timestamp; and

wherein said steps of employing a trigger and obtaining a lock are performed prior to said step of establishing a timestamp.

12. The computer readable medium according to claim 11, wherein:

said step of applying transactions comprises the steps of,

obtaining an exclusive lock on said source table,

applying transactions, from said transaction table, corresponding to blocks of said source table previously unloaded, to the data loaded into said new object,

unloading data from blocks of said source table involved in transactions since said checkpoint and not previously unloaded, and loading it into said new object, and

releasing said exclusive lock on said source table.

13. The computer readable medium according to claim 11, wherein:

said step of applying transactions comprises the steps of,

obtaining an exclusive lock on said source table,

deleting data previously loaded into said new object and corresponding to blocks identified in said transaction table,

unloading modified data of said source table from blocks previously unloaded and identified in said transaction table, and loading the modified data into said new object,

unloading data from blocks of said source table involved in transactions since said timestamp and not previously unloaded, and loading it into said new object, and

releasing said exclusive lock on said source table.

14. The computer readable medium according to claim 10, wherein:

said source table is a database table; and

said step of creating a new object comprises creating a new database table object for maintaining said reorganized copy,

said step of unloading data comprises reading blocks of table information from the unloaded data blocks of said source table untouched since said timestamp, and

said step of loading the data comprises,

extracting row information from the blocks read, and

storing said row information in the new database table object.

15. The computer readable medium according to claim 14, wherein:

said step of creating a new table object comprises the steps of,

retrieving a database Data Definition Language (DDL) schema for said database table, and

creating said new table object utilizing said DDL; and

said step of creating a reorganized copy further comprises the step of establishing an indexing system to reference locations where said row information is retrieved from said database table and stored in said new table object.

16. A computer readable medium having computer instructions stored thereon that, when loaded into a computer, cause the computer to perform the steps of:

employing a trigger on said source table for logging entries of transactions on said source table in a transaction table;

obtaining a lock on said source table to prevent transactions from occurring to said source table;

deleting entries logged by said trigger prior to obtaining said lock on said source table;

establishing a timestamp identifying a point in time and a state of said source table at said point in time;

unlocking said source table;

creating a reorganized copy of said source table at said timestamp;

applying transactions occurring after said timestamp to the reorganized table; and

switching said source table with the reorganized table, wherein;

said steps of employing a trigger and obtaining a lock are preformed prior to said step of establishing a timestamp;

wherein said step of applying transactions comprises the steps of,

obtaining an exclusive lock on said source table,

applying remaining entries in said transaction log to the reorganized table, and

determining if any additional transactions are pending as a result of said exclusive lock, and if additional transactions are pending, performing the steps of,

releasing said exclusive lock on said source table, and

repeating said steps of obtaining an exclusive lock, applying remaining entries, and determining until no more additional transactions are pending.

17. The computer readable medium according to claim 16, wherein said step of establishing a timestamp comprises the step of:

determining a System Change Number for use as said timestamp, said System Change Number providing a unique identifier for any transaction occurring to any one of said source table and other tables or objects maintained in conjunction with said source table.

18. An apparatus for on-line reorganization of a source file, comprising:

a checkpoint device configured to establish an SCN checkpoint comprising a point in time and identifying a state of said source file at said point in time;

a copy mechanism configured to create a reorganized copy of said source file;

applying transactions occurring after said timestamp to the reorganized file;

switching said source file with the reorganized file;

trigger configured to log entries of transactions on said source file in a transaction table;

a locking mechanism configured to obtain a lock on said source file to prevent transactions from occurring to said source file;

a removal device configured to delete entries logged by said trigger prior to obtaining said lock on said source file; and

an unlocking mechanism configured to unlock said source file;

wherein;

said copy mechanism comprises,

a creation device configured to create a new object for maintaining said reorganized copy,

an unloading mechanism configured to unload data from blocks of said source file that are unmodified since said timestamp; and

a loading mechanism configured to load the data into said new object.

19. The apparatus according to claim 18, wherein said transaction device comprises:

a selection mechanism configured to select transactions corresponding to blocks of said source file previously unloaded, to be applied to the data loaded into said new object; and

an unload/load device configured to unload data from blocks of said source file involved in transactions since said timestamp and not previously unloaded, and load the data into said new object.

20. The apparatus according to claim 18, wherein said transaction device comprises:

a delete mechanism configured to delete data previously loaded into said new object and corresponding to blocks identified in said transaction table; and

an unload/load device configured to,

unload modified data of said source file from blocks previously unloaded and identified in said transaction table, and loading the modified data into said new object, and

unload data from blocks of said source file involved in transactions since said timestamp and not previously unloaded, and loading it into said new object.

21. The apparatus according to claim 18, wherein:

said source file is a database table;

said creation device comprises an object device configured to create a new table object for maintaining said reorganized copy;

said unloading mechanism comprises a reading mechanism configured to read blocks of table information from said database file, and

an extraction device configured to extract row information from the blocks read; and

said loading mechanism comprises a storing mechanism configured to store said row information in the new table object.

22. The apparatus according to claim 21, wherein:

said object device comprises,

a DDL replicator configured to retrieve a database Data Definition Language (DDL) schema for said database table, and

create said new table object utilizing said DDL; and

said apparatus further comprises an indexer configured to reference locations where said row information is retrieved from said database table and stored in said new table object.

23. The apparatus according to claim 22, wherein said checkpoint device further comprises:

a trigger configured to log entries of transactions on said database table in a transaction table;

a locking mechanism configured to obtain a lock on said database table to prevent transactions from occurring to said source table;

a removal device configured to delete entries logged by said trigger prior to obtaining said lock on said database table; and

an unlocking mechanism configured to unlock said database table.

29. The apparatus according to claim 22, wherein said means for reading blocks, comprises:

means for directly opening a file containing said database table; and

means for directly reading said blocks of table information from the opened file;

wherein said means for reading blocks bypasses any SQL interfaces available from a management system associated with said database table.

24. An apparatus for on-line reorganization of a source file, comprising:

a checkpoint device configured to establish an SCN checkpoint comprising a point in time and identifying a state of said source file at said point in time;

a copy mechanism configured to create a reorganized copy of said source file;

applying transactions occurring after said timestamp to the reorganized file;

switching said source file with the reorganized file;

a trigger configured to log entries of transactions on said source file in a transaction table;

a locking mechanism configured to obtain a lock on said source file to prevent transactions from occurring to said source file;

a removal device configured to delete entries logged by said trigger prior to obtaining said lock on said source file; and

an unlocking mechanism configured to unlock said source file;

wherein said transaction device comprises,

an applicator configured to apply remaining entries in said transaction log to the reorganized file;

a decision mechanism configured to determine if any additional transactions are pending as a result of said source table being locked; and

a controller configured to repeatedly release said exclusive lock on source file, obtain an exclusive lock on said source file, and apply remaining entries in said transaction log until no more additional transactions are pending via utilization of said locking mechanism, unlocking mechanism, applicator, and decision mechanism.

25. The apparatus according to claim 24, wherein said SCN checkpoint comprises a System Change Number providing a unique identifier for any transaction occurring to any one of said source file and other files or objects maintained in conjunction with said source file.

26. An apparatus for on-line reorganization of a source table, comprising:

means for employing a trigger on said source table for logging entries of transactions on said source table in a transaction table;

means for obtaining a lock on said source table to prevent transactions from occurring to said source table;

means for deleting entries logged by said trigger prior to obtaining said lock on said source table; and

means for establishing a timestamp comprising a point in time and identifying a state of said source table at said point in time;

means for creating a reorganized copy of said source table in a state just prior to said timestamp;

means for unlocking said source table;

means for applying transactions occurring after said checkpoint to the reorganized table; and

means for switching said source table with the reorganized table;

wherein said means for creating a reorganized copy of said source table comprises

means for creating a new object for maintaining said reorganized copy;

means for unloading data from blocks of said source table that are unmodified since said timestamp, and

means for loading the data into said new object.

27. The apparatus according to claim 26, further comprising means for dropping said source table after switching said reorganized table with said source table.

28. The apparatus according to claim 26, wherein:

said source table is a database table; and

said means for creating a reorganized copy comprises,

said means for creating a new object comprises means for creating anew table object for maintaining said reorganized copy;

said means for unloading comprises means for reading blocks of table information from said database table, and

means for extracting row information from the blocks read; and

said means for loading comprises means for storing said row information in the new table object.