17.5.12 Key conditions

Organization of this subsection
(1) Overview of key conditions
(2) Predicates used in key conditions
(3) Values used in key conditions

(1) Overview of key conditions

KeyCnd: key-condition
A key condition makes evaluation possible only with the index component columns.
Given a key condition, the utility can evaluate the search condition only with the index pages without having to reference the data pages, thereby achieving high-speed retrieval. Evaluation based on key conditions is effective if it is conducted after narrowing the index search range by search conditions. If it is impossible to evaluate a predicate with the specified search conditions and key conditions, the utility evaluates it during data page retrieval.
To specify multiple key conditions, use AND and OR along with parentheses to indicate the priority levels of AND and OR.
(a) Both search condition and key condition specified

The utility narrows the index search range by the specified search condition, then conducts evaluation based on the specified key condition.

Example
where T1.C1 between a and z and T1.C1 like %c
[Figure]
Example
where XMLEXISTS('/A/B/C/D/E[./F<8 and ./F>2 and ./F<>5] '
passing by value T1.C1)
[Figure]
(b) Only key condition specified

The utility conducts evaluation based on the specified key condition from the top to the end of the index. Because the utility needs to search the entire range of index, performance is lower than when both search and key conditions are specified.

Example

where T1.C1 like '%c'

[Figure]
Example
where XMLEXISTS('/A/B/C/D/E[./F<>0] 'passing by value T1.C1 )
[Figure]
(c) Key condition deleted during execution

If you specify an embedded variable, ? parameter, SQL variable, LIKE predicate containing an SQL parameter, or a SIMILAR predicate in a pattern character string, and the pattern character string given during execution is something such as abc%d, the utility searches as the search condition for values that begin with abc and determines as the key condition whether or not the value ends with d. If the pattern character string given during execution results in right truncation, such as 'abc%', the utility assumes that the condition can be evaluated simply by searching for a value that begins with 'abc' and does not evaluate the key condition during execution.

The utility displays in diamond brackets (< >) a key condition that can be deleted due to the value of pattern character string.

Example

where T1.C1 like ?

Where the value of the ? parameter is 'abc%' (data type of C1: CHAR(5)).
[Figure]
Explanation:
  • ?: 'abc%'
    The utility searches the range from X'6162630000' to X'616263ffff'. (The key condition is deleted).
  • ?: 'abc%d'
    The utility searches the range from X'6162630000' to X'616263ffff' and evaluates T1.C1 like 'abc%d' using the key condition.
  • ?: '%abc'
    The utility searches the entire range of the index and evaluates T1.C1 like '%abc' using the key condition (only the search condition is deleted; for details about the search conditions, see 17.5.11 Search conditions).
  • ?(1) indicates the ? number. For details, see (3) Values used in key conditions.

(2) Predicates used in key conditions

This section presents the format of predicates that can be included in the key condition. You cannot use a quantified predicate or EXISTS predicate in a key condition. The examples of key conditions explained here may be used for search conditions to achieve high-speed retrieval depending on the index definition method.

(a) NULL predicate
value is null:
The utility evaluates to see whether the index key value is null.
Example

where T1.C1 is null
 [Figure] KeyCnd: T1.C1 is null

value is not null:
The utility evaluates to see whether the index key value is not null.
Example

where T1.C1 is not null
 [Figure] KeyCnd: T1.C1 is not null

(b) IN predicate
value in (value,value,..., value):
The utility evaluates to see whether the index key value matches any of the specified values.
Example

where T1.C1 in ('a','b','c')
 [Figure] KeyCnd: T1.C1 in ('a','b','c')

value not in (value,value,..., value):
The utility evaluates to see whether the index key value matches none of the specified values.
Example

where T1.C1 not in ('a','b','c')
 [Figure] KeyCnd: T1.C1 not in ('a','b','c')

(c) LIKE predicate
value like pattern-character-string[escape escape-character]:
The utility evaluates to see whether the index key value matches the specified pattern.
Example

where T1.C1 like '%a'
 [Figure] KeyCnd: T1.C1 like '%a'

value not like pattern-character-string[escape escape-character]:
The utility evaluates to see whether the index key value does not match the specified pattern.
Example

where T1.C1 not like '%a\_' escape '\'
 [Figure] KeyCnd: T1.C1 not like '%a\_' escape '\'

(d) XLIKE predicate
value xlike pattern-character-string[escape escape-character]:
The utility evaluates to see whether the index key value matches the specified pattern regardless of the case (uppercase or lowercase).
Example

where T1.C1 xlike '%a'
 [Figure] KeyCnd: T1.C1 xlike '%a'

value not xlike pattern-character-string[escape escape-character]:
The utility evaluates to see whether the index key value does not match the specified pattern regardless of the case (uppercase or lowercase).
Example

where T1.C1 not xlike '%a\_' escape '\'
 [Figure] KeyCnd: T1.C1 not xlike '%a\_' escape '\'

(e) BETWEEN predicate
value between value-1 and value-2:
The utility evaluates to see whether the index key value is in the range from value-1 to value-2. Note that not between is converted to a combination of a comparison predicate (<, >) and OR.
Example 1

where T1.C1 >= 'a' and T1.C1 <= 'z'
  [Figure] KeyCnd: T1.C1 between 'a' and 'z'

Example 2

where T1.C1 not between 'a' and 'z'
  [Figure] KeyCnd: T1.C1 < 'a' OR T1.C1 > 'z'

(f) Comparison predicate
value{=, <, <=, >, >=, <>}value:
The utility evaluates to see whether the index key value meets the comparison predicate (=, <, <=, >, >=, <>).
Example 1

where T1.C1=T1.C2
 [Figure] KeyCnd: T1.C1=T1.C2

Example 2

where T1.C1=(select C1 from T2)
 [Figure] KeyCnd: T1.C1=SUBQ(2)

(g) Structured repetition predicate
array(...)[any](key-condition):
From the conditions specified with a structured repetition predicate, the utility eliminates the conditions that can be evaluated by the search conditions, then displays the conditions to be evaluated by the key condition.
Example

where array(T1.C1,T1.C2)[any]
  (T1.C1<'a' or (T1.C1>'z' and T2.C2='a'))
 [Figure] KeyCnd: array(...)[any]
       (T1.C1<'a' or (T1.C1>'z' and T2.C2='a'))

(h) SIMILAR predicate
value similar to pattern-character-string[escape escape-character]:
The utility evaluates whether the index key value matches the specified pattern.

Example: where T1.C1 similar to '%a'
 [Figure] KeyCnd: T1.C1 similar to '%a'

value not similar to pattern-character-string[escape escape-character]:
The utility evaluates whether the index key value does not match the specified pattern.

Example: where T1.C1 not similar to '%a\_' escape '\'
 [Figure] KeyCnd: T1.C1 not similar to '%a\_' escape '\'

(3) Values used in key conditions

(a) table-name.column-name

The utility displays table-name.column-name if a column name is specified in the key condition.

If the table name is a correlation name, the correlation name is displayed in parentheses instead.

(b) (NEW ROW).column-name

The utility displays (NEW ROW).column-name when using an insertion or updating value for a foreign key to narrow the search condition in a subquery that is created internally by HiRDB for checking constraints.

(c) table-name.column-name[subscript]

The utility displays table-name.column-name[subscript] if a repetition column (subscript is an integer or ANY) is specified in the key condition.

If the table name is a correlation name, the correlation name is displayed in parentheses instead.

(d) literal

The utility displays a value if a literal is specified in the key condition. For details about the literal output format, see the HiRDB Version 9 SQL Reference.

(e) ? (?-number)

The utility displays ? (?-number) if an embedded variable or ? parameter is specified in the key condition. ?-number is a sequence number assigned to each embedded variable or ? parameter in the SQL statement in the order it appears from left to right, beginning at number 1.

(f) SQL variable name

The utility displays the name of an SQL variable if it is specified in the key condition.

(g) SQL parameter name

The utility displays the name of an SQL parameter if it is specified in the key condition.

If a column with new value correlation name and a column with old correlation name are used in a trigger SQL statement, the former is displayed as (NEWROW).column-name and the latter as (OLDROW).column-name.

(h) USER

The utility displays the user when the value of the USER function is specified in the key condition.

(i) CURRENT_DATE

The utility displays the current date when the value of the CURRENT_DATE function is specified in the key condition.

(j) CURRENT_TIME

The utility displays the current time when the value of the CURRENT_TIME function is specified in the key condition.

(k) CURRENT_TIMESTAMP(p)

The utility displays the current timestamp when the value of the CURRENT_TIMESTAMP(p) function is used in the key condition (p = 0, 2, 4, or 6).

(l) (SUBQ(query-ID))

The utility displays SUBQ(query-ID) if a row subquery or a scalar subquery that does not contain a set operation is specified in the key condition. The parentheses enclose the query ID.

(m) SUBQEX(query-expression-ID)

The utility displays SUBQ(query-ID) if a row subquery or a scalar subquery that contains a set operation is specified in the key condition. The parentheses enclose the query ID.

(n) Scalar operation

The utility displays a scalar operation if it is specified in the key condition.

(o) row value constructor

The utility displays a row value constructor if it is specified in the key condition.