Qualifying Data

An access method can do one or more of the following to qualify or disqualify each source record or row:

• Pass the row to database server for evaluation
• Evaluate the source data inside the access-method
• Send part or all of the query to external software

Qualification by the Database Server

The optimizer does not create a qualification descriptor if the cost for the access method to qualify rows exceeds the cost for a full table scan. If the database server does not construct a qualification descriptor, the mi_scan_quals() function returns a NULL-valued pointer.

Qualification by the Access Method

An access method might perform all the qualification tests or it might examine some of the values that a WHERE clause specifies to partially qualify rows.

Guidelines for Implementation

An access method might create a row from each source record and pass the row to the database server for evaluation. However, each call to mi_row_create() to format a row or to mi_eval_am_qual() to have the database server evaluate the row can reduce performance. A developer might use this simple approach for low-volume data.

If possible, an access method evaluates the entire WHERE clause to eliminate unqualified source records. For each candidate record that it cannot disqualify, the access method calls mi_row_create() and mi_eval_am_qual() functions, which causes the database server to fill in any missing results in the qualification descriptor. For an example of this approach, refer to Processing Complex Qualifications.

Ideally, the access method only formats values that the query projects and fills the remaining columns with NULL values. To determine which columns contain the values that the query requires, the access method calls the mi_scan_nprojs() and mi_scan_projs() functions.

Executing Qualification Functions

This section describes the following alternative ways to process a simple function:

• To execute a function in a database server thread, use the routine identifier.
• To enable the access method or external software to execute an equivalent function, use the function name.

Using the Routine Identifier

The access method uses a DataBlade API facility called FastPath to execute registered UDRs that do not reside in the same shared-object module as the access-method functions. To use the FastPath facility, the access method performs the following general steps:

1. Calls the mi_qual_funcid() accessor function to obtain the routine identifier
2. Passes the routine identifier to the DataBlade API mi_func_desc_by_typeid() function, which returns the function descriptor
3. Passes the function descriptor to the DataBlade API mi_routine_exec() function

For complete information about FastPath functions and the function descriptor (MI_FUNC_DESC), see the IBM Informix: DataBlade API Programmer's Guide.

Using the Function Name

To extract the function name from the qualification descriptor, the access method calls the mi_qual_funcname() accessor function.

You can use mi_qual_funcname() to identify the function in a qualification, then directly call a local routine that implements it. For example, if an access method contains a local equal() function, it might include the following condition:

/* Compare function name to string.*/
if (strcmp("equal", mi_qual_funcname(qd)) == 0)
{ /* Execute equal() locally. */ }

An access method can also use the mi_qual_funcid() function if external software controls the data. The access method uses this and other accessor functions to extract information from the qualification descriptor into a form that the external software can interpret. For a demonstration access method that parses and passes a qualification to external software, choose the DataBlade Corner from the list box on the IBM Informix Developer Zone at www.ibm.com/software/data/developer/informix.

Processing Complex Qualifications

In Figure 11, the am_getnext purpose function attempts to disqualify source records. It creates rows for fully qualified source records and for those that it cannot disqualify.

mi_integer sample_getnext(sd,retrow,retrowid)
   MI_AM_SCAN_DESC *sd;
   MI_ROW             **retrow
   mi_integer         retrowid; 
{
   my_data_t    *my_data;
   MI_ROW_DESC *rd;
   MI_AM_TABLE_DESC *td;
   MI_AM_QUAL_DESC  *qd;
   td = mi_scan_table(sd); /* Get table descriptor. */
   rd = mi_tab_rowdesc(td); /* Get column data types. */
   my_data = (my_data_t *)mi_tab_userdata(td); /* Get pointer to user data.*/
   MI_DATUM    qdvalue;
   /* Evaluate records until one qualifies for return to caller.. */
   for (;;)
   {
      /* Test for and exit if end of data. (more_rows() routine not shown.)*/ 
      if (more_rows(my_data) !=MI_OK) 
         return MI_NO_MORE_RESULTS;
      /* User data contains more rows, so evaluate the next one */
      get_results(qd, my_data);
      qdvalue = mi_qual_value(qd)
      if (qdvalue == MI_VALUE_TRUE)
      {
         /*Create MI_ROW and return it to the database server. */
         *retrow = mi_row_create(...);
         return MI_ROWS;
      }
      else if (qdvalue == MI_VALUE_NOT_EVALUATED)
       {
         /*Create MI_ROW and return it to the database server. */
         *retrow = mi_row_create(...);
         if (mi_eval_am_qual(retrow, qd) == MI_VALUE_TRUE)
            return MI_ROWS;
      }
      /* Either get_result() or mi_eval_am_qual() returned MI_VALUE_FALSE. */
      mi_init_am_qual(qd); /* Reset qualification descriptor */
      my_data->rowptr++;
   } /*End loop.*/
}/* End getnext.*/

In Figure 12, the get_result() function loops recursively through the qualification descriptor, looking for simple qualifications that the access method knows how to evaluate. It sets results for the simple qualifications and leaves MI_VALUE_NOT_EVALUATED in the Boolean-operator portions of the qualification descriptor.

  

... get_result(qd, my_data)
   MI_AM_QUAL_DESC   *qd;
   user_data_t                     *my_data
{
   if (mi_qual_issimple(qd))  
   { 
      /* Execute simple, function. (Not shown.) */
      /* Test the result that the function returns. */
      if (result == MI_TRUE) 
      {
         /* Set result in qualification descriptor.*/
         mi_qual_setvalue(qd,MI_VALUE_TRUE);
         return; ;
      }
      else 
      {
         mi_qual_setvalue( qd,MI_VALUE_FALSE);
         return;;
      } 
   } /* END: if (mi_qual_issimple(qd)) */
   else
   { /* Complex qualification (has AND or OR)..Loop until all functions execute.*/
      for (i = 0; i < mi_qual_nquals(qd); i++) 
         get_result(mi_qual_qual(qd, i), my_data)
   } /* END: Complex qualification (has AND or OR) */
   return;;

Qualification by External Software

If required, an access method can pass a qualification to external software. To exchange information with external software, the access method must manage communication. To obtain a demonstration access method that communicates with external software, choose the DataBlade Corner from the list box on the IBM Informix Developer Zone at www.ibm.com/software/data/developer/informix.

Supporting Query Plan Evaluation

At the start of a SELECT statement, the database server initiates query planning. A query plan specifies the steps that the database server takes to fulfill a query with optimal efficiency. The database server includes an optimizer, which compares various combinations of operations and chooses the query plan from among alternative approaches. To help the optimizer select the best query plan, provide reliable information about the cost for using the access method to select data.

Calculating Statement-Specific Costs

The optimizer compares the cost in time and memory to perform such tasks as the following:

• Locating an index entry or table row on disk
• Retrieving the entry or row into memory
• Sorting and joining data
• Applying WHERE clause qualifications
• Retrieving rows from a primary table, if the optimizer uses an index

For more information about query plans, refer to the IBM Informix: Performance Guide.

If the query involves a user-defined access method, the database server executes the am_scancost purpose function to request cost information from the access method. For a description of the factors that am_scancost calculates, refer to am_scancost.

To avoid error messages, the access method can use the am_scancost purpose function to notify the optimizer when it does not support all the requirements specified in a query. If necessary, am_scancost can return a negative cost so that the optimizer excludes this access method from the query plan.

Updating Statistics

The UPDATE STATISTICS statement stores statistics about the distribution of rows on physical storage media for use by the optimizer. The database server updates data-distribution statistics for internal, relational tables; the access method updates data-distribution statistics for virtual tables. When a user issues an UPDATE STATISTICS statement that requires the access method to determine the distribution of data in a table, the database server calls the am_stats purpose function.

The access method can call mi_tab_update_stat_mode() to determine if the UPDATE STATISTICS statement includes the keyword HIGH or MEDIUM, each of which influences the percentage of rows that the access method should sample and the particular statistics that it should supply.

To store statistics in the statistics descriptor, the am_stats purpose function calls the various accessor functions with the name prefix mi_tstats_set. The database server copies the information from the statistics descriptor in the appropriate system catalog tables. For information about these functions, refer to Descriptor Function Reference.

For information about how to access the system catalog tables or to maintain tables in an IBM Informix database, refer to Accessing Database and System Catalog Tables. For information about the effects of query costs and distribution of data, refer to the IBM Informix: Performance Guide.