17.5.11 Search conditions

Organization of this subsection
(1) Overview of search conditions
(2) Types of search conditions
(3) Values used in a narrowed range

(1) Overview of search conditions

SearchCnd:search-condition-type[narrowed-range],...

A search condition determines the range of index to be searched.

(a) Search condition specified
Example

where C1 between 'a' and 'z'

[Figure]
Note
The index component column to be used is C1.
(b) Search 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 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.

Example

where C1 LIKE ?

Where the data type of C1 is CHAR(5).

[Figure]

Note
The index component column to be used is C1.
Explanation:
  • If ? is abc%, the utility searches the range from X'6162630000' to X'616263ffff', where the character code a is X'61', b is X'62', and c is X'63'.
  • If ? is %abc, the utility searches the entire range of the index.
  • ?(1) indicates the ? number. For details, see (3) Values used in a narrowed range.

(2) Types of search conditions

The table below lists the search conditions by type.

Table 17-1 Search conditions by type

Type of search conditionCharacteristicsCondition for creating the type of search condition
Single-column indexMulticolumn index
IS NULLEffective when there are only a few null key values.There is a NULL predicate (IS NULL) in all index component columns.
IS NOT NULLEffective when there are only a few non-null key values.There is a NULL predicate (IS NOT NULL) in the index component columns.Not created
IS TRUEDepends 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
ATEffective 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.
RANGEEffective 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.
ATSEffective when there are only a few duplicates in the narrowing value and only a few narrowing values.There is an IN (row-value-constructor#3), 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-constructor#3), IN (table-subquery), =ANY (table-subquery), or =SOME (table-subquery) exceeds the pd_apply_search_ats_num operand value, RANGE or RANGES is assumed.#2
RANGESEffective when there are only a few items in the narrowed ranges and only a few narrowed ranges.Note createdThe index component column containing an IN (row-value-constructor#3), 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.
If the product of the narrowing values specified in IN (row-value-constructor#3), IN (table-subquery), =ANY (table-subquery), or =SOME (table-subquery) exceeds the pd_apply_search_ats_num operand value, RANGE or RANGES is assumed.#2
This does not apply to ATS.
#1: If the SQL optimization mode being used is the optimizing mode based on cost 2, IN(table-subquery), =ANY(table-subquery), or =SOME(table-subquery) uses only one column for narrowing down the search range. If a multicolumn index is used and the SQL optimization mode in use is the optimizing mode based on cost 1, IN(table-subquery), =ANY(table-subquery), or =SOME(table-subquery) is not used for narrowing the search range.
#2: The number of narrowing values for IN(row-value-constructor) is the number of row value constructors. It is 44 for IN(table-subquery), =ANY(table-subquery), and =SOME(table-subquery). For the types of search conditions based on the number of narrowing value combinations, see Table 17-2 Types of search conditions based on the number of narrowing value combinations.
#3: This is applicable if the row value constructor elements of all row value constructors are value specification, scalar subquery, or row subquery.

The table below shows the types of search conditions based on the number of narrowing value combinations.

Table 17-2 Types of search conditions based on the number of narrowing value combinations

Specified conditionCombination of the number of narrowing valuesType of search condition
There is an IN (row-value-constructor), IN (table-subquery), =ANY (table-subquery), or =SOME (table-subquery) in one or more index component columns, and either a comparison predicate (=) or an IS NULL in all the other index component columns.p > nq > nRANGE
q[Figure] nRANGE
p[Figure] nq > nRANGES#1
q[Figure] nATS#2
The index component column containing an IN (row-value-constructor), IN (table-subquery), =ANY (table-subquery), or =SOME (table-subquery) is the first index component column; or, if it is not the first index component column, all the index component columns preceding this index component column contain either a comparison predicate (=) or IS NULL.p > nq > nRANGE
q[Figure] nRANGE
p[Figure] nq > nRANGES#1
q[Figure] nRANGES#3
Legend:
n: 255 if the HiRDB version is 08-03 or earlier; the pd_apply_search_ats_num operand value if the HiRDB version is 08-04 or later.
p: Number of narrowing values in the predicate specified for index component column x
q: Number of narrowing value combinations in the predicate specified for a column following index component column x
x: First index component column for which IN (row-value-constructor), IN (table-subquery), =ANY (table-subquery), or =SOME (table-subquery) was specified
#1
A condition specified after index component column x is not used in the specified predicates.
#2
If IN (table-subquery), =ANY (table-subquery), or =SOME (table-subquery) is specified more than once, RANGES is assumed unconditionally regardless of the number of narrowing value combinations (because no more than one table subquery can be evaluated).
#3
All the specified conditions are used. However, if IN (table-subquery), =ANY (table-subquery), or =SOME (table-subquery) is specified more than once, RANGES that does not use a condition specified after index component column x is assumed (because no more than one table subquery can be evaluated).

(a) IS NULL

IS NULL searches a range of index in which the value is null.

[Figure]

Note
The index component column to be used is C1.
Example

where C1 IS NULL
[Figure] SearchCnd: IS NULL

(b) IS NOT NULL

IS NOT NULL searches a range of index in which the value is not null.

[Figure]

Note
The index component column to be used is C1.
Example

where C1 IS NOT NULL
[Figure] SearchCnd: IS NOT NULL

(c) IS TRUE

IS TRUE is displayed for a retrieval using a plug-in index.

FULL SCAN TYPE plug-in-provided function
[Figure]
Note
The index component column to be used is feature.
Example

where SearchImageData (feature,? as blob(1M),
                      ? as varchar(1024))IS TRUE
  [Figure] SearchCnd: IS TRUE

Other than FULL SCAN TYPE plug-in-provided function
[Figure]
Note
The index component column to be used is SENTENCES.
Example

where Contains (SENTENCES,'DOCUMENT_DATA[EFFECTS
               {"FOOD_POISONING"}]')IS TRUE
  [Figure] SearchCnd: IS TRUE

(d) AT
Search condition type
AT[narrowing-value]:
Use AT to search the index for a specific value.
The search condition can be C1='a', C1=(Select C1 from T2), for example.
[Figure]
Note
The index component column to be used is C1.
Example

where C1='a'
  [Figure] SearchCnd: AT ['a']

Narrowing value

AT for a single-column index

AT[value]:
Displays the value for a single-column index.
Example

where C1='a'
  [Figure] SearchCnd: AT ['a']

Note: The index component columns to be used is C1.

AT for a multicolumn index

AT[(value,...,value)]:
For a multicolumn index, the utility displays the values sequentially from the first index component column by separating the values with the comma (,) and enclosing the entire set of values in parentheses.
Example 1

where C1='a' and C2='A'
 [Figure] SearchCnd: AT [('a','A')]

Note: The index component columns to be used are C1 and C2.
Example 2

where (C1,C2) in (('a','A'))
     [Figure]  SearchCnd: AT [('a','A')]

Note: The index component columns to be used are C1 and C2.
Example 3

where (C1,C2) = ((select C1 from T2),(select C2 from T3))
     [Figure]  SearchCnd: AT [((SUBQ(2)),(SUBQ(3)))]

Note: The index component columns to be used are C1 and C2.
Example 4

where array(C1,C2) [any] ((C1,C2)=('a','A'))
     [Figure]  SearchCnd: AT [('a','A')]

Note: The index component columns to be used are C1 and C2.
(e) RANGE
Search condition type

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(...).

  1. Both start and end values of narrowed range included
    RANGE(CS_CE)[narrowing-start-value,narrowing-end-value]:
    The search condition can be C1>='a' and C1<='z', C1 between 'a' and 'z', C1 like 'a%', for example.

    [Figure]

    Example 1

    where C1 >= 'a' and C1 <= 'z'
       [Figure] SearchCnd: RANGE(CS-CE) ['a','z']

    Note: The index component column to be used is C1.
    Example 2

    where C1 >= 'a'
       [Figure] SearchCnd: RANGE(CS-CE) ['a',MAX]

    Note: The index component column to be used is C1.
    Example 3

    where C1 <= 'a'
       [Figure] SearchCnd: RANGE(CS-CE) [MIN,'a']

    Note: The index component column to be used is C1.
  2. Narrowing start value included, but narrowing end value not included
    RANGE(CS_OE)[narrowing-start-value,narrowing-end-value]:
    The search condition can be C1>='a' and C1<'z', for example.

    [Figure]

    Example 1

    where C1 >= 'a' and C1 < 'z'
       [Figure] SearchCnd: RANGE(CS-OE) ['a','z']

    Note: The index component column to be used is C1.
    Example 2

    where C1 < 'a'
       [Figure] SearchCnd: RANGE(CS-OE) [MIN,'a']

    Note: The index component column to be used is C1.
  3. Narrowing start value not included, but narrowing end value included
    RANGE(OS_CE)[narrowing-start-value,narrowing-end-value]:
    The search condition can be C1>='a' and C1<='z', for example.

    [Figure]

    Example 1

    where C1 > 'a' and C1 <= 'z'
       [Figure] SearchCnd: RANGE(OS-CE) ['a','z']

    Note: The index component column to be used is C1.
    Example 2

    where C1 > 'a'
       [Figure] SearchCnd: RANGE(OS-CE) ['a',MAX]

    Note: The index component column to be used is C1.
  4. Neither narrowing start value nor end value included
    RANGE(OS_OE)[narrowing-start-value,narrowing-end-value]:
    The search condition can be C1>='a' and C1<'z', for example.

    [Figure]

    Example

    where C1 > 'a' and C1 < 'z'
      [Figure] SearchCnd: RANGE(OS-OE) ['a','z']

    Note: The index component column to be used is C1.
Narrowed range (narrowing-start-value, narrowing-end-value)

RANGE for a single-column index

RANGE (...) [value,value]:
For a single-column index, the utility displays the start and end positions of the narrowed range by separating them by a comma. ... in RANGE (...) depends on the search condition type.
Example

where C1 between 'a' and 'z'
  [Figure] SearchCnd: RANGE(CS-CE) ['a','z']

Note: The index component column to be used is C1.

RANGE for a multicolumn index

RANGE (...) [(value,...,value),(value,...,value)]:
For a multicolumn index, the utility displays a set of values sequentially from the first index component column by separating each value by a comma (,) and enclosing the entire values in parentheses for the start position and for the end position, which are further separated by a comma. ... in RANGE (...) depends on the search condition type.
Example 1

where C1 = 'a' and C2 between 'A' and 'Z'
  [Figure] SearchCnd: RANGE(CS-CE) [('a','A'),('a','Z')]

Note: The index component columns to be used are C1 and C2.
Example 2

where (C1,C2) between ('A', 'Z') and (select C1, C2 from T2)
     [Figure]  SearchCnd: RANGE(CS-CE)[( 'A', 'Z'),((SUBQ(2)),(SUBQ(2)))]

Note: The index component columns to be used are C1 and C2.
Example 3

where (C1,C2) > ('a', 'z') and (C1,C2) < ('A', 'Z')
     [Figure]  SearchCnd: RANGE(OS-OE)[( 'a', 'z'),( 'A', 'Z')]

Note: The index component columns to be used are C1 and C2.
Example 4

where array(C1,C2) [any] ((C1,C2)<( 'A', 'Z'))
     [Figure]  SearchCnd: RANGE(CS-OE)[(MIN,MIN),( 'A', 'Z')]

Note: The index component columns to be used are C1 and C2.
(f) ATS
Search condition type
ATS with multiple narrowing values
ATS[narrowing-value],...,[narrowing-value]:
ATS [narrowing-value], ... ,[narrowing-value],...(Number of All Row Values : n)...,[narrowing-value]:
Use ATS to search an index more than once using narrowing values.
An example of search condition is C1 in ('a', 'b', 'c').
When the total number of narrowing values is 256 or more, ...(Number of All Row Values : n)... is displayed, where n indicates the total number of narrowing values.
Note that the ellipses (...) in ...(Number of All Row Values : n)... do not mean an omission.
[Figure]
Example

where C1 in ('a','b','c')
  [Figure] SearchCnd: ATS ['a'],['b'],['c']

Note: The index component column to be used is C1.
ATS with a set of narrowing values
ATS [set-of-narrowing-values]:
Use this type of ATS to search an index more than once using a set of narrowing values (work table created from table subqueries).
Examples of search condition are C1=any(Select C1 from T2), C1=some(Select C1 from T2), and C1 in (Select C1 from T2).
[Figure]
Example

where C1 =any(select C1 from T2)
     [Figure]  SearchCnd:ATS [SUBQ(2)]

Note: The index component column to be used is C1.
ATS with multiple sets of narrowing values
ATS [set-of-narrowing-values], ... ,[set-of-narrowing-values]:
ATS [set-of-narrowing-values], ... ,[set-of-narrowing-values],...(Number of All Row Values : n)...,[set-of-narrowing-values]:
Use this type of ATS to search an index more than once using the result of combining sets of narrowing values (work table created from table subqueries) and the IN predicate.
An example of search condition is C1 in ('a','b','c') and C2=any(Select C1 from T2).
When the total number of sets of narrowing values is 256 or more, ...(Number of All Row Values : n)... is displayed, where n indicates the total number of sets of narrowing values.
Note that the ellipses (...) in ...(Number of All Row Values : n)... do not mean an omission.
[Figure]
Example

where C1 in ('a','b','c') and C2=any(Select C1 from T2)
     [Figure]  SearchCnd: ATS [('a',(SUBQ(2)))],
                 [('b',(SUBQ(2)))],
                 [('c',(SUBQ(2)))]

Note: The index component columns to be used are C1 and C2.
ATS for a single-column index by multiple narrowing values
ATS [value], ... ,[value]:
ATS [value], ... ,[value],...(Number of All Row Values : n)...,[value]:
Displays the values separated by the comma.
When the total number of narrowing values is 256 or more, ...(Number of All Row Values : n)... is displayed, where n indicates the total number of narrowing values.
Note that the ellipses (...) in ...(Number of All Row Values : n)... do not mean an omission.
Example

where C1 in ('a','b','c')
     [Figure]  SearchCnd:ATS ['a'],['b'],['c']

Note: The index component column to be used is C1.
ATS for a single-column index by a set of narrowing values
ATS [(SUBQ(query-ID))-or-(SUBQEX(query-expression-ID))]:
Displays the ID of the specified table subquery or query expression.
Example

where C1 =any(select C1 from T2)
   [Figure]  SearchCnd: ATS [(SUBQ(2))]

Note: The index component column to be used is C1.
ATS for a multicolumn index by multiple narrowing values
ATS [(value,...,value)], ... ,[(value,...,value)]:
ATS [(value,...,value)], ... ,[(value,...,value)],...(Number of All Row Values : n)...,[(value,...,value):
For a multicolumn index, a single narrowing value consists of the values that are sequentially separated by the comma from the top of the index component columns and then enclosed in its entirety in double quotation marks ("). More than one such narrowing value is displayed by separating them with the comma.
When the total number of narrowing values is 256 or more, ...(Number of All Row Values : n)... is displayed, where n indicates the total number of narrowing values.
Note that the ellipses (...) in ...(Number of All Row Values : n)... do not mean an omission.
Example 1

where C1 in ('a','b') and C2 in ('A','B') and C3=1
     [Figure]  SearchCnd:ATS [('a','A',1)],[('a','B',1)],[('b','A',1)],[('b','B',1)]

Note: The index component columns to be used are C1, C2, and C3.
Example 2

where (C1,C2) in (('a','b'), ('A','B'))
     [Figure]  SearchCnd: ATS [('a','b'),( 'A','B')]

Note: The index component columns to be used are C1 and C2.
ATS for a multicolumn index by a set of narrowing values
ATS [(value,(SUBQ(query-ID)) or (SUBQEX(query-expression-ID)),...,value,(SUBQ(query-ID)) or (SUBQEX(query-expression-ID)))]
For a multicolumn index, a set of narrowing values consists of values or query IDs of table subqueries that are sequential and separated by the comma from the top of the index component columns and then enclosed in their entirety in double quotation marks (").
Example 1

where C1=any(select C1 from T2) and C2='a' and C3='A'
     [Figure]  SearchCnd: ATS [((SUBQ(2)),'a','A')]

Note: The index component columns to be used are C1, C2, and C3.
Example 2

where C1= 'a' and (C2,C3) in (select C2, C3 from T2)
     [Figure]  SearchCnd: ATS [('a',(SUBQ(2)),(SUBQ(2)))]

Note: The index component columns to be used are C1, C2, and C3.
ATS for a multicolumn index by multiple sets of narrowing values
ATS [(value,(SUBQ(query-ID)) or (SUBQEX(query-expression-ID)),...,value,(SUBQ(query-ID)) or (SUBQEX(query-expression-ID)))]
, ... ,[(value,(SUBQ(query-ID)) or (SUBQEX(query-expression-ID)),...,value,(SUBQ(query-ID)) or (SUBQEX(query-expression-ID)))]
ATS [(value,(SUBQ(query-ID)) or (SUBQEX(query-expression-ID)),...,value,(SUBQ(query-ID)) or (SUBQEX(query-expression-ID)))]
, ... ,[(value,(SUBQ(query-ID)) or (SUBQEX(query-expression-ID)),...,value,(SUBQ(query-ID)) or (SUBQEX(query-expression-ID)))]
,...(Number of All Row Values : n)...
,[(value,(SUBQ(query-ID)) or (SUBQEX(query-expression-ID)),...,value,(SUBQ(query-ID)) or (SUBQEX(query-expression-ID)))]:
For a multicolumn index, a set of narrowing values consists of the values or query IDs of table subqueries that are sequential and separated by the comma from the top of the index component columns and then enclosed in their entirety in double quotation marks ("). More than one set of such narrowing values is displayed by separating them with the comma.
When the total number of narrowing values is 256 or more, ...(Number of All Row Values : n)... is displayed, where n indicates the total number of sets of narrowing values.
Note that the ellipses (...) in ...(Number of All Row Values : n)... do not mean an omission.
Example

where C1=any(select C1 from T2) and C2 in ('a','b')
           and C3 in ('A','B')
     [Figure]  SearchCnd: ATS [((SUBQ(2)),'a','A')],[((SUBQ(2)),'a','B')],
                    [((SUBQ(2)),'b','A')],[((SUBQ(2)),'b','B')]

Note: The index component columns to be used are C1, C2, and C3.
(g) RANGES

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(...).

RANGES by multiple narrowed ranges
RANGES(...) [narrowing-start-value,narrowing-end-value], ... ,[narrowing-start-value,narrowing-end-value]:
RANGES(...) [narrowing-start-value,narrowing-end-value], ... ,[narrowing-start-value,narrowing-end-value],...(Number of All Row Values : n)...,[narrowing-start-value,narrowing-end-value]:
Use this type of RANGES to search an index more than once using narrowed ranges.
An example of a search condition is C1 in ('a','b','c') and C2 between 'A' and 'Z',C1 in ('a','b','c').
When the total number of narrowing values is 256 or more, ...(Number of All Row Values : n)... is displayed, where n indicates the total number of narrowing ranges.
Note that the ellipses (...) in ...(Number of All Row Values : n)... do not mean an omission.
[Figure]
Example 1

where C1 in ('a','b','c') and C2 between 'A' and 'Z'
       [Figure]  SearchCnd: RANGES(CS-CE)
                      [('a','A'),('a','Z')],[('b','A'),('b','Z')],[('c','A'),('c','Z')]

Note: The index component columns to be used are C1 and C2.
Example 2

where C1 in ('a','b','c')
       [Figure]  SearchCnd: RANGES(CS-CE)
                      [('a',MIN),('a',MAX)],[('b',MIN),('b',MAX)],
                      [('c',MIN),('c',MAX)]

Note: The index component columns to be used are C1 and C2.
Example 3

where (C1,C2) in (('a', 'a'), ('b', 'b'))
       [Figure]  SearchCnd: RANGES(CS-CE)
                      [('a', 'a',MIN),( 'a', 'a',MAX)] ,
                      [('b', 'b',MIN),( 'b', 'b',MAX)]

Note: The index component columns to be used are C1, C2, and C3.
RANGES by a set of narrowed ranges
RANGES(...) [set-of-narrowing-start-value,set-of-narrowing-end-value]:
Use this type of RANGES to search an index more than once using a set of narrowed ranges (work table created from table subqueries).
An example of a search condition is C1 =any(select C1 from T2).
[Figure]
Example 1

where C1 =any(select C1 from T2)
     [Figure]  SearchCnd: RANGES(CS-CE)
                    [((SUBQ(2)),MIN),((SUBQ(2)),MAX)]

Note: The index component columns to be used are C1 and C2.
Example 2

where (C1,C2) = any(select C1, C2 from T2)
     [Figure]  SearchCnd: RANGES(CS-CE)
                    [((SUBQ(2)),(SUBQ(2)),MIN),((SUBQ(2)),
                    (SUBQ(2)),MAX)]

Note: The index component columns to be used are C1, C2, and C3.
RANGES by multiple sets of narrowed ranges
RANGES(...) [narrowing-start-value-or-set-of-narrowing-start-value,narrowing-end-value-or-set-of-narrowing-end-value], ... ,[narrowing-start-value-or-set-of-narrowing-start-value,narrowing-end-value-or-set-of-narrowing-end-value]:
RANGES(...) [narrowing-start-value-or-set-of-narrowing-start-value,narrowing-end-value-or-set-of-narrowing-end-value], ... ,[narrowing-start-value-or-set-of-narrowing-start-value,narrowing-end-value-or-set-of-narrowing-end-value],...(Number of All Row Values : n)...,[narrowing-start-value-or-set-of-narrowing-start-value,narrowing-end-value-or-set-of-narrowing-end-value]:
Use this type of RANGES to search an index more than once using the result of combining sets of narrowed ranges (work table created from table subqueries) and the IN predicate.
An example of a search condition is C1 in ('a','b','c') and C2=any(Select C1 from T2).
When the total number of narrowing values is 256 or more, ...(Number of All Row Values : n)... is displayed, where n indicates the total number of the sets of narrowing ranges
Note that the ellipses (...) in ...(Number of All Row Values : n)... do not mean an omission.
[Figure]
Example 1

where C1 in ('a','b','c') and C2=any(Select C1 from T2)
     [Figure]  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)]

Note: The index component columns to be used are C1, C2, and C3.
Example 2

where C1 in ('a','b') and
(C2,C3)=any(Select C1,C2 from T2) and C4>'A'
     [Figure]  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)]

Note: The index component columns to be used are C1, C2, C3, and C4.

(3) Values used in a narrowed range

(a) Literal

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 9 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 the table below.

Table 17-3 Display example and search range when LIKE predicate is used to specify a pattern character string that results in right truncation

Data type of columnPattern character stringOutput example of narrowed range based on search conditionIndex search range#
Fixed length
(for example: char(5))
LiteralRANGE(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 ? parameterRANGE(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
(for example: varchar(5))
LiteralRANGE(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 ? parameterRANGE(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.

(b) ? (?-number)

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 the LIKE or SIMILAR predicate is used to specify a pattern character string that results in right truncation, ? is displayed, as shown in Table 17-3.

(c) SQL variable name

The utility displays the name of an SQL variable.

If the 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-3 Display example and search range when LIKE predicate is used to specify a pattern character string that results in right truncation.

(d) SQL parameter name

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 the 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-3 Display example and search range when LIKE predicate is used to specify a pattern character string that results in right truncation.

(e) USER

The utility displays the user when the value of the USER function is used to narrow the search condition.

(f) CURRENT_DATE

The utility displays of the current date when the value of the CURRENT_DATE function is used to narrow the search conditions.

(g) CURRENT_TIME

The utility displays the current time when the value of the CURRENT_TIME function is used to narrow the search conditions.

(h) CURRENT_TIMESTAMP(p)

The utility displays the current timestamp when the value of the CURRENT_TIMESTAMP(p) function is used to narrow the search conditions (p = 0, 2, 4, or 6).

(i) SUBQ(subquery-ID)

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.

(j) SUBQEX(query-expression-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.

(k) Set function

The utility displays set function when a set function is used to narrow the search condition.

(l) NULL

The utility displays NULL when using the null value to narrow the search condition.

(m) table-name.column-name

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.

(n) (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.

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

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.

(p) MIN

The utility displays MIN to search for the smallest index key value (for a character string, the smallest character encoding).

(q) MAX

The utility displays MAX to search for the largest index key value (for a character string, the largest character encoding).