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 row 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 VTI accessor functions to extract individual qualifications from a qualification descriptor. The following table lists frequently used accessor functions.

For a complete list of accessor functions for the qualification descriptor, refer to Descriptor Function Reference.

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 VTI 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 mi_qual_needoutput() and mi_qual_setoutput().

For more information about output parameters, the OUT keyword, and SLVs, refer to the 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 row in the table, 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 table. 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.

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 8 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 9 and Figure 10 represent the structure of the qualification descriptor that corresponds to the WHERE clause in Figure 8.

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 10. Qualification- Descriptor Hierarchy

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