Browse Prior Art Database

Method of Determining Indexes That Satisfy an ORDER Requirement

IP.com Disclosure Number: IPCOM000036557D
Original Publication Date: 1989-Oct-01
Included in the Prior Art Database: 2005-Jan-29
Document File: 6 page(s) / 95K

Publishing Venue

IBM

Related People

Boykin, JR: AUTHOR [+2]

Abstract

A Data Base Management System (DBMS) often needs to access records in a data base in a certain order. One of the ways to do this is to use an index, which has been created on the table. This invention is a method of searching the available indexes on a given table to find one that satisfies the given ordering requirements.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 34% of the total text.

Page 1 of 6

Method of Determining Indexes That Satisfy an ORDER Requirement

A Data Base Management System (DBMS) often needs to access records in a data base in a certain order. One of the ways to do this is to use an index, which has been created on the table. This invention is a method of searching the available indexes on a given table to find one that satisfies the given ordering requirements.

There are several query options offered by a data base management system (DBMS) that require the DBMS to put rows of a table in a certain order. For example, if a query has a request to remove duplicate values of a certain column of a table, one way to do this is to put the rows of the table in e.g. ascending order of that column, read the table, compare each value of that column with that column's value in the previous row, and eliminate the value if it is the same as the previous one.

A typical DBMS puts rows in order by one of two methods: it can sort the rows or it can use an index that already gives the required ordering. First, it would attempt to find an index that satisfies the ordering requirement. (An index is a user-defined data base object that maintains a certain order of rows. There can be zero, one, or more indexes on a table.) If one can't be found, a sort would be required.

The Structured Query Language (SQL) will be used in the discussions, although the invention could be applied to any query language that has similar features. Ordering

Requirements in SQL: Features of SQL that require ordering are as follows:

1. ORDER BY: A list of columns is given in the ORDER BY clause, each specifying whether it is to be ordered descending or ascending (the default).

2. SELECT DISTINCT: All of the rows of the result of a query are ordered (does not matter whether ascending or descending). The values of each row after the first are compared with the corresponding values of the previously returned row. Only if one or more of the columns of a given row have a different value from the corresponding previous value is the new row returned as a result row of the query.

3. GROUP BY: One or more columns are specified in a GROUP BY clause when one result row is requested per set of unique values of the columns listed. This is accomplished by ordering the input rows by the grouping columns, and comparing each new row's grouping column values with the corresponding values of the previous row. If they are all the same, the new row is part of the same group as the previous row. If not, the new row is the first row of a new group. 4. Column function

DISTINCT: Column functions produce a value from a set of values. The column functions provided by SQL are COUNT, AVG, SUM, MAX, and MIN. The keyword DISTINCT can be used with any of these functions with a column as the argument, which means that only the distinct values of the column are to be

1

Page 2 of 6

included in the calculations. To do this, the DBMS orders the columns and includes only the first in the set...