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 index 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.

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

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?

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 .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. If retrieving an index entry does not supply the columns that the SELECT statement projects, the scan cost includes both of the following:

Number of disk accesses to fetch the entry from the index
Number of disk accesses to fetch the entry from the table

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.

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 index, for example, because of a join. To execute am_scancost before each rescan, call the mi_qual_setreopt() function.

Returning a negative cost

If the query specifies a feature that the access method does not support, return a value from am_scancost that forces the optimizer to pursue another path. In the following figure, an access method that does not process Boolean operators checks the qualification descriptor for Boolean operators and returns a negative value if it finds one.

Figure 1: Forcing a table scan

mi_real * my_scan_cost(td, qd)
   MI_AM_QUAL_DESC *qd;
   MI_AM_TABLE_DESC *td;
{......
   for (i = 0; i < mi_qual_nquals(qd); i++)
      if (mi_qual_issimple(qd, i) == MI_FALSE) /* Boolean Operator found. */
          return -1;
}

The database server might respond to a negative scan-cost value in one of the following ways:

Use another index, if available
Perform a sequential table scan

Important: The database server has no means to detect if a secondary access method does not set values for complex expressions. If an access method has no code to evaluate AND or OR, call accessor function mi_qual_boolop() or mi_qual_issimple() to determine if the qualification descriptor contains a Boolean operator.

Return values

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

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