The utility narrows the index search range by the specified search condition, then conducts evaluation based on the specified key condition.
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.
where T1.C1 like '%c'
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.
where T1.C1 like ?
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.
where T1.C1 is null
KeyCnd: T1.C1 is null
where T1.C1 is not null
KeyCnd: T1.C1 is not null
where T1.C1 in ('a','b','c')
KeyCnd: T1.C1 in ('a','b','c')
where T1.C1 not in ('a','b','c')
KeyCnd: T1.C1 not in ('a','b','c')
where T1.C1 like '%a'
KeyCnd: T1.C1 like '%a'
where T1.C1 not like '%a\_' escape '\'
KeyCnd: T1.C1 not like '%a\_' escape '\'
where T1.C1 xlike '%a'
KeyCnd: T1.C1 xlike '%a'
where T1.C1 not xlike '%a\_' escape '\'
KeyCnd: T1.C1 not xlike '%a\_' escape '\'
where T1.C1 >= 'a' and T1.C1 <= 'z'
KeyCnd: T1.C1 between 'a' and 'z'
where T1.C1 not between 'a' and 'z'
KeyCnd: T1.C1 < 'a' OR T1.C1 > 'z'
where T1.C1=T1.C2
KeyCnd: T1.C1=T1.C2
where T1.C1=(select C1 from T2)
KeyCnd: T1.C1=SUBQ(2)
where array(T1.C1,T1.C2)[any]
(T1.C1<'a' or (T1.C1>'z' and T2.C2='a'))
KeyCnd: array(...)[any]
(T1.C1<'a' or (T1.C1>'z' and T2.C2='a'))
Example: where T1.C1 similar to '%a'
KeyCnd: T1.C1 similar to '%a'
Example: where T1.C1 not similar to '%a\_' escape '\'
KeyCnd: T1.C1 not similar to '%a\_' escape '\'
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.
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.
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.
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.
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.
The utility displays the name of an SQL variable if it is specified in the key condition.
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.
The utility displays the user when the value of the USER function is specified in the key condition.
The utility displays the current date when the value of the CURRENT_DATE function is specified in the key condition.
The utility displays the current time when the value of the CURRENT_TIME function is specified in the key condition.
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).
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.
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.
The utility displays a scalar operation if it is specified in the key condition.
The utility displays a row value constructor if it is specified in the key condition.