MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 10: Index Rowsets

Chapter 10: Index Rowsets

This chapter discusses the interfaces, use, and structure of an index rowset. An OLE DB index, also known as an index rowset, is a rowset built over an index in a data store. It is generally used in conjunction with a rowset built over a base table in the same data store. Each row of the index rowset contains a bookmark that points to a row in the base table rowset. Thus, the consumer can traverse the index rowset and use it to access rows in the base table rowset. Index structures can be either of two types: one or more columns that form a key value, or a column containing a bookmark. This chapter also includes an index example.

The index object cotype is defined as follows. For more information about cotypes, see "OLE DB Objects" in Chapter 1, "Overview of OLE DB."

CoType TIndex {
   [mandatory]   interface IAccessor;
   [mandatory]   interface IColumnsInfo;
   [mandatory]   interface IConvertType;
   [mandatory]   interface IRowset;
   [mandatory]   interface IRowsetIndex;
   [mandatory]   interface IRowsetInfo;
   [optional]    interface IRowsetChange;
   [optional]    interface IRowsetCurrentIndex;
   [optional]    interface IRowsetFind;
   [optional]    interface IRowsetIdentity;
   [optional]    interface IRowsetLocate;
   [optional]    interface IRowsetRefresh;
   [optional]    interface IRowsetScroll;
   [optional]    interface IRowsetUpdate;
   [optional]    interface IRowsetView;
   [optional]    interface ISupportErrorInfo;
};

The primary consumer for the IRowsetIndex interface is a query processor component that uses it during query execution. Although an SQL provider can expose IRowsetIndex by translating IRowsetIndex methods into SQL statements, this is not efficient. Instead, typical consumers of SQL providers should use the ICommand interface as the primary data access mechanism and rely on the query processor in the SQL provider to optimize access to data.

The primary index interface, IRowsetIndex, exposes the functionality required by file access methods based on ISAM and B+-trees. It does not support functionality such as the following: hashing, required by unordered indexes; R+-trees, required by access methods for spatial data; or signature files, required by access methods for text.

Index rowsets can be used in the following situations:

• Reading records efficiently by means of a key, which is one or more columns whose values, taken together, uniquely identify a row. The following are examples of SQL queries whose execution plans can exploit the capabilities of the IRowsetIndex interface:
  • Retrieving rows ordered by some column. For example where x is indexed:

    SELECT * FROM Table ORDER BY Table.x
    

  • Scanning records having a column value within a continuous range. For example:

    SELECT * FROM Table WHERE Table.x BETWEEN 4 AND 20
    

  • Computing joins. For example:

    SELECT * FROM R INNER JOIN S ON R.x = S.x
    

  • Retrieving distinct rows. For example, where R.x is indexed:

    SELECT DISTINCT R.x FROM R
    

  • Positioning at a particular index entry, such as skipping over key entries within a range.
• Supporting indexes with multicolumn keys. The values in each column can be in ascending or descending order.
  
  
• Traversing indexes in ascending or descending order to support ORDER BY clauses.