SearchCnd:search-condition-type[narrowed-range],...
A search condition determines the range of index to be searched.
where C1 between 'a' and 'z'
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 results in right truncation, such as abc%, the utility can narrow the index search range. However, if the pattern character string results in left truncation such as %abc, the utility cannot narrow the index search range.
If the index search range cannot be narrowed due to the value of pattern character string provided during execution, the utility displays in diamond brackets (< >) the start and end values used to narrow the index range.
where C1 LIKE ?
Where the data type of C1 is CHAR(5).
Table 17-1 lists the search conditions by type.
Table 17-1 Search conditions by type
Type of search condition | Characteristics | Condition for creating the type of search condition | |
---|---|---|---|
Single-column index | Multicolumn index | ||
IS NULL | Effective when there are only a few null key values. | There is a NULL predicate (IS NULL) in all index component columns. | |
IS NOT NULL | Effective when there are only a few non-null key values. | There is a NULL predicate (IS NULL) in the index component columns. | Not created |
IS TRUE | Depends on the plug-in-provided function. | There is checking using a Boolean predicate (IS TRUE) for the execution result of a plug-in-provided function. | Not created |
AT | Effective when there are only a few duplicates in the narrowing value. | There is a comparison predicate (=) in one or more index component columns and a NULL predicate (IS NULL) in all other index component columns. | |
RANGE | Effective when there are only a few narrowed ranges. | The index component column contains a comparison predicate (>, >=, <, <=), BETWEEN predicate, LIKE or SIMILAR predicate that results in right truncation. For a multicolumn index, there is a comparison predicate (=) or a NULL predicate (IS NULL) in some of the index component columns. This does not apply to RANGES. | |
ATS | Effective when there are only a few duplicates in the narrowing value and only a few narrowing values. | There is an IN (row-value-constructor3), IN (table-subquery), =ANY (table-subquery), or =SOME (table-subquery) in one or more index component columns,1 and either a comparison predicate (=) or an IS NULL in all the other index component columns. If the product of the narrowing values specified in IN (row-value-constructor3), IN (table-subquery), =ANY (table-subquery), or =SOME (table-subquery) exceeds 255, RANGES is assumed.2 | |
RANGES | Effective when there are only a few items in the narrowed ranges and only a few narrowed ranges. | Note created | The index component column containing an IN (row-value-constructor3), IN (table-subquery), =ANY (table-subquery), or =SOME (table-subquery) is the first index component column;1 or, if it is not the first index component column, all the index component columns preceding this index component column contain either comparison predicate (=) or IS NULL. This does not apply to ATS. |
IS NULL searches a range of index in which the value is null.
where C1 IS NULL
SearchCnd: IS NULL
IS NOT NULL searches a range of index in which the value is not null.
where C1 IS NOT NULL
SearchCnd: IS NOT NULL
IS TRUE is displayed for a retrieval using a plug-in index.
where SearchImageData (feature,? as blob(1M),
? as varchar(1024))IS TRUE
SearchCnd: IS TRUE
where Contains (SENTENCES,'DOCUMENT_DATA[EFFECTS
{"FOOD_POISONING"}]')IS TRUE
SearchCnd: IS TRUE
where C1='a'
SearchCnd: AT ['a']
AT for a single-column index
where C1='a'
SearchCnd: AT ['a']
AT for a multicolumn index
where C1='a' and C2='A'
SearchCnd: AT [('a','A')]
where (C1,C2) in (('a','A'))
SearchCnd: AT [('a','A')]
where (C1,C2) = ((select C1 from T2),(select C2 from T3))
SearchCnd: AT [((SUBQ(2)),(SUBQ(3)))]
where array(C1,C2) [any] ((C1,C2)=('a','A'))
SearchCnd: AT [('a','A')]
Use RANGE to search an index in a narrowed range from the start value to the end value. RANGE is classified into four types depending on whether it includes the start value for the narrowed range and whether it includes the end value for the narrowed range. This information is displayed in the portion enclosed in double quotation marks ("") in RANGE(...).
The search condition can be C1>='a' and C1<='z', C1 between 'a' and 'z', C1 like 'a%', etc.
where C1 >= 'a' and C1 <= 'z'
SearchCnd: RANGE(CS-CE) ['a','z']
where C1 >= 'a'
SearchCnd: RANGE(CS-CE) ['a',MAX]
where C1 <= 'a'
SearchCnd: RANGE(CS-CE) [MIN,'a']
where C1 >= 'a' and C1 < 'z'
SearchCnd: RANGE(CS-OE) ['a','z']
where C1 < 'a'
SearchCnd: RANGE(CS-OE) [MIN,'a']
where C1 > 'a' and C1 <= 'z'
SearchCnd: RANGE(OS-CE) ['a','z']
where C1 > 'a'
SearchCnd: RANGE(OS-CE) ['a',MAX]
where C1 > 'a' and C1 < 'z'
SearchCnd: RANGE(OS-OE) ['a','z']
RANGE for a single-column index
where C1 between 'a' and 'z'
SearchCnd: RANGE(CS-CE) ['a','z']
RANGE for a multicolumn index
where C1 = 'a' and C2 between 'A' and 'Z'
SearchCnd: RANGE(CS-CE) [('a','A'),('a','Z')]
where (C1,C2) between ('A', 'Z') and (select C1, C2 from T2)
SearchCnd: RANGE(CS-CE)[( 'A', 'Z'),((SUBQ(2)),(SUBQ(2)))]
where (C1,C2) > ('a', 'z') and (C1,C2) < ('A', 'Z')
SearchCnd: RANGE(OS-OE)[( 'a', 'z'),( 'A', 'Z')]
where array(C1,C2) [any] ((C1,C2)<( 'A', 'Z'))
SearchCnd: RANGE(CS-OE)[(MIN,MIN),( 'A', 'Z')]
where C1 in ('a','b','c')
SearchCnd: ATS ['a'],['b'],['c']
where C1 =any(select C1 from T2)
SearchCnd:ATS [SUBQ(2)]
where C1 in ('a','b','c') and C2=any(Select C1 from T2)
SearchCnd: ATS [('a',(SUBQ(2)))],
[('b',(SUBQ(2)))],
[('c',(SUBQ(2)))]
where C1 in ('a','b','c')
SearchCnd:ATS ['a'],['b'],['c']
where C1 =any(select C1 from T2)
SearchCnd: ATS [(SUBQ(2))]
where C1 in ('a','b') and C2 in ('A','B') and C3=1
SearchCnd:ATS [('a','A',1)],[('a','B',1)],[('b','A',1)],[('b','B',1)]
where (C1,C2) in (('a','b'), ('A','B'))
SearchCnd: ATS [('a','b'),( 'A','B')]
where C1=any(select C1 from T2) and C2='a' and C3='A'
SearchCnd: ATS [((SUBQ(2)),'a','A')]
where C1= 'a' and (C2,C3) in (select C2, C3 from T2)
SearchCnd: ATS [('a',(SUBQ(2)),(SUBQ(2)))]
where C1=any(select C1 from T2) and C2 in ('a','b')
and C3 in ('A','B')
SearchCnd: ATS [((SUBQ(2)),'a','A')],[((SUBQ(2)),'a','B')],
[((SUBQ(2)),'b','A')],[((SUBQ(2)),'b','B')]
RANGES is applicable to multicolumn indexes only. Use it to search a range of index narrowing values, from start value to end value, more than once.
RANGES is classified into three types depending on whether it includes the start value for the narrowed range and whether it includes the end value for the narrowed range. This information is displayed in the portion enclosed in double quotation marks ("") in RANGE(...).
where C1 in ('a','b','c') and C2 between 'A' and 'Z'
SearchCnd: RANGES(CS-CE)
[('a','A'),('a','Z')],[('b','A'),('b','Z')],[('c','A'),('c','Z')]
where C1 in ('a','b','c')
SearchCnd: RANGES(CS-CE)
[('a',MIN),('a',MAX)],[('b',MIN),('b',MAX)],
[('c',MIN),('c',MAX)]
where (C1,C2) in (('a', 'a'), ('b', 'b'))
SearchCnd: RANGES(CS-CE)
[('a', 'a',MIN),( 'a', 'a',MAX)] ,
[('b', 'b',MIN),( 'b', 'b',MAX)]
where C1 =any(select C1 from T2)
SearchCnd: RANGES(CS-CE)
[((SUBQ(2)),MIN),((SUBQ(2)),MAX)]
where (C1,C2) = any(select C1, C2 from T2)
SearchCnd: RANGES(CS-CE)
[((SUBQ(2)),(SUBQ(2)),MIN),((SUBQ(2)),
(SUBQ(2)),MAX)]
where C1 in ('a','b','c') and C2=any(Select C1 from T2)
SearchCnd: RANGES(CS-CE)
[('a',(SUBQ(2)),MIN),('a',(SUBQ(2)),MAX)],
[('b',(SUBQ(2)),MIN),('b',(SUBQ(2)),MAX)],
[('c',(SUBQ(2)),MIN),('c',(SUBQ(2)),MAX)]
where C1 in ('a','b') and
(C2,C3)=any(Select C1,C2 from T2) and C4>'A'
SearchCnd: RANGES(CS-CE)
[('a',(SUBQ(2)),(SUBQ(2)),'A'),
('a',(SUBQ(2)),(SUBQ(2)),MAX)],
[('b',(SUBQ(2)),(SUBQ(2)),'A'),
('b',(SUBQ(2)),(SUBQ(2)),MAX)]
The utility displays a value if a literal is used to narrow the search condition. For details about the literal output format, see the HiRDB Version 8 SQL Reference.
If a LIKE or SIMILAR predicate is used to specify a pattern character string that results in right truncation, the literal is displayed as shown in Table 17-2.
Table 17-2 Display example and search range when LIKE predicate is used to specify a pattern character string that results in right truncation
Data type of column | Pattern character string | Output example of narrowed range based on search condition | Index search range* |
---|---|---|---|
Fixed length (e.g., char(5)) | Literal | RANGE(CS-CE) ['abc'00,'abc'ff] (where 'abc%' is the literal) For the character string found, eliminate the first '%' part or the part following '_', then fill 0s up to the defined length for the start value and fill fs up to the defined length for the end value. | X'6162630000' to X'616263ffff' |
Embedded variable or ? parameter | RANGE(CS-CE) [<?(1)0>,<?(1)f>] Fill one 0 for the start value and one 'f' for the end value. | X'6162630000' to X'616263ffff' (? parameter is 'abc%'.) | |
SQL variable or SQL parameter (SQLVAR assumed as the name of SQL variable) | RANGE(CS-CE) [<SQLVAR0>,<SQLVARf>] Fill one 0 for the start value and one 'f' for the end value. | X'6162630000' to X'616263ffff' (SQL variable is 'abc%'.) | |
Variable length (e.g., varchar(5)) | Literal | RANGE(CS-CE) ['abc','abc'ff] (where 'abc%' is the literal) For the start value, eliminate the first '%' part or the part following '_'; for the end value, fill fs in the start value up to the defined value. | X'616263' to X'616263ffff' |
Embedded variable or ? parameter | RANGE(CS-CE) [<?(1)>,<?(1)f>] Only for the end value, fill one f. | X'616263' to X'616263ffff' (? parameter is 'abc%'.) | |
SQL variable or SQL parameter (SQLVAR assumed as the name of SQL variable) | RANGE(CS-CE) [<SQLVAR>,<SQLVARf>] Only for the end value, fill one f. | X'616263' to X'616263ffff' (SQL variable is 'abc%'.) |
* Character code is X'61'for a, X'62'for b, and X'63'for c.
The utility displays ? (?-number) for an embedded variable or ? parameter. ?-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.
If a LIKE or SIMILAR predicate is used to specify a pattern character string that results in right truncation, ? is displayed as shown in Table 17-2.
The utility displays the name of an SQL variable.
If a LIKE or SIMILAR predicate is used to specify a pattern character string that results in right truncation, the SQL variable name is displayed as shown in Table 17-2.
The utility displays the name of an SQL parameter.
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.
If a LIKE or SIMILAR predicate is used to specify a pattern character string that results in right truncation, the SQL parameter name is displayed as shown in Table 17-2.
The utility displays the user when using USER to narrow the search condition.
The utility displays the current date when using CURRENT DATE to narrow the search condition.
The utility displays the current time when using CURRENT TIME to narrow the search condition.
The utility displays CURRENT_TIMESTAMP(p) when using CURRENT_TIMESTAMP to narrow the search condition (p = 0, 2, 4, or 6).
The utility displays SUBQ(subquery-ID) when using the result of a subquery that does not contain a set operation to narrow the search condition. The parentheses enclose the query ID.
The utility displays SUBQEX(query-expression-ID) when using the result of a subquery that contains a set operation to narrow the search condition. The parentheses enclose the query ID.
The utility displays set function when a set function is used to narrow the search condition.
The utility displays NULL when using the null value to narrow the search condition.
The utility displays table-name.column-name when using columns to narrow the search 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] when using repetition columns (subscript is an integer) to narrow the search condition.
If the table name is a correlation name, the correlation name is displayed in parentheses instead.
The utility displays MIN to search for the smallest index key value (for a character string, the smallest character encoding).
The utility displays MAX to search for the largest index key value (for a character string, the largest character encoding).