Performance Reference
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.
Restriction Analysis
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.
Modified CNF Conversion
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.
Restrictions that Cannot be Converted
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:
The restriction contains a subquery.
The restriction contains a NOT operator.
The restriction contains a dynamic parameter (a dynamic parameter is a question mark ("?") in the SQL statement, which will be prompted for when the statement is executed).
Conditions Under Which Conversion is Avoided
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:
The restriction is in Disjunctive Normal Form (DNF) and all Predicates involve only the equal (=), LIKE or IN comparison operators.
For example, the database engine does not convert the following restriction to modified CNF:
(c1 = 1 AND c2 = 1) OR (c1 = 1 AND c2 = 2) OR (c1 = 2)
The restriction meets all of the following conditions:
It contains an expression in Disjunctive Normal Form (DNF) that is AND connected to the rest of the restriction.
The specified DNF expression contains only Predicates that involve the equal (=), LIKE or IN comparison operator.
The predicates in identical positions in each Conjunct in the DNF expression reference the same column.
For example, a Restriction that contains the following Expression will not be converted to modified CNF:
(c1 = 1 AND c2 = 1) OR (c1 = 1 AND c2 = 2)
Restriction Optimization
This section 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.
Single Predicate Optimization
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:
The predicate is joined to the rest of the restriction by the AND operator.
One operand of the predicate consists of a column reference which is a leading segment of an index and the other operand consists of an expression that does not contain a column reference (that is, the other operand contains only a literal value or dynamic parameter).
The comparison operator is one of: <, <=, =, >=, >, LIKE, or IN.
For example, suppose an index exists with the first segment on column c1. The following predicates can be used for optimization:
c1 = 1
c1 IN (1,2)
c1 > 1
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.
Closed Range Optimization
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:
c1 >= 1 AND c1 < 10
Modified Disjunct Optimization
A Modified Disjunct can be used for optimization if it satisfies all of the following conditions:
It is joined to the rest of the Restriction by the AND operator.
Each Predicate and Closed Range in the disjunct satisfies the requirements for Single Predicate Optimization and Closed Range Optimization.
Each predicate or closed range references the same column as the others.
For example, suppose an index exists with the first segment on column c1. The following modified disjunct can be used for optimization:
c1 = 1 OR (c1 > 5 AND c1 < 10) OR c1 > 20
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:
c1 = 1 OR (c1 > 5 AND c1 < 10) OR c2 = 1
Conjunct Optimization
A Conjunct can be used for optimization if it satisfies all of the following conditions:
It is joined to the rest of the restriction by the AND operator.
Each Predicate in the conjunct satisfies the requirements for Single Predicate Optimization.
Each predicate optimizes on the leading segments of an index with only one predicate for each leading segment (that is, there are not two different predicates that use the same set of leading segments).
All predicates, except for the predicate referencing the last segment used for optimization, use the equal (=) comparison operator.
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:
c1 = 1 AND c2 = 1 AND c3 = 1
c1 = 1 AND c2 = 1 AND c3 >= 1
c1 = 1 AND c2 > 1
The order of the predicates does not matter. For example, the following conjunct can be used for optimization:
c2 = 1 AND c3 = 1 AND c1 = 1
The following conjunct cannot be used for optimization because the second segment of the index is skipped (there is no reference to column c2):
c1 = 1 AND c3 = 1
In this case, the single predicate, c1 = 1, can still be used for optimization.
Disjunctive Normal Form Optimization
An expression in Disjunctive Normal Form (DNF) can be used for optimization if it satisfies all of the following conditions:
It is joined to the rest of the restriction by the AND operator.
Each conjunct in the expression satisfies the requirements for Conjunct Optimization with the additional limitation that all the predicates must contain the equal (=) comparison operator.
All the conjuncts must use the same index and the same number of segments for optimization.
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:
(c1 = 1 AND c2 = 1 AND c3 = 1) OR (c1 = 1 AND c2 = 1 AND c3 = 2) OR (c1 = 2 AND c2 = 2 AND c3 = 2)
The following expression in DNF cannot be used for optimization because both conjuncts do not reference the same number of segments:
(c1 = 1 AND c2 = 1 AND c3 = 1) OR (c1 = 1 AND c2 = 2)
Modified Conjunctive Normal Form Optimization
An expression in Modified Conjunctive Normal Form (Modified CNF) can be used for optimization if it satisfies all of the following conditions:
It is joined to the rest of the restriction by the AND operator.
Each Modified Disjunct satisfies the requirements for Modified Disjunct Optimization except that each modified disjunct must reference a different index segment which together make up the Leading Segments (that is, taking all the disjuncts together, no segments can be skipped).
All the modified disjuncts except for the one that references the last segment must contain at least one predicate that contains the equals (=) comparison operator.
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:
(c1 = 1 OR c1 = 2) AND (c2 = 1 OR (c2 > 2 AND c2 < 5)) AND (c3 > 1)
It may be easier to understand how the database engine uses this expression for optimization by looking at the equivalent expression in modified DNF:
(c1 = 1 AND c2 = 1 AND c3 > 1) OR (c1 = 1 AND (c2 > 2 AND c2 < 5) AND c3 > 1) OR (c1 = 2 AND c2 = 1 AND c3 > 1) OR (c1 = 2 AND (c2 > 2 AND c2 < 5) AND c3 > 1)
Closing Open-Ended Ranges through Modified CNF Optimization
Two Modified Disjuncts can be combined to form one or more Closed Ranges if the following conditions are satisfied:
Both modified disjuncts satisfy the requirements for Modified Disjunct Optimization.
Both modified disjuncts use the same segment in the same index.
Both modified disjuncts contain open-ended ranges that can be combined to form one or more closed ranges.
For example, suppose an index exists with the first segment on column c1. The following expression in modified CNF can be used for optimization:
(c1 = 1 OR c1 > 2) AND (c1 < 5 OR c1 = 10)
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:
c1 = 1 OR (c1 > 2 AND c1 < 5) OR c1 = 10
Single Join Condition Optimization
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:
t1.c1 = t2.c2
t1.c1 > t2.c2
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.
Conjunct with Join Conditions Optimization
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:
All the join conditions compare columns from the same two tables.
The conjunct satisfies the requirements for regular Conjunct Optimization for one of the two tables.
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:
t1.c1 = t2.c1 AND t1.c2 = t2.c2 AND t1.c3 = t2.c3
t1.c1 = t2.c1 AND t1.c2 > t2.c2
t1.c1 = t2.c1 AND t1.c2 = 1
t1.c1 = 1 AND t1.c2 = t2.c2
Modified Conjunctive Normal Form with Join Conditions 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:
It satisfies the conditions for Modified Conjunctive Normal Form Optimization.
In addition, all disjuncts but the disjunct optimizing on the last portion of the leading segment being used must contain only a single join condition or a single predicate and at least one of these is a single join condition.
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:
(t1.c1 = t2.c1) AND (t1.c2 = t2.c2 OR
(t1.c2 > 2 AND t1.c2 < 5))
 
(t1.c1 = 1) AND (t1.c2 = t2.c2) AND
(t1.c3 > 2 AND t1.c3 < 5)
Closing Join Condition Open-Ended Ranges through Modified CNF 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:
(t1.c1 > t2.c1) AND (t1.c1 < t2.c2 OR t1.c1 = 10)
 
(t1.c1 = t2.c1) AND (t1.c2 > t2.c2) AND (t1.c2 < 10 OR t1.c2 = 100)
Multi-Index Modified Disjunct 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:
Is joined to the rest of the restriction by the AND operator.
Each Predicate and Closed Range in the disjunct satisfies the requirements for Single Predicate Optimization or Closed Range Optimization, respectively.
Each predicate or closed range references a column that is the first segment in an index. If all predicates and closed ranges reference the same column, then this scenario is simply Modified Disjunct Optimization, as described previously.
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:
c1 = 1 OR (c1 > 5 AND c1 < 10) OR c2 = 1
Push-Down Filters
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:
A Predicate can be used in a push-down filter if it is joined to the rest of the Restriction by the AND operator.
A Predicate can be used in a push-down filter if one operand consists of a column reference and the other operand consists of either a literal value or a dynamic parameter ("?"). Also, the referenced column must not be of one of the following data types: bit, float, double, real, longvarchar, longvarbinary, or binary.
A Predicate can be used in a push-down filter if the comparison operator is one of the following: <, <=, =, >=, >, or <>.
A Disjunct can be used in a push-down filter if it is joined to the rest of the restriction by the AND operator and all the predicates within the disjunct satisfy the requirements for a predicate to be used in a push-down filter, except for the condition that the predicate must be joined to the rest of the restriction by an AND operator. Only one disjunct may be included in the push-down filter.
A push-down filter may combine a single disjunct with other predicates that satisfy the requirements for a predicate to be used in a push-down filter.
For definitions of the technical terms used, see Terminology.
Efficient Use of Indexes
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.
DISTINCT in Aggregate Functions
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:
SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 = 1
DISTINCT Preceding Selection List
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:
SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c2 = 1
Relaxed Index Segment Order Sensitivity
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:
SELECT DISTINCT c1, c2, c3 FROM t1
SELECT DISTINCT c2, c3, c1 FROM t1 WHERE c3 > 1
The index can be used to optimize on the GROUP BY in the following queries:
SELECT c1, c2, c3, count(*) FROM t1 GROUP BY c2, c1, c3
SELECT c2, c3, c1, count(*) FROM t1 GROUP BY c3, c2, c1
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:
SELECT c1, c2, c3 FROM t1 ORDER BY c2, c1, c3
Relaxed Segment Ascending Attribute Sensitivity
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:
The DESC keyword follows the column in the ORDER BY clause.
The corresponding index segment is defined as descending.
The specified column is not nullable.
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):
The ASC keyword or neither ASC nor DESC follows the column in the ORDER BY statement.
The corresponding index segment is defined as ascending.
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:
SELECT c1, c2, c3 FROM t1 ORDER BY c1, c2
SELECT c1, c2, c3 FROM t1 ORDER BY c1 DESC, c2 DESC
SELECT DISTINCT c1, c2 FROM t1
SELECT DISTINCT c2, c1 FROM t1
SELECT * FROM t1 WHERE c1 = 1
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:
SELECT c1, c2, c3 FROM t1 ORDER BY c1
SELECT c1, c2, c3 FROM t1 ORDER BY c1 DESC
SELECT DISTINCT c1, c2 FROM t1
SELECT DISTINCT c2, c1 FROM t1
SELECT * FROM t1 WHERE c1 = 1
If column c2 is not nullable, then the index can also be used to optimize on the following queries:
SELECT c1, c2, c3 FROM t1 ORDER BY c1, c2 DESC
SELECT c1, c2, c3 FROM t1 ORDER BY c1 DESC, c2
Search Update Optimization
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.
CREATE TABLE t1 (c1 INT)
CREATE INDEX t1_c1 ON t1(c1)
INSERT INTO t1 VALUES(1)
INSERT INTO t1 VALUES(1)
INSERT INTO t1 VALUES(9)
INSERT INTO t1 VALUES(10)
INSERT INTO t1 VALUES(10)
UPDATE t1 SET c1 = 2 WHERE c1 = 10
UPDATE t1 SET c1 = c1 + 1 WHERE c1 >= 1
Temporary Table Performance
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:
The DISTINCT keyword precedes the selection list and the items in the selection list are not columns that are the leading segments of an index.
For example, a temporary table is generated to process the following query unless an index exists with columns c1 and c2 as leading segments:
SELECT DISTINCT c1, c2 FROM t1
A GROUP BY clause is used, and the items in the GROUP BY clause are not columns that are the leading segments of an index.
For example, a temporary table is generated to process the following query unless an index exists with columns c1 and c2 as leading segments:
SELECT c1, c2, COUNT(*) FROM t1 GROUP BY c1, c2
A static cursor is being used.
For example, a temporary table is generated if an application calls the ODBC API SQLSetStmtOption specifying the SQL_CURSOR_TYPE option and the SQL_CURSOR_STATIC value prior to creation of the result set.
The result set includes bookmarks.
For example, if the ODBC API SQLSetStmtOption is called specifying the SQL_USE_BOOKMARKS option and the SQL_UB_ON value prior to generating the result set.
A query contains a non-correlated subquery to the right of the IN or =ANY keywords.
For example:
SELECT c1 FROM t1 WHERE c2 IN (SELECT c2 FROM t2)
Row Prefetch
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:
Array fetch is enabled.
The result set does not include large variable length data. For example, the selection list does not contain a column of type LONGVARCHAR or LONGVARBINARY.
The result set does not include bookmarks.
For example, prefetching does not occur if the ODBC API SQLSetStmtOption is called prior to generating the result set, specifying the SQL_USE_BOOKMARKS option and the SQL_UB_ON value.
A cursor with read-only concurrency is being used.
For example, prefetching does not occur if the ODBC API SQLSetStmtOption is called, specifying the SQL_CONCURRENCY option and any value other than SQL_CONCUR_READ_ONLY, prior to generating the result set. By default, concurrency is read-only.
Terminology
This topic provides definitions and examples to help you understand the complex technical material presented here.
Aggregate Function
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.
Closed Range
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:
c1 > 1 AND c1 <= 10
c1 BETWEEN 1 AND 10
Conjunct
A conjunct is an expression in which two or more Predicates are joined by AND operators. For example, the following Restrictions are conjuncts:
c1=2 AND c2<5
c1>2 AND c1<5 AND c2= 'abc'
Conjunctive Normal Form (CNF)
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:
c1 = 2 AND c2 < 5
(c1 = 2 OR c1 = 5) AND (c2 < 5 OR c2 > 20) AND (c3 = 'abc' OR c3 = 'efg')
Disjunct
A disjunct is an Expression in which two or more Predicates are joined by OR operators. For example, the following expressions are disjuncts:
c1 = 2 OR c2 = 5
c1 = 2 OR c1 > 5 OR c2 = 'abc'
Disjunctive Normal Form (DNF)
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:
c1 = 2 OR c2 < 5
(c1 = 2 AND c2 = 5) OR (c2 > 5 AND c2 < 10) OR c3 = 'abc'
Expression
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.
Index
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.
Join Condition
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:
t1.c1 = t2.c1
t1.c1 > t2.c2
Leading Segments
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.
Modified Conjunctive Normal Form (Modified CNF)
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:
c1 = 2 AND c2 < 5
(c1 = 2 OR (c1 > 4 AND c1 < 6) OR c1 = 10) AND (c2 = 1 OR c3 = 'efg')
Modified Disjunct
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:
c1 = 2 OR (c1 > 4 AND c1 < 5)
(c1 = 2 OR (c1 > 4 AND c1 < 5)) OR c2 = 'abc'
Open-Ended Range
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:
c1 > 1
c1 <= 10
t1.c1 > t2.c1
Predicate
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:
(c1 = 1)
(c1 LIKE 'abc')
(c1 BETWEEN 1 AND 2)
The following examples are not predicates:
(c1 > 1 AND c1 < 5)
(c1 = 1 OR c1 = 2)
Restriction
A restriction is defined as the entire WHERE clause of a SQL query.
 
Last modified date: 10/31/2023