The Zen database engine uses a number of optimizations. The following topics discuss how to take advantage of these in SQL statements. This technical material is meant for expert SQL users. For definitions of the terms used, see Terminology.

You will find more information about improving database performance under Performance in Advanced Operations Guide.

This topic explains one method that the database engine uses to analyze and optimize on a Restriction. For definitions of the technical terms used, see Terminology.

During SQL statement execution, the database engine attempts to convert the restriction into Modified Conjunctive Normal Form (Modified CNF). Conversion to modified CNF is a method for placing Boolean expressions in a uniform structure to facilitate restriction analysis for possible query processing optimizations. If the restriction can be converted to modified CNF, the database engine can methodically and thoroughly analyze the query for possible optimizations that make efficient use of available Indexes. If the database engine is unable to convert the restriction to modified CNF, it still analyzes the restriction for possible optimizations. In this case, however, the database engine is often unable to make use of the available indexes as effectively as it would for restrictions that either are already in modified CNF or can be converted internally to modified CNF.

The database engine is unable to convert some restrictions into modified CNF depending on the contents of the restriction. A restriction is not converted to modified CNF if any of the following conditions is true:

There are some cases in which the database engine may be capable of converting a restriction into modified CNF but will not do so. The database engine chooses not to convert a restriction to modified CNF in cases where it has determined that the restriction is more likely to benefit from optimizations that can be applied to its original form than from optimizations that could be applied after modified CNF conversion.

A restriction is not converted to modified CNF if either of the following conditions is true:

This topic provides a detailed description of the primary techniques employed by the database engine to make use of expressions in a restriction for optimization purposes. The types of optimizations performed by the database engine are described below in order from the simplest to the most complex.

A clear understanding of optimization techniques used by the database engine may aid you in structuring queries to achieve optimal performance from the database engine. In addition, by understanding how the database engine uses indexes to optimize queries, you can determine how to construct indexes that provide the best performance for a given set of typical uses.

For the sake of simplicity, the descriptions below initially address expressions that reference columns from only a single table. Optimizations making use of join conditions, in which predicates compare columns from two different tables, are described following the single table optimizations.

For definitions of the technical terms used, see Terminology.

The simplest form of Restriction optimization involves the use of a single Predicate. A predicate can be used for optimization if it meets all of the following conditions:

For example, suppose an index exists with the first segment on column c1. The following predicates can be used for optimization:

The LIKE operator is optimized only if the second operand starts with a character other than a wildcard. For example, C2 LIKE 'ABC%' can be optimized, but C2 LIKE '%ABC' will not be.

A Closed Range can be used for optimization if it satisfies all the requirements for Single Predicate Optimization.

For example, suppose an index exists with the first segment on column c1. The following closed range can be used for optimization:

A Modified Disjunct can be used for optimization if it satisfies all of the following conditions:

For example, suppose an index exists with the first segment on column c1. The following modified disjunct can be used for optimization:

The following modified disjunct cannot be used for this type of optimization because the same column is not referenced in all predicates and closed ranges:

A Conjunct can be used for optimization if it satisfies all of the following conditions:

For example, suppose an index exists with the first three segments on columns c1, c2 and c3, in that order. The following conjunct assignments can be used for optimization:

The order of the predicates does not matter. For example, the following conjunct can be used for optimization:

The following conjunct cannot be used for optimization because the second segment of the index is skipped (there is no reference to column c2):

In this case, the single predicate, c1 = 1, can still be used for optimization.

An expression in Disjunctive Normal Form (DNF) can be used for optimization if it satisfies all of the following conditions:

The database engine does not convert restrictions that are originally in DNF into modified CNF, because it can optimize on DNF.

For example, suppose an index exists with the first three segments on columns c1, c2 and c3, in that order. The following expression in DNF can be used for optimization:

The following expression in DNF cannot be used for optimization because both conjuncts do not reference the same number of segments:

An expression in Modified Conjunctive Normal Form (Modified CNF) can be used for optimization if it satisfies all of the following conditions:

Modified CNF optimization is similar to DNF optimization but allows combinations of predicates involving different comparison operations not supported by DNF optimization.

For example, suppose an index exists with the first three segments on columns c1, c2 and c3, in that order. The following expression in modified CNF can be used for optimization:

It may be easier to understand how the database engine uses this expression for optimization by looking at the equivalent expression in modified DNF:

Two Modified Disjuncts can be combined to form one or more Closed Ranges if the following conditions are satisfied:

For example, suppose an index exists with the first segment on column c1. The following expression in modified CNF can be used for optimization:

It may be easier to understand how the database engine uses this expression for optimization by looking at an equivalent expression which is simply a modified disjunct:

The simplest form of optimization involving two tables makes use of a single Join Condition. Single join condition optimization is similar to Single Predicate Optimization. A join condition can be used for optimization if it satisfies the requirements for single predicate optimization. The table that will be optimized through the use of the join condition will be processed after the other table referenced in the join condition. The table optimized through the use of the join condition uses an optimization value retrieved from a row in the other table referenced in the join condition.

For example, suppose an index exists on table t1 with the first segment on column c1. The following join conditions can be used for optimization:

During optimization, a row is retrieved from table t2. From this row, the value of column c2 is used to optimize on table t1 according to the join condition.

If, instead of an index on t1.c1, there is an index on t2.c2, then t1.c1=t2.c2 could be used to optimize on table t2. In this case, table t1 would be processed first and the value for t1.c1 would be used to optimize on table t2 according to the join condition.

In the case that there is an index on t1.c1 as well as an index on t2.c2, the database engine query optimizer examines the size of both tables as well as the characteristics of the two indexes and chooses the table to optimize that will provide the best overall query performance.

A Conjunct that consists of a mixture of join conditions and other Predicates can be used for optimization if it satisfies all of the following conditions:

The table that will be optimized through the use of the conjunct will be processed after the other table referenced.

For example, suppose an index exists on table t1 with the first three segments on columns c1, c2 and c3, in that order. The following conjuncts can be used for optimization:

An Expression in Modified Conjunctive Normal Form (Modified CNF) that contains join conditions can be used for optimization if it satisfies all the following conditions:

For example, suppose an index exists on table t1 with the first three segments on columns c1, c2 and c3, in that order. The following expressions in modified CNF can be used for optimization:

This type of optimization is exactly like Closing Open-Ended Ranges through Modified CNF Optimization except that the range being closed may be a Join Condition.

For example, suppose an index exists on table t1 with the first two segments on columns c1 and c2, in that order. The following expressions in modified CNF can be used for optimization:

A Modified Disjunct can be used for optimization through the use of more than one index if it satisfies all of the following conditions:

For example, suppose an index exists with the first segment on column c1 and another index exists with the first segment on column c2. The following modified disjunct can be used for optimization:

Push-down filters are strictly an internal optimization technique. By taking advantage of high speed filtering capabilities, the database engine can efficiently identify certain rows to be rejected from the result set depending on characteristics of the restriction. Because rows are rejected from the result set before they are returned, the database engine has to analyze fewer rows and completes the operation faster than it would without push-down filters.

The database engine can use an expression or combination of expressions as a push-down filter if the following conditions are satisfied:

For definitions of the technical terms used, see Terminology.

Indexes can optimize on query characteristics other than the Restriction, such as a DISTINCT or ORDER BY clause.

For definitions of the technical terms used, see Terminology.

An index can be used to reduce the number of rows retrieved for queries with a selection list that consists of an Aggregate Function containing the DISTINCT keyword. To be eligible for this type of optimization, the expression on which the DISTINCT keyword operates must consist of a single column reference. Furthermore, the column must be the leading segment of an index.

For example, suppose an index exists on table t1 with the first segment on column c1. The index can be used to avoid retrieving rows with duplicate values of column c1:

An index can be used to reduce the number of rows retrieved for some queries with the DISTINCT keyword preceding the selection list. To be eligible for this type of optimization, the selection list must consist only of column references (no complex expressions such as arithmetic expressions or scalar functions), and the referenced columns must be the leading segments of a single index.

For example, suppose an index exists on table t1 with the first three segments on columns c1, c2 and c3, in any order. The index can be used to avoid retrieving rows with duplicate values for the selection list items:

Whether an index can be used to optimize on an ORDER BY clause depends on the order in which the columns appear as segments in the index. Specifically, to be eligible for this type of optimization, the columns in the ORDER BY clause must make up the leading segments of an index, and the columns must appear in the ORDER BY clause in the same order as they appear as segments in the index.

In contrast, an index can be used to optimize on a DISTINCT preceding a selection list or on a GROUP BY clause as long as the selection list or GROUP BY clause consists of columns that are the leading segments of the index. This statement is true regardless of the order in which the columns appear as segments in the index.

For example, suppose an index exists on table t1 with the first three segments on columns c1, c2 and c3, in any order. The index can be used to optimize on the DISTINCT in the following queries:

The index can be used to optimize on the GROUP BY in the following queries:

For the index to be used to optimize on the ORDER BY, however, the index segments must be in the order of c2, c1, c3:

Whether an index can be used to optimize on an ORDER BY clause depends on several conditions.

Specifically, an index can be used for optimization of ORDER BY if all of the following conditions are satisfied:

In addition, an index can be used for optimization of ORDER BY if all of the following converse conditions are satisfied (note that nullable columns are allowed for ascending ORDER BY):

As well, an index can be used for optimization of ORDER BY if the ascending/descending attributes of all the involved segments are the exact opposite of each ASC or DESC keyword specified in the ORDER BY. Again, the segments defined as descending can only be used if the associated columns are not nullable.

Indexes can be used for any of the restriction optimizations, optimization on a DISTINCT, or optimization on a GROUP BY clause, regardless of the ascending/descending attribute of any of the segments.

For example, suppose an index exists on table t1 with the first two segments on columns c1 and c2, in that order, and both segments are ascending. The index can be used to optimize on the following queries:

Suppose an index exists on table t1 with the first two segments on columns c1 and c2, in that order, with the segment on c1 defined as ascending and the segment on c2 defined as descending. Suppose also that c2 is nullable. The second segment cannot be used to optimize on ORDER BY because the column is both descending and nullable. The index can be used to optimize on the following queries:

If column c2 is not nullable, then the index can also be used to optimize on the following queries:

You may take advantage of search optimization when you update a leading segment index by using the same index in the WHERE clause for the search. The database engine uses one session (client ID) for the UPDATE and another session for the search.

The following statements benefit from search optimization.

Performance improvements have been made to the implementation of temporary sort tables in this release. To process certain queries, the database engine must generate temporary tables for internal use. The performance for many of these queries has been improved.

In general, the database engine generates at least one temporary table to process a given query if any of the following conditions is true:

Under certain circumstances, upon execution of a SELECT statement, this release of the database engine attempts to prefetch to the client the first two rows of the resulting rowset. This prefetch greatly improves the performance of fetching data from result sets that consist of zero or one row.

Prefetching rows can be a costly waste of time if the result set consists of more than one row and the first data retrieval operation requests a row other than the first row in the result set, such as the last row. Therefore, prefetching is limited to a maximum of two rows with the goal of improving performance for the cases that would benefit most while avoiding cases where prefetching would not provide significant benefits.

Prefetching occurs only if Array Fetch is enabled in the advanced connection attributes for client DSNs (see Advanced Connection Attributes in ODBC Guide). Array fetching is similar to prefetching except that an array fetch does not occur until the first explicit data retrieval operation is performed. This difference exists because the first explicit data retrieval operation may provide enough information to allow the database engine to extrapolate how the rest of the result set will be retrieved. For example, if the first data retrieval operation is a call to the ODBC API SQLFetch, then the database engine can assume with complete certainty that the entire result set will be retrieved one record at a time in the forward direction only. This assumption can be made because, according to the ODBC specification, a SQLFetch entails that the rest of the result set will be retrieved via SQLFetch as well. On the other hand, if a SQLExtendedFetch call is made, and the row set size is greater than one, then the client assumes that the developer-specified rowset size is optimal, and it does not override that setting with the array fetch.

Prefetching occurs only when all of the following conditions are satisfied:

This topic provides definitions and examples to help you understand the complex technical material presented here.

An aggregate function uses a group of values in the SELECT or HAVING clause of a query to produce a single value. Aggregate functions include: COUNT, AVG, SUM, STDEV, MAX, MIN, and DISTINCT.

A closed range is a pair of Open-Ended Ranges joined by an AND operator. Both open-ended ranges must reference the same column and one must contain the < or <= comparison operator and the other must contain the >= or > comparison operator. A BETWEEN clause also defines a closed range.

For example, the following expressions are closed ranges:

A conjunct is an expression in which two or more Predicates are joined by AND operators. For example, the following Restrictions are conjuncts:

An Expression is in Conjunctive Normal Form if it contains two or more Disjuncts joined by AND operators. For example, the following expressions are in CNF:

A disjunct is an Expression in which two or more Predicates are joined by OR operators. For example, the following expressions are disjuncts:

An Expression is in disjunctive normal form if it contains two or more Conjuncts joined by OR operators. For example, the following expressions are in DNF:

An expression consists of any Boolean algebra allowed in a Restriction. An entire restriction or any part of the restriction that includes at least one or more complete Predicates is an expression.

An index is a construct associated with one or more columns in a table that allows the database engine to perform efficient searches and sorts. The database engine can make use of indexes to improve search performance by reading only specific rows that will satisfy the search conditions rather than by examining all the rows in the table. The database engine can make use of indexes to retrieve rows in the order specified by a SQL query rather than having to use inefficient techniques to order the rows after retrieving them.

A join condition is a Predicate that compares a column in one table to a column in another table using any of the comparison operators: <, <=, =, >=, >.

For example, the following predicates are join conditions:

A group of index segments are leading segments if they consist of the first n columns in an Index, where n is any number up to and including the total number of segments in the index. For example, if an index is defined with segments on columns c1, c2, and c4, then c1 is a leading segment, c1 and c2 together are leading segments, and all three together are leading segments. c2 alone is not a leading segment, because the segment c1 precedes c2 and is excluded. Columns c1 and c4 together are not leading segments, because c2 precedes c4 and is excluded.

An Expression in Modified Conjunctive Normal Form is like an expression in Conjunctive Normal Form (CNF) except that each Disjunct may contain Closed Ranges as well as Predicates.

For example, the following expressions are in Modified CNF:

A modified disjunct is like a Disjunct except that it may contain Closed Ranges as well as Predicates.

For example, the following expressions are modified disjuncts:

An open-ended range is a predicate that contains any of the following comparison operators: <, <=, >= or >. Furthermore, one of the predicate operands must consist entirely of a single column and the other operand must consist entirely of either a single column from another table or a literal.

For example, the following expressions are open-ended ranges:

A predicate is a Boolean expression that does not include any AND or OR Boolean operators (with the exception of a BETWEEN predicate).

For example, the following expressions are predicates:

The following examples are not predicates:

A restriction is defined as the entire WHERE clause of a SQL query.