Browse Prior Art Database

Table Reorganization Identification

IP.com Disclosure Number: IPCOM000120794D
Original Publication Date: 1991-Jun-01
Included in the Prior Art Database: 2005-Apr-02
Document File: 3 page(s) / 147K

Publishing Venue

IBM

Related People

Bracht, CJ: AUTHOR [+3]

Abstract

Disclosed is a tool for OS/2* Extended Edition Database Manager users that identifies Database Tables that need to be reorganized based on various table and index statistics.

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 52% of the total text.

Table Reorganization Identification

      Disclosed is a tool for OS/2* Extended Edition Database
Manager users that identifies Database Tables that need to be
reorganized based on various table and index statistics.

      In a relational database environment, maintenance of database
tables is inevitable.  As tables are changed, their data and
structure are also changed.  These changes can contribute to
performance degradation and fragmentation of data which causes wasted
storage.  To alleviate these problems, the database tables should be
reorganized.  After tables are reorganized, the current statistics
for the tables and their indexes should be updated to provide optimal
access paths to the tables.

      Since the OS/2 Database Manager table reorganization can be a
time-consuming process and does not allow other applications to
access the database containing the table during the reorganization,
it is important to only request reorganization when it is necessary.
Therefore, a tool is provided which will interpret table and index
statistics and determine for the user which tables need to be
reorganized.

      Various formulas are used to interpret the various statistics
and determine if a table needs to be reorganized. The first formula
used is ((100*OVERFLOW/CARD)<5) where OVERFLOW is the total number of
overflow records in a table and CARD is the total number of rows in a
table. The total number of overflow rows in the table should be less
than 5% of the total number of rows. Overflow rows can be created
when rows are updated and the new rows contain more bytes of data
than the old ones (VARCHAR fields) or when columns are added to
existing tables.

      Another formula used by this tool is ((100*TSIZE)/((FPAGES-1)
*4020)>70) where TSIZE is the table size in bytes calculated from the
average column length and FPAGES is the total number of 4K pages in
the file.  The table size in bytes (TSIZE) should be more than 70% of
the total space allocated for the table (there should be less than
30% free space). Because the last page allocated usually will not be
filled, 1 is subtracted from FPAGES. This formula is only used if
FPAGES is greater than one.

      A third formula used by this tool is (((100*NPAGES)/FPAGES)>80)
where NPAGES is the total number of 4K pages on which data rows of a
table appear.  The number of pages that contain no rows at all should
be less than 20% of the total number of pages.  Pages can become
empty after rows are deleted.

      Next, the (CLUSTERRATIO>80) formula is used where CLUSTERRATIO
is the degree of data clustered with an index. The cluster ratio of
an index should be greater than 80%. Note that when multiple indexes
are defined on one table that some of the indexes will ha...