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.

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.

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.

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 table, for example, because of a join. To execute am_scancost before each rescan, call the mi_qual_setreopt() function. For a list of VTI accessor functions that am_scancost can call to help evaluate cost and the need to reoptimize, refer to Related Topics.

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

See the descriptions of:

• Purpose function am_stats
• Purpose flag am_scancost in Settings 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