Terminology

This section provides definitions and examples designed to help you understand the complex technical material in this chapter.

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 an 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’s 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:

(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 the entire WHERE clause of an SQL query.