The am_scancost purpose function

The query optimizer calls am_scancost during a SELECT statement, before it calls am_open.

Syntax

mi_real * am_scancost(MI_AM_TABLE_DESC *tableDesc,
   MI_AM_QUAL_DESC *qualDesc)

tableDesc: Points to the table descriptor.
qualDesc: Points to the qualification descriptor, which specifies the criteria that a table row must satisfy to qualify for retrieval.

Usage

The am_scancost purpose function estimates the cost to fetch and qualify data for the current query. The optimizer relies on the am_scancost return value to evaluate a query path for a scan that involves the access method. This function is not called for indexes on remote tables.

Important: If the access method does not have an am_scancost purpose function, the database server estimates the cost of a scan, which can diminish the optimal nature of the query plan.

For more information, see the purpose flag am_scancost in Settings purpose functions, flags, and values.

To calculate a cost, am_scancost considers the following factors:

Disk access
Add 1 to the cost for every disk access required to access the data.
Memory access
Add 0.15 to the cost for every row accessed in memory.
The cost of evaluating the qualification criteria

Compute the cost of retrieving only those table entries that qualify.

Important: Because a function cannot return an mi_real data type by value, you must allocate memory to store the scan cost value and return a pointer to that memory from the am_scancost purpose function.

Calculating cost

The following types of information influence cost:

Distribution of values across storage media
- Is the data clustered?
- Are fragments spread across different physical volumes?
- Does any one fragment contain a large or a narrow range of values for a column that the query specifies?
Information about the tables, columns, and indexes in the queried database
- Does the query contain a subquery?
- Does it require a place in memory to store aggregations?
- Does a qualification require casting or conversion of data types?
- Does the query involve multiple tables or inner joins?
- Do indexes exist for the appropriate key columns? Are keys unique?

Factoring cost

To adjust the result of am_scancost, set the am_costfactor purpose value. The database server multiplies the cost that am_scancost returns by the value of am_costfactor, which defaults to 1 if you do not set it.

Forcing reoptimization

The optimizer might need a new scan cost for subsequent scans of the same table, for example, because of a join. To execute am_scancost before each rescan, call the mi_qual_setreopt() function.

Return values

The return value is a pointer to an mi_real data type that contains the cost value.