Browse Prior Art Database

# Processing Group by and Order by Using an Index

IP.com Disclosure Number: IPCOM000037903D
Original Publication Date: 1989-Aug-01
Included in the Prior Art Database: 2005-Jan-30
Document File: 1 page(s) / 11K

IBM

## Related People

Cheng, JM: AUTHOR [+3]

## Abstract

When a relational data base query deals with large amounts of data, the ORDER BY and GROUP BY operators allow control over and reduction of the data returned. SORT is commonly used to support ORDER BY and GROUP BY. However, quite often ORDER BY and GROUP BY can also be supported by an index.

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

Page 1 of 1

Processing Group by and Order by Using an Index

When a relational data base query deals with large amounts of data, the ORDER BY and GROUP BY operators allow control over and reduction of the data returned. SORT is commonly used to support ORDER BY and GROUP BY. However, quite often ORDER BY and GROUP BY can also be supported by an index.

A method has been found for determining whether an index supports the ORDER BY or GROUP BY operators, as follows: (1) Check the key columns of the index against the columns in ORDER BY or GROUP BY, one by one, beginning with the first key column. (2) If a key column matches a column in the ORDER BY or GROUP BY operator, check the subsequent pairs of columns until the end of the operator is reached. (3) If no key column of the index matches the column in the ORDER BY or GROUP BY operator, then scan the query predicates for a Boolean predicate having an equal comparison ("=") between a key column and a constant. (4) If such a Boolean predicate is found, skip to the next key column of the index and check whether it matches the next column in the ORDER BY or GROUP BY operator by repeating step (2). (5) If all the columns in the ORDER BY or GROUP BY operator are matched by key columns of the index according to the above steps, then the index indeed supports the ORDER BY or GROUP BY operator. (6) If several such indexes exist, select the one with the cheapest cost.

Disclosed anonymously.

1