Browse Prior Art Database

ORDER Class Detection

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

Publishing Venue

IBM

Related People

Boykin: AUTHOR [+3]

Abstract

This invention addresses the need to detect the various ordering requirements of data base queries. It does it in such a way that several ordering requirements can be satisfied in some cases with just one sort or index scan.

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

Page 1 of 3

ORDER Class Detection

This invention addresses the need to detect the various ordering requirements of data base queries. It does it in such a way that several ordering requirements can be satisfied in some cases with just one sort or index scan.

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 cannot 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 columns 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 included in the calculations. To do this, the DBMS orders the columns and

1

Page 2 of 3

includes only the first in the set of column values, and any that differ from the previous. Order Classes

To determine the sorting or index requirements, order classes are b...