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
Calculating 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?
- 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
- Number of disk accesses to fetch the entry from the index
- Number of disk accesses to fetch the entry from the table
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
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;
}
- Use another index, if available
- Perform a sequential table scan
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.