5.12.2 Using the datetime information of the archive range column to narrow the search range

(a) Specification rules and example of recommended specification

Specification format of comparison predicate

comparison-predicate::=comparison-operand-1 comparison-operator comparison-operand-2

Specification rules

• =, <, <=, >=, or > is specified as the comparison operator.

• One of the comparison operands specifies the archive range column (as a single column specification).

• The opposite comparison operand specifies a value specification.

Note

We recommend that you specify a literal in the value specification.

Example of recommended specification

SELECT * FROM "ARCHIVE-T1" WHERE "RECORD-DAY" >= DATE'2016/01/01'

Examples of deprecated specification

SELECT * FROM "ARCHIVE-T1" WHERE "RECORD-DAY" = ?
SELECT * FROM "ARCHIVE-T1" WHERE "RECORD-DAY" >= CURRENT_DATE

Although these examples result in narrowing of the search range, use of non-literals is not recommended.

Important

Specifying a literal in the value specification results in faster narrowing of the search range than if a non-literal were specified.

(b) Specification examples where search range is not narrowed

Example 1

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" - 10 DAY > DATE'2016/02/10'

In this example, the search range is not narrowed because a comparison operand specifies a datetime operation that uses the archive range column. That is, it is not a single column specification. In this case, you can narrow the search range by amending the SELECT statement as follows:

Amended example

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" > DATE'2016/02/10' + 10 DAY

In this example, the archive range column is specified as a single column specification in the comparison operand on one side. The comparison operand on the other side specifies a value expression equivalent to a literal.

Example 2

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" > CURRENT_DATE - 1 YEAR

In this example, the search range is not narrowed because the comparison operand does not specify a value specification. In this case, you can narrow the search range by amending the SELECT statement as follows:

Amended example

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" > DATE'2016/02/10' - 1 YEAR

As in this example, specify CURRENT_DATE explicitly as a literal. The search range will then be narrowed because the comparison operand on the right side is a value expression equivalent to a literal.

For details about value expressions equivalent to literals, see the table Conditions under which value expressions are equivalent to literals under Rules in Specification format and rules for value expressions in the manual HADB SQL Reference.

(a) Specification rules and example of recommended specification

Specification format of IN predicate

IN predicate::=value-expression-1 [IS] [NOT] IN {(value-expression-2[,value-expression-3]...)|table-subquery}

Specification rules

• value-expression-1 specifies the archive range column (as a single column specification).

• value-expression-2 and subsequent value expressions specify value specifications.

• There is no table subquery specified in the IN predicate.

• NOT is not specified in the IN predicate.

Note

We recommend that you specify literals for the value specifications specified in value-expression-2 onward.

Example of recommended specification

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" IN (DATE'2016/01/01',DATE'2016/02/01')

Example of deprecated specification

SELECT * FROM "ARCHIVE-T1" WHERE "RECORD-DAY" IN (?,?)

Although this example results in narrowing of the search range, use of non-literals is not recommended.

Important

Specifying a literal in the value specification results in faster narrowing of the search range than if a non-literal were specified.

(b) Specification examples where search range is not narrowed

Example 1

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" IN (CURRENT_DATE,
                           CURRENT_DATE - 7 DAY,
                           CURRENT_DATE - 14 DAY)

In this example, the search range is not narrowed because a condition other than a value specification is specified in value-expression-2 and later in an IN predicate. In this case, you can narrow the search range by amending the SELECT statement as follows:

Amended example

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" IN (DATE'2016/02/10',
                           DATE'2016/02/10' - 7 DAY,
                           DATE'2016/02/10' - 14 DAY)

As in this example, specify CURRENT_DATE explicitly as a literal. The search range will then be narrowed because the value expressions specified in value-expression-2 and later in the IN predicate are equivalent to literals.

For details about value expressions equivalent to literals, see the table Conditions under which value expressions are equivalent to literals under Rules in Specification format and rules for value expressions in the manual HADB SQL Reference.

Example 2

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" IN (SELECT "SALES_DATE" FROM "SALESLIST"
                             WHERE "USERID" = 'U001')

In this example, the search range is not narrowed because a table subquery is specified in an IN predicate. In this case, you can narrow the search range by amending the SELECT statement as follows:

Amended example

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" IN (SELECT "SALES_DATE" FROM "SALESLIST"
                             WHERE "USERID" = 'U001')
      AND "RECORD-DAY" > DATE'2015/10/01'

As in this example, add a search condition that results in narrowing of the search range.

(a) Specification rules and example of recommended specification

Specification format of BETWEEN predicate

BETWEEN predicate::=value-expression-1 [NOT] BETWEEN value-expression-2 AND value-expression-3

Specification rules

• value-expression-1 specifies the archive range column (as a single column specification).

• value-expression-2 and value-expression-3 specify value specifications.

• NOT is not specified in the BETWEEN predicate.

Note

We recommend that you specify literals for the value specifications in value-expression-2 and value-expression-3.

Example of recommended specification

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" BETWEEN DATE'2016/01/01' AND DATE'2016/01/10'

Example of deprecated specification

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" BETWEEN ? AND ?

Although this example results in narrowing of the search range, use of non-literals is not recommended.

Important

Specifying a literal in the value specification results in faster narrowing of the search range than if a non-literal were specified.

(b) Specification examples where search range is not narrowed

Example 1

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" BETWEEN CURRENT_DATE - 2 YEAR
                           AND CURRENT_DATE - 1 YEAR

In this example, the search range is not narrowed because a condition other than a value specification is specified as value-expression-2 or value-expression-3 of the BETWEEN predicate. In this case, you can narrow the search range by amending the SELECT statement as follows:

Amended example

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" BETWEEN DATE'2016/02/10' - 2 YEAR
                           AND DATE'2016/02/10' - 1 YEAR

As in this example, specify CURRENT_DATE explicitly as a literal. The search range will then be narrowed because value-expression-2 or value-expression-3 in the BETWEEN predicate is a value expression that is equivalent to a literal.

For details about value expressions equivalent to literals, see the table Conditions under which value expressions are equivalent to literals under Rules in Specification format and rules for value expressions in the manual HADB SQL Reference.

Example 2

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" NOT BETWEEN DATE'2016/01/01' AND DATE'2016/01/31'

In this example, the search range is not narrowed because NOT is specified in a BETWEEN predicate. In this case, you can narrow the search range by amending the SELECT statement as follows:

Amended example

SELECT * FROM "ARCHIVE-T1"
    WHERE "RECORD-DAY" BETWEEN DATE'2015/12/01' AND DATE'2015/12/31'
      AND "RECORD-DAY" BETWEEN DATE'2016/02/01' AND DATE'2016/02/29'

As in this example, do not specify NOT in a BETWEEN predicate. Specify multiple BETWEEN predicates with an AND condition.