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.