Browse Prior Art Database

Unique Index Inference

IP.com Disclosure Number: IPCOM000118711D
Original Publication Date: 1997-May-01
Included in the Prior Art Database: 2005-Apr-01
Document File: 2 page(s) / 49K

Publishing Venue

IBM

Related People

Beavin, TA: AUTHOR [+2]

Abstract

A critical function of any Database Optimizer involves selecting the most efficient index available for a particular query. Typically, this process involves evaluating each index independently. However, in some cases additional information about an index can be inferred by looking at other indexes defined on the same table. This additional information can improve the odds that the Optimizer will select the most efficient index for that query.

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

Unique Index Inference

      A critical function of any Database Optimizer involves
selecting the most efficient index available for a particular query.
Typically, this process involves evaluating each index
independently.  However, in some cases additional information about
an index can be inferred by looking at other indexes defined on the
same table.  This additional information can improve the odds that
the Optimizer will select the most efficient index for that query.

      In most cases, the most efficient index to be used to access a
particular table is the index that returns the fewest number or rows.
If the index is defined to be unique and the index is being searched
using equal conditional on all columns of that index, then it is
guaranteed that no more than one row will be returned by that index.
For example:
  CREATE UNIQUE INDEX SOMEINDEX ON SOMETABLE (COL1, COL2);
  SELECT COL3
  FROM SOMETABLE
  WHERE COL1 = 'A' AND
        COL2 = 'B';

      In this example, the most number of rows that can be returned
is one.  In most cases the Database Optimizer will choose the index
which guarantees that only one row is returned.  However, that index
is not always the most efficient index.  A second index is defined as
follows:
  CREATE UNIQUE INDEX SOMEINDEX2 ON SOMETABLE (COL1, COL2, COL3);

      This index (SOMINDEX2) is a more efficient index in this case
because it provides index-only access (i.e., all information needed
is contain...