Interpreting the Qualification Descriptor

A qualification descriptor contains the individual qualifications that the WHERE clause specifies. A qualification, or filter, tests a value from a key against a constant value. Each branch or level of a WHERE clause specifies one of the following operations:

• A function
• A Boolean expression

The WHERE clause might include negation indicators, each of which reverses the result of a particular function.

The access method executes VII accessor functions to extract individual qualifications from a qualification descriptor. The following table lists frequently used accessor functions.

For a complete list of access functions for the qualification descriptor, refer to Qualification Descriptor.

Simple Functions

The smallest element of a qualification is a function that tests the contents of a column against a specified value. For example, in the following SELECT statement, the function tests whether the value in the lname column is the character string SMITH:

SELECT lname, fname, customer_num from customer 
WHERE lname = "SMITH"

In the preceding example, the equal operator (=) represents the function equal() and has two arguments, a column name and a string constant. The following formats apply to simple qualification functions.

Runtime Values as Arguments

The following types of arguments supply values as the function executes:

• A statement-local variable (SLV)
• A host variable

Statement-Local Variables

The parameter list of a UDR can include an OUT keyword that the UDR uses to pass information back to its caller. The following example shows a CREATE FUNCTION statement with an OUT parameter:

CREATE FUNCTION stem(column LVARCHAR, OUT y CHAR)...

In an SQL statement, the argument that corresponds to the OUT parameter is called a statement-local variable, or SLV. The SLV argument appears as a variable name and pound sign (#), as the following example shows:

SELECT...WHERE stem(lname, y # CHAR)

The VII includes functions to determine whether a qualification function includes an SLV argument and to manage its value. For more information about how the access method intercepts and sets SLVs, refer to the descriptions of the mi_qual_needoutput() function on page mi_qual_needoutput() and the mi_qual_setoutput() function on page mi_qual_setoutput().

For more information about output parameters, the OUT keyword, and SLVs, refer to IBM Informix: User-Defined Routines and Data Types Developer's Guide.

Host Variables

While a client application executes, it can calculate values and pass them to a function as an input parameter. Another name for the input parameter is host variable. In the SQL statement, a question mark (?) represents the host variable, as the following example shows:

SELECT...WHERE equal(lname, ?)

The SET parameter in the following example contains both explicit values and a host variable:

SELECT...WHERE in(SET{'Smith', 'Smythe', ?}, lname)

Because the value of a host variable applies to every entry in the index, the access method treats the host variable as a constant. However, the constant that the client application supplies might change during additional scans of the same index. The access method can request that the optimizer reevaluate the requirements of the qualification between scans.

For more information about how the access method provides for a host variable, refer to the description of mi_qual_const_depends_hostvar() and mi_qual_setreopt() in Descriptor Function Reference.

For more information about the following topics, refer to the manual indicated in the table.

Negation

The NOT operator reverses, or negates, the meaning of a qualification. In the following example, the access method returns only rows with an lname value other than SMITH:

WHERE NOT lname = "SMITH"

NOT can also reverse the result of a Boolean expression. In the next example, the access method rejects rows that have southwest or northwest in the region column:

WHERE NOT (region = "southwest" OR region = "northwest")

Complex Boolean Expressions

In a complex WHERE clause, Boolean operators combine multiple conditions. The following example combines a function with a complex qualification:

WHERE year > 95 AND (quarter = 1 OR quarter = 3)

The OR operator combines two functions, equal(quarter,1) and equal(quarter,3). If either is true, the combination is true. The AND operator combines the result of the greaterthan(year,95) with the result of the Boolean OR operator.

If a WHERE clause contains multiple conditions, the database server constructs a qualification descriptor that contains multiple, nested qualification descriptors.

Figure 11 shows a complex WHERE clause that contains multiple levels of qualifications. At each level, a Boolean operator combines results from two previous qualifications.

WHERE region = "southwest" AND 
   (balance < 90 OR aged <= 30) 

Figure 12 and Figure 13 represent the structure of the qualification descriptor that corresponds to the WHERE clause in Figure 11.

AND(equal(region,'southwest'), 
   OR(lessthan(balance,90), lessthanequal(aged,30)))

The qualification descriptors for the preceding expression have a hierarchical relationship, as the following figure shows.

Figure 13. Qualification- Descriptor Hierarchy for a Three-Key Index

For a detailed description of the functions that the access method uses to extract the WHERE clause conditions from the qualification descriptor, refer to Qualification Descriptor.

Qualifying Data

To qualify table rows, a secondary access method applies the functions and Boolean operators from the qualification descriptor to key columns. The access method actually retrieves the contents of the keys from an index rather than from the table. If the index keys qualify, the secondary access method returns identifiers that enable the database server to locate the whole row that includes those key values.

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 routine identifier to execute a UDR with the DataBlade API FastPath facility. A qualification specifies a strategy UDR to evaluate index keys. To complete the qualification, the access method might also execute support UDRs. For information about FastPath and how to use it to execute strategy and support UDRs, refer to Using FastPath.

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 the 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. */ }

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.

Processing Complex Qualifications

In Figure 14, the am_getnext purpose function attempts to disqualify index keys. It sets the row identifier and fragment identifier in the row-ID descriptor and signals the database server to retrieve the row information.

mi_integer sample_getnext(sd,retrow,retrowid)
   MI_AM_SCAN_DESC   *sd;
   MI_ROW                    **retrow
   MI_AM_ROWID_DESC  *retrowid;    /* Store rowid. */
{
   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 key column data types. */
   my_data = (my_data_t *)mi_tab_userdata(td); /* Get pointer to user
data.*/
   /* Evaluate keys until one qualifies for return to caller.. */
   for (;;)
   {
      if ( ! my_data ) return MI_NO_MORE_RESULTS;
      if ( eval_qual(sd, qd, my_data))== MI_TRUE)
      {
         mi_id_setrowid(retrowid, current->rowid);
         mi_id_setfragid(retrowid, current->fragid);
         return MI_ROWS;
      }

      my_data->rowptr++;
   } /*End loop.*/
}/* End getnext.*/

For more examples, see the indexing information 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 of 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 page 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. For an example, refer to Figure 31.

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 indexes; the access method updates data-distribution statistics for virtual indexes. When a user issues an UPDATE STATISTICS statement that requires the access method to determine the distribution of data in an index, 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_istats_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.

The database server does not use the information in the statistics descriptor to evaluate query costs. The access method can, however, use these statistics during the am_scancost purpose function to compute the cost for a given query. 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.