Optimizing Queries

To provide the optimum performance with an access method, perform the following actions:

• Provide am_scancost and am_stats purpose functions.
• Split scan processing into am_beginscan, am_getnext, am_rescan, and am_endscan purpose functions.
• Return more than one row from am_getnext or am_rescan, as Buffering Multiple Results describes.
• Register purpose functions as parallelizable, as Executing in Parallel describes.

Providing Optimizer Information

In response to a SELECT statement, the query optimizer compares the cost of alternative query paths. To determine the cost for the access method to scan the virtual index that it manages, the optimizer relies on two sources of information:

• The cost of a scan that the access method performs on its virtual index

  The am_scancost purpose function calculates and returns this cost to the optimizer. If you do not provide an am_scancost purpose function, the optimizer cannot analyze those query paths that involve a scan of data by the access method.

• The distribution statistics that the am_stats purpose function sets

  This purpose function takes the place of the type of distribution analysis that the database server performs for an UPDATE STATISTICS statement.

Splitting a Scan

The way in which you split a scan influences the ability of the access method to optimize performance during queries. You can choose to provide separate functions for each of the following purpose-function prototypes:

• am_beginscan

  In this purpose function, identify the columns to project and the strategy function to execute for each WHERE clause qualification. The database server calls the function for am_beginscan only once per query.

• am_getnext

  In this purpose function, scan through the index to find a qualifying entry and return it. The database server calls this function as often as necessary to exhaust the qualified entries in the index.

• am_rescan

  In this purpose function, reuse the information from am_beginscan and data from am_getnext to perform any subsequent scans for a join or subquery.

• am_endscan

  In this purpose function, deallocate any memory that am_beginscan allocates. The database server calls this function only once.

If you provide only an am_getnext purpose function, that one purpose function (and any UDRs that it calls) analyzes the query, scans, rescans, and performs end-of-query cleanup.