System and Method for Automated Online Reorganization of Sequential Access Databases

• US 20130275367A1
• Filed: 04/12/2012
• Published: 10/17/2013
• Est. Priority Date: 04/12/2012
• Status: Active Grant

• Current Assignee: CA, Inc. (d/b/a CA Technologies) (Broadcom, Inc.)
• Original Assignee: Computer Associates Think Inc. (Broadcom, Inc.)
• Inventors: Shuma, Kevin P., Lynn, Joseph B., Florian, Robert
• Granted Patent: US 9,230,008 B2
• Time in Patent Office: Days
• US Class Current: 707/609
• CPC Class Codes: G06F 16/215 Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/284 Relational databases

Abstract

Various systems, non-transitory computer readable media, and methods for reorganizing data records of a database are provided. For example, a system may use an efficiency parameter that indicates a satisfactory level of organizational efficiency of at least a portion of a database table having a group of rows stored using a plurality of data blocks. The system may determine an efficiency of the group of rows based on a number of data block changes that occur during a sequential access of the group of rows. When the determined efficiency is less than the efficiency indicated by the efficiency parameter, the server may generate a control card to reorganize the group of rows when the efficiency is not satisfactory. The control card is used by an online reorganization process that reorganizes the group of rows.

Claims

1. A computer-implemented method for reorganizing data records of a database, comprising:

receiving, by a processor of a server, an efficiency parameter that indicates a satisfactory level of organizational efficiency of at least a portion of a database table having a group of rows stored using a plurality of data blocks,

wherein a level of organizational efficiency is based on a number of data block changes that occur while sequentially accessing the database table, and

wherein a data block change occurs when a current row is stored in a first data block and a next row is stored in a second data block that is different from the first data block such that accessing the next row after accessing the current row results in the data block change;

determining, by the server, a number of data block changes that occur during a sequential access of the group of rows;

determining, by the server, an efficiency associated with the group of rows based on the determined number of data block changes that occur during the sequential access of the group of rows;

comparing, by the server, the determined efficiency and the efficiency parameter;

determining, by the server, whether the determined efficiency is satisfactory based on the comparison; and

generating, by the server, a control card to reorganize the group of rows when the efficiency is not satisfactory, the control card causing a reorganization of the group of rows.

2. The computer-implemented method of claim 1, wherein generating a control card comprises generating a control card comprising a refgroup parameter, wherein the refgroup parameter indicates a level of organizational efficiency to be achieved during the reorganization of the group of rows.

3. The computer-implemented method of claim 1, wherein generating a control card comprises generating a control card comprising a first key parameter that indicates a start of the group of rows.

4. The computer-implemented method of claim 3, wherein the first key parameter comprises a key value of a first row of the group of rows.

5. The computer-implemented method of claim 3, wherein generating a control card comprises generating a control card comprising a last key parameter that indicates an end of the group of rows.

6. The computer-implemented method of claim 5, wherein the last key parameter comprises a key value of a last row of the group of rows.

7. The computer-implemented method of claim 1, wherein the group of rows comprises a subset of all the rows of the database table.

8. The computer-implemented method of claim 7, the method further comprising:

determining, by the server, a number of data block changes that occur during a sequential access of a second group of rows;

determining, by the server, a second efficiency associated with the group of rows based on the determined number of data block changes that occur during the sequential access of the second group of rows;

comparing, by the server, the determined second efficiency and the efficiency parameter; and

determining, by the server, whether the determined second efficiency is satisfactory based on the comparison,

wherein generating a control card comprises generating a control card to reorganize the group of rows and the second group of rows when the determined efficiency and the determined second efficiency are not satisfactory.

9. The computer-implemented method of claim 8, wherein generating a control card comprises generating a control card that causes a reorganization of the group of rows and the second group of rows by indicating the group of rows and the second group of rows as a single group of rows.

10. A computer-implemented system for reorganizing data records of a database, comprising:

a server comprising a processor configured to;

receive an efficiency parameter that indicates a satisfactory level of organizational efficiency of at least a portion of a database table having a group of rows stored using a plurality of data blocks,

wherein a level of organizational efficiency is based on a number of data block changes that occur while sequentially accessing the database table, and

wherein a data block change occurs when a current row is stored in a first data block and a next row is stored in a second data block that is different from the first data block such that accessing the next row after accessing the current row results in the data block change;

determine a number of data block changes that occur during a sequential access of the group of rows;

determine an efficiency associated with the group of rows based on the determined number of data block changes that occur during the sequential access of the group of rows;

compare the determined efficiency and the efficiency parameter;

determine whether the determined efficiency is satisfactory based on the comparison; and

generate a control card to reorganize the group of rows when the efficiency is not satisfactory, the control card causing a reorganization of the group of rows.

11. The computer-implemented system of claim 10, wherein the server is further configured to:

generate a control card that comprises a refgroup parameter, wherein the refgroup parameter indicates a level of organizational efficiency to be achieved during the reorganization of the group of rows.

12. The computer-implemented system of claim 10, wherein the server is further configured to generate a control card that comprises a first key parameter that indicates a start of the group of rows.

13. The computer-implemented system of claim 12, wherein the first key parameter comprises a key value of a first row of the group of rows.

14. The computer-implemented system of claim 12, wherein the server is further configured to generate a control card that comprises a last key parameter that indicates an end of the group of rows.

15. The computer-implemented system of claim 14, wherein the last key parameter comprises a key value of a last row of the group of rows.

16. The computer-implemented system of claim 10, wherein the group of rows comprises a subset of all the rows of the database table.

17. The computer-implemented system of claim 16, wherein the server is further configured to:

determine a number of data block changes that occur during a sequential access of a second group of rows;

determine a second efficiency associated with the group of rows based on the determined number of data block changes that occur during the sequential access of the second group of rows;

compare the determined second efficiency and the efficiency parameter; and

determine whether the determined second efficiency is satisfactory based on the comparison,

wherein the control card causes reorganization of the group of rows and the second group of rows when the determined efficiency and the determined second efficiency are not satisfactory.

18. The computer-implemented system of claim 17, wherein the control card causes a reorganization of the group of rows and the second group of rows by indicating the group of rows and the second group of rows as a single group of rows.

19. A non-transitory computer-readable storage medium having one or more computer-readable instructions thereon which when executed by a processor cause the processor to:

receive an efficiency parameter that indicates a satisfactory level of organizational efficiency of at least a portion of a database table having a group of rows stored using a plurality of data blocks,

wherein a level of organizational efficiency is based on a number of data block changes that occur while sequentially accessing the database table, and wherein a data block change occurs when a current row is stored in a first data block and a next row is stored in a second data block that is different from the first data block such that accessing the next row after accessing the current row results in the data block change;

determine a number of data block changes that occur during a sequential access of the group of rows;

determine an efficiency associated with the group of rows based on the determined number of data block changes that occur during the sequential access of the group of rows;

compare the determined efficiency and the efficiency parameter;

determine whether the determined efficiency is satisfactory based on the comparison; and

generate a control card to reorganize the group of rows when the efficiency is not satisfactory, the control card causing a reorganization of the group of rows.

20. The non-transitory computer-readable storage medium of claim 19, wherein the instructions further configure the processor to:

generate a control card that comprises a refgroup parameter, wherein the refgroup parameter indicates a level of organizational efficiency to be achieved during the reorganization of the group of rows.