Browse Prior Art Database

An algorithm for relational database table reorganisation

IP.com Disclosure Number: IPCOM000244381D
Publication Date: 2015-Dec-08
Document File: 2 page(s) / 47K

Publishing Venue

The IP.com Prior Art Database

Abstract

Disclosed is an algorithm for determining which index to use to reorganise a relational database table.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 55% of the total text.

Page 01 of 2

An algorithm for relational database table reorganisation

Disclosed is an algorithm for determining which index to use to reorganise a relational database table.

    Relational databases are a critical component of enterprise cloud solutions. Such cloud offerings can host 1000's if instances accessed by 10,000's of users globally. Each instance can comprise 1,000's of tables in a relational database. This means that a cloud offering can comprise possibly millions of database tables. It is not possible to manually check each table in turn to determine if it is running

with optimal performance. Relational database management systems often offer tools which allow checks to be performed on the tables to see if they or the indexes associated with them need to be reorganised (REORG'd) to improve their performance in accessing the data they contain. REORGing is similar in concept defragmentation and will also include the option to order the rows of the table based upon a given order criteria. For example, a table containing address information could be ordered on the postcode and house number, or differently on the town associated with the address. The most appropriate order would be to order the table in such a way as to make the most common access method as efficient as possible as this will reduce the amount of resources required (e.g. space to perform the queries which access the table, or the time for the query to return results to the user).

    For many relational database tables REORGs are based upon their clustering index. This is an index which will automatically be used when the table is REORG'd to sort the rows of the table accordingly. For example, if the address table has clustering index which is sorted on the postcode then house number columns then when a table REORG is issued the table itself will be sorted on the postcode then house number columns.

    It is the responsibility of the database administrator (DBA) or application developer to determine the clustering index for a table. However this is not straightforward because:

    1. DBAs need to perform complex analysis on tables to determine the ideal clustering index for a table, and

    2. Application developers often are unable to determine the best clustering index for a particular table. Going back to our example, if a GUI for finding an address offers the choice to either search by postcode/house number or by town then it will not be possible to know which option will be used the most. For this reason application developers often don't define clustering indexes.

    So if a table doesn't have a clustering index then how can it be REORG'd in such a way to improve performance without requiring complex and manual interaction? How can providers of cloud service offerings ensure their database tables are in optimal condition to minimise overhead costs associated with accessing the table and reduce the time taken by end users to access the data they need?

    This article describes a method whi...