Query Expressions for Fragment Elimination

A query expression (the expression in the WHERE clause) can consist of any of the following expressions:

• Simple expression
• Not simple expression
• Multiple expressions

The database server considers only simple expressions or multiple simple expressions combined with certain operators for fragment elimination.

A simple expression consists of the following parts:

column operator value 

Simple Expression Part

Description

column

Is a single column name

The database server supports fragment elimination on all column types except columns that are defined with the NCHAR, NVARCHAR, BYTE, and TEXT data types.

operator

Must be an equality or range operator

value

Must be a literal or a host variable

The following examples show simple expressions:

name = "Fred"
date < "01/25/1994"
value >= :my_val

The following examples are not simple expressions:

unitcost * count > 4500
price <= avg(price)
result + 3 > :limit

The database server considers two types of simple expressions for fragment elimination, based on the operator:

• Range expressions
• Equality expressions

Range Expressions in Query

Range expressions use the following relational operators:

<, >, <=, >=, !=

The database server can handle one or two column fragment elimination on queries with any combination of these relational operators in the WHERE clause.

The database server can also eliminate fragments when these range expressions are combined with the following operators:

AND, OR, NOT
IS NULL, IS NOT NULL
MATCH, LIKE 

If the range expression contains MATCH or LIKE, the database server can also eliminate fragments if the string ends with a wildcard character. The following examples show query expressions that can take advantage of fragment elimination:

columna MATCH "ab*"
columna LIKE "ab%" OR columnb LIKE "ab*" 

Equality Expressions in Query

Equality expressions use the following equality operators:

=, IN 

The database server can handle one or multiple column fragment elimination on queries with a combination of these equality operators in the WHERE clause. The database server can also eliminate fragments when these equality expressions are combined with the following operators:

AND, OR