Leverage indexes on virtual tables

The database server can quickly scan a B-tree index for qualifying entries. For each qualifying entry, the database server takes one of the following actions:
  • Invokes the access method to fetch a specific row from the base table
  • Returns the index keys

    If the index keys contain all the columns that the query projects, the database server does not need to invoke the access method.

If the query requires data from the base table, the database server can pass row identifiers to the access method. With row identifiers, the access method retrieves data by address, which eliminates the need to scan the entire base table.

To enable an index on a virtual table, provide an am_getbyid purpose function to fetch data directly from a physical address and set the am_rowids purpose flag with the CREATE ACCESS_METHOD or ALTER ACCESS_METHOD statement.

If the database server can scan an index to locate rows in a virtual table, it executes am_getbyid instead of the am_getnext purpose function. The am_getbyid purpose function calls DataBlade® API or external routines to access the row by its row identifier. Thus, if you provide am_getbyid and the appropriate index exists, the access method does not scan the table to find rows.