Browse Prior Art Database

Method for Optimizing for N Rows in a Database Management System

IP.com Disclosure Number: IPCOM000117053D
Original Publication Date: 1995-Dec-01
Included in the Prior Art Database: 2005-Mar-31
Document File: 2 page(s) / 45K

Publishing Venue

IBM

Related People

Haderle, DJ: AUTHOR [+3]

Abstract

Disclosed is a new algorithm which solves performance problems relating to partial answer sets and browsing applications in data base management systems. By using the new algorithm, the data base management system will optimize for the partial answer set instead of for the full answer set, and will select the best access path to return the partial rows the fastest. For partial answer set and browsing type applications, the first record response time is greatly improved.

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

Method for Optimizing for N Rows in a Database Management System

      Disclosed is a new algorithm which solves performance problems
relating to partial answer sets and browsing applications in data
base management systems.  By using the new algorithm, the data base
management system will optimize for the partial answer set instead of
for the full answer set, and will select the best access path to
return the partial rows the fastest.  For partial answer set and
browsing type applications, the first record response time is greatly
improved.

      The disclosed new algorithm allows the application to provide
the "n" rows retrieved through an optional clause: OPTIMIZE FOR n
ROWS on SQL SELECT statements.  The application indicates to the
query optimizer that it is not interested in the entire answer set,
but only the first "n" rows.  The query optimizer will use the "n"
value in an effort to choose an access path which can get the first
"n" rows the fastest.

      Once the command OPTIMIZE FOR n ROWS is given to the query
optimizer, the query optimizer will apply appropriate cost reduction
for each candidate access path to make a better choice for the query.

      For a single table query, the query optimizer has to derive the
impact on each stage of a query access path.  For example, the Record
ID (RID) phase for the List Prefetch or a multiple index access will
not get any cost reduction, but the data phases do get cost
reduction.  The cost reducti...