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 rowindex key must satisfy to qualify for retrieval.

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.

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

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. To find out how to set purpose values, refer to SQL Statements for Access Methods.

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 Figure 31, 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.

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

This function returns a pointer to an mi_real data type that contains the cost value.

See the descriptions of:

• Purpose functions am_stats and am_getnext
• Purpose flags am_scancost and am_rowids in Setting Purpose Functions, Flags, and Values
• Accessor functions mi_qual_const_depends_hostvar(), mi_qual_constisnull_nohostvar(),mi_qual_constant_nohostvar() , mi_qual_boolop(), mi_qual_issimple(), and mi_qual_setreopt() in Descriptor Function Reference
• DataBlade API function mi_row_create() in the IBM Informix: DataBlade API Programmer's Guide