(a) Substructure index (B-tree)
In XML type columns, a specific substructure can be made into a key, and an index can be defined with that value as its key value. Use this index to reduce the processing time for narrowing down the rows, if a predicate for a structure that defines a substructure index was specified in the XQuery expression of an XMLEXISTS predicate or an XMLQUERY function.
The following lists the predicates within an XQuery expression that can use a substructure index:
- Comparison expressions (=, !=, >, >=, <, <=, <>, eq, ne, lt, le, gt, ge) using the substructure that is the key
- The fn:contains function, fn:starts-with function, or fn:ends-with function with the substructure that is the key
For details about index usage conditions, see (4) Index usage conditions.
For details about searches using substructure indexes, see the HiRDB Version 9 UAP Development Guide.
(b) XML-type full-text search index (n-gram)
N-gram indexes for full-text searches against the value of XML types (IXXML) can be defined within XML type columns. Defining a XML-type full-text search index can reduce line narrowing processing time when a predicate that includes full-text search conditions such as character string matching is stated in the XQuery expression of a XMLEXISTS predicate.
Predicates in XQuery expressions that serve as conditions for XML-type full-text searches are:
- Perfectly matching character strings (xs:string type) (=)
- fn:contains function
- fn:starts-with function
- fn:ends-with function
- hi-fn:contains function
For details about index usage conditions, see (4) Index usage conditions.
For details about searches that use XML-type full-text search indexes, see the HiRDB Version 9 UAP Development Guide.
There are two types of input data when data is stored in tables. The data storage methods to use differ according to the type of input data.
- ESIS-B format, for converting XML language to XML insertion data
In this case, XML language is parsed using the XML conversion command (phdxmlcnv) or the XML conversion library (Java library), and XML insertion data (ESIS-B format) is generated. This ESIS-B format data is output in binary format and stored in a table using pdload or an INSERT statement.
For details about the XML conversion command and XML conversion library, see the manual HiRDB Version 9 XML Extension.
- XML language
In this case, XML language is converted to XML insertion data (ESIS-B format) using the database load utility (pdload) or the XMLPARSE function, and stored in a table. The conversion from XML language to ESIS-B format data is conducted with pdload or the XMLPARSE function. To perform the conversion to ESIS-B format with pdload, specify the -G option.
(a) Loading data
To use the database load utility (pdload) to store data in the table:
- Procedure
- Use the pdhold command to shut down the target data load RDAREAs (RDAREA01, RDAREA02 and LOBAREA01).
- Use the pdload command to load the input data file into the table.
- To use XML statements directly as the input data, specify the -G option.
- Because only the target data load table and index are stored in the RDAREAs, and because this is an initial load, select the no-log mode as the database update log acquisition mode.
- For the index creation method, select the batch index creation mode (the default value).
- Specify the constructor function and the data type information passed to the constructor function in a column structure information file.
- Set the format of the input data file to binary.
For details about the options of the pdload command, see the manual HiRDB Version 9 Command Reference.
- Because the pdload command is executed in the no-log mode, make a backup of the target data load RDAREAs. For details about how to make backups in units of RDAREAs, see the HiRDB Version 9 System Operation Guide.
- Use the pdrels command to release the target data load RDAREAs from shutdown status.
For details about these commands and utilities, and about how to verify the command and utility execution results, see the manual HiRDB Version 9 Command Reference.
- Supplemental notes
- Because the pdload command executes in the no-log mode, the RDAREAs to which data is being loaded must remain in shutdown status during steps 1 to 3.
- In the case of a falsification prevented table, when data loading is performed with the pdload command, the -d option cannot be specified.
- For details about error handling during batch index creation, see 6.6 Handling errors during batch index creation.
(b) Insertion of XML language
- Inserting ESIS-B format data into a table or updating ESIS-B format data in a table
- Specify an XML constructor function as the insertion value of an INSERT statement or the update value of an UPDATE statement, and set the generated ESIS-B format data in its argument.
- The following is an example of inserting an XML language (ESIS-B format) value stored in embedded variable bookinfo into BOOK_MANAGEMENT_TABLE.
- Example of XML language (ESIS-B format) insertion
INSERT INTO BOOK_MANAGEMENT_TABLE
VALUES ( 310494321, XML(:bookinfo AS BINARY(102400)))
- Inserting XML language into a table or updating XML language in a table
- Specify an XMLPARSE function as the insertion value of an INSERT statement or as the update value of an UPDATE statement, and set the XML language in its argument.
- The following is an example of inserting an XML language value stored in embedded variable bookdoc into BOOK_MANAGEMENT_TABLE.
- Example of XML language insertion
INSERT INTO BOOK_MANAGEMENT_TABLE
VALUES ( 310494321, XMLPARSE(DOCUMENT :bookdoc AS BINARY(32000)))
This section describes the two index usage conditions described in (2).
- Substructure index usage conditions
- When a substructure index is defined, the index is used when the substructure index usage conditions shown in the following table are met.
Table 6-4 Substructure index usage conditions
USING UNIQUE TAG specified? | Where XQuery specification is located | Operators or functions in XQuery | Substructure index usage conditions# |
---|
Yes | XMLEXISTS predicate | = | (a) 1, 2, 3 (b) 1, 2, 5, 6, 7, 8, 9 |
!=, >, >=, <, <=, <>, eq, ne, gt, ge, lt, le, fn:contains, fn:starts-with, fn:ends-with | (a) 1, 2, 3 (b) 1, 2, 4, 6, 7, 8, 9 |
XMLQUERY function | = | (c) 1, 2, 3 |
No | XMLEXISTS predicate | = | (a) 1, 2, 3 (b) 1, 3, 5, 6, 7, 8, 9 |
!=, >, >=, <, <=, <> | (a) 1, 2, 3 (b) 1, 3, 4, 6, 7, 8, 9 |
fn:contains fn:starts-with fn:ends-with | (a) 1, 2, 3 (b) 1, 3, 4, 6, 7, 8, 10 |
XMLQUERY function | = | (c) 1, 2, 3 |
- #
- (a): Indicates the usage conditions common to substructure indexes and XML-type full-text search indexes, as described in (a) below.
- (b): Indicates the usage conditions of substructure indexes as described in (b) below.
- (c): Indicates the usage conditions of substructure indexes pertaining to XQuery in the XMLQUERY function, as described in (c) below.
- The numbers refer to the item numbers in sections (a), (b), and (c) below.
- XML-type full-text search index usage conditions
- When an XML-type full-text search index is defined, the index is used when the usage conditions for XML -type full-text search indexes shown in the following table are met.
Table 6-5 XML-type full-text search index usage conditions
Where XQuery specification is located | Operators or functions in XQuery | Index usage conditions for XML-type full-text search# |
---|
XMLEXISTS predicate | fn:contains fn:starts-with fn:ends-with = | (a) 1, 2, 3 (d) 1, 2, 4, 5, 6, 7, 8 |
hi-fn:contains | (a) 1, 2, 3 (d) 1, 3, 4, 5, 6, 7, 8 |
- #
- (a): Indicates the usage conditions common to substructure indexes and XML-type full-text search indexes, as described in (a) below.
- (d): Indicates the usage conditions for XML -type full-text search indexes, as described in (d) below.
- The numbers refer to the item numbers in sections (a) and (d) below.
- Operators and functions that can use multiple indexes
- During a search with XQuery operators or functions that can use both substructure indexes and XML-type full-text search indexes, the index used for evaluation is determined by the operator or function. The table below shows which indexes are used with each operator or function.
Table 6-6 Indexes used to evaluate operators and functions that can use multiple indexes
Item No. | Operator or function | Index used in evaluation |
---|
1 | = | Substructure index |
2 | fn:contains | XML-type full-text search index |
3 | fn:starts-with | Substructure index |
4 | fn:ends-with | XML-type full-text search index |
- To specify the index to be used, specify SQL optimization for that index. For details, see SQL optimization specification for a used index in the manual HiRDB Version 9 SQL Reference.
HiRDB might sometimes not use these indexes because of the estimated access cost. Use the access path display utility (pdvwopt) to determine whether a search that uses an index will be conducted.
For predicates that can use substructure indexes or XML-type full-text search indexes that were specified within the XQuery query of a XMLEXISTS predicate, only a maximum of 255 indexes can be used in evaluation.
(a) Usage conditions common to substructure indexes and XML-type full-text search indexes
The following are usage conditions that are common to substructure indexes and XML-type full-text search indexes.
Indexes defined in the manner shown below are used in sample text.
create index idx1 on t1(c1) key using unique tag from '/root/elm1/@attr1' as varchar(10)
- Any XML query context item is specified in the XML query argument of a XMLEXISTS predicate.
Example
select c2 from t1
where xmlexists('/root/elm1[@attr1 eq "ABC"]'
passing by value c1,'DEF' as A)
Note: The underlined part is the XML query context item.
- All context item expressions (periods) specified in the XQuery query of the XMLEXISTS predicate are specified in the XQuery predicate.
Example
select c2 from t1
where xmlexists('/root/elm1[./@attr1 eq "ABC"]' passing by value c1)
Note: The underlined part is the context item expression specified in the XQuery predicate.
- All XQuery Boolean expressions (AND, OR) within the XQuery query of the XMLEXISTS predicate are specified in the XQuery predicate.
Example
select c2 from t1
where xmlexists('/root[elm1/@attr1 = "ABC" or elm1/@attr1 = "DEF"]'
passing by value c1)
Note: The underlined part is the XQuery Boolean expression (OR) specified in the XQuery predicate.
(b) Usage conditions for substructure indexes pertaining to the XQuery of the XMLEXISTS predicate
The following are usage conditions for substructure indexes pertaining to the XQuery of the XMLEXISTS predicate.
Items 1, 2, and 4 to 9 use indexes defined as shown below in the sample text.
create index idx1 on t1(c1) key using unique tag from '/root/elm1/@attr1' as varchar(10)
create index idx4 on t1(c1) key using unique tag from '/root/elm1/elm2' as varchar(10)
Items 3 and 10 use indexes defined as shown in the following sample text.
create index idx2 on t1(c1) key from '/root/elm1/@attr1' as varchar(10)
create index idx5 on t1(c1) key from '/root/elm1/elm2' as varchar(10)
- The index substructure specification matches the substructure path specified as a condition in the XQuery query of the XMLEXISTS predicate.
Example
select c2 from t1
where xmlexists('/root/elm1[@attr1 eq "ABC"]' passing by value c1)
Note: The underlined part is the matching substructure path.
- If the substructure index has a USING UNIQUE TAG specification, it is compared to the substructure path specified as a condition in the XQuery query of the XMLEXISTS predicate. This is done using a general comparison, value comparison, fn:contains function, fn:starts-with function, or fn:ends-with function.
Example
select c2 from t1
where xmlexists('/root/elm1[@attr1 eq "ABC"]' passing by value c1)
Note: The underlined part is an XQuery comparison expression (value comparison).
- If the substructure index does not have a USING UNIQUE TAG specification, it is compared to the substructure path specified as a condition in the XQuery query of the XMLEXISTS predicate. This is done using a general comparison, fn:contains function, fn:starts-with function, or fn:ends-with function.
Example
select c2 from t1
where xmlexists('/root/elm1[@attr1 = "ABC"]' passing by value c1)
Note: The underlined part is an XQuery comparison expression (general comparison).
- The following describes the case with general or value comparisons separately from the case with fn:contains, fn:starts-with, or fn:ends-with functions.
Using general or value comparisons
The items compared using a general or value comparison are the substructure path specified as a condition in the XQuery query of the XMLEXISTS predicate, and a single XQuery constant or XQuery variable.
Example
select c2 from t1
where xmlexists('/root/elm1[@attr1 >= "ABC"]' passing by value c1)
Note: The underlined part compares the substructure path to an XQuery constant.
Using the fn:contains, fn:starts-with, or fn:ends-with functions
The first argument of the fn:contains, fn:starts-with, or fn:ends-with function is the substructure path specified as a condition in the XQuery query of the XMLEXISTS predicate, while the second argument is a single XQuery constant or XQuery variable.
Example
select c2 from t1
where xmlexists('/root/elm1[fn:starts-with(@attr1,"ABC")]' passing by value c1)
Note: The underlined part compares a substructure path and an XQuery constant.
- Comparisons with = compare the substructure path, specified as a condition in the XQuery query of the XMLEXISTS predicate, to an XQuery sequence concatenation expression comprised of an XQuery constant or XQuery variable at or below the specification of the system common definition pd_apply_search_ats_num operand.
Example
select c2 from t1
where xmlexists('/root/elm1[@attr1 = ("ABC","DEF","GHI")]'
passing by value c1)
Note: The underlined part is an XQuery sequence concatenation expression comprised of an XQuery constant at or below the specification of the system common definition pd_apply_search_ats_num operand.
- The data type of the key value specified when the substructure index was defined is the same as, or can be converted to, the data type of the XQuery constant or the value expression passed to the XQuery variable in the XQuery query that is compared to the substructure path specified as a condition in the XQuery query of the XMLEXISTS predicate.
Example
select c2 from t1
where xmlexists('/root/elm1[@attr1 = "ABC"]' passing by value c1)
Note: The underlined part is string type data that is the same as the VARCHAR type that is the data type of the key value.
- The XQuery Boolean expression (OR) operand specified in the XQuery query of the XMLEXISTS predicate contains only the condition that allows use of substructure indexes.
Example
select c2 from t1
where xmlexists('/root[elm1/@attr1 = "ABC" or elm1/@attr1 = "DEF"]'
passing by value c1)
Note: The underlined part is the condition that allows use of all substructure indexes.
- If an XQuery variable was specified as a condition in items 4 or 5 as the value compared with the substructure path, the value expression passed to that XQuery variable is one of the following:
- Constant
- USER
- CAST specification whose value expression is a dynamic parameter, an SQL parameter, or an SQL variable
- Scalar subquery that does not make an external reference
Example 1
select c2 from t1
where xmlexists('/root/elm1[@attr1 eq $A]'
passing by value c1,'ABC' as A)
Note: The underlined part is the value expression passed to an XQuery variable in an XQuery query (constant).
Example 2
select c2 from t1
where xmlexists('/root/elm1[@attr1 eq $A]'
passing by value c1,cast(? as varchar(256)) as A)
Note: The underlined portion is the value expression passed to the XQuery variable in an XQuery query (CAST specification whose value expression is a dynamic parameter, an SQL parameter, or an SQL variable).
- The substructure path that is compared using a value or general comparison is specified in the following format. Alternatively, the substructure index has a USING UNIQUE TAG specification, and the substructure path that includes the first argument of the fn:contains, fn:starts-with, or fn:ends-with function is specified in the following format.
substructure-path:: = [XML-namespace-declaration]... substructure-path-expression
XML-namespace-declaration::={declare namespace prefix = XML-namespace-URI;
| declare default element namespace XML-namespace-URI;}
substructure-path-expression:: = [/step-expression...]/step-expression
step-expression:: = {[{child:: | attribute:: | @ }] qualifier-name | context-item-expression}
context-item-expression:: = period
period::= .
qualifier-name:: = [prefix:]local-name
Example using value or general comparison
The following three examples use value comparisons or general comparisons. In these examples, the index is used even when another value comparison or general comparison is specified in the value comparison operator eq. See items 2 and 3 for index usage conditions pertaining to whether USING UNIQUE TAG has been specified.
Example 1: @ or attribute:: and a qualifier name are specified in a step expression (@ and attribute:: have the same meaning).
select c2 from t1
where xmlexists('/root/child::elm1[@attr1 eq "ABC"]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
Example 2: child:: is specified in a step expression, or child:: is not specified and a qualifier name is specified.
select c2 from t1
where xmlexists('/root/child::elm1[child::elm2 eq "ABC"]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
Example 3: A context item expression is specified in a step expression (only when usage condition 2 applies to the value comparison).
select c2 from t1
where xmlexists('/root/child::elm1/elm2[ . eq "ABC"]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
Examples using XQuery functions
The following examples, 4 through 6, use XQuery functions. In these examples, the index is used even when an XQuery function other than fn:starts-with is specified.
Example 4: @ or attribute:: and a qualifier name are specified in a step expression (@ and attribute:: have the same meaning).
select c2 from t1
where xmlexists('/root/elm1[fn:starts-with(@attr1 ,"ABC")]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
Example 5: child:: is specified in a step expression, or child:: is not specified and a qualifier name is specified.
select c2 from t1
where xmlexists('/root/elm1[fn:starts-with(elm2 ,"ABC")]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
Example 6: A context item expression is specified in a step expression.
select c2 from t1
where xmlexists('/root/elm1/@attr1[fn:starts-with( . ,"ABC")]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
- With a substructure index that has no USING UNIQUE TAG specification, the substructure path that contains the first argument of the fn:contains, fn:starts-with, or fn:ends-with function is specified in the following format. The first argument is also specified in the following step expression end format.
substructure-path:: = [XML-namespace-declaration]... substructure-path-expression
XML-namespace-declaration::={declare namespace prefix = XML-namespace-URI;
| declare default element namespace XML-namespace-URI;}
substructure-path-expression:: = [/step-expression...]/step-expression-end
step-expression:: = {[{child:: | attribute:: | @ }] qualifier-name | context-item-expression}
step-expression-end:: = {{attribute:: | @ } qualifier-name | context-item-expression}
context-item-expression:: = period
period::= .
qualifier-name:: = [prefix:]local-name
In the following examples, the index is used even when another XQuery function is specified in the fn:starts-with function.
Example 1: @ or attribute:: and a qualifier name are specified in a step expression end (@ and attribute:: have the same meaning).
select c2 from t1
where xmlexists('/root/elm1[fn:starts-with(@attr1 ,"ABC")]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
Example 2: A context item expression is specified in a step expression end.
select c2 from t1
where xmlexists('/root/elm1/@attr1[fn:starts-with( . ,"ABC")]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
(c) Usage conditions for substructure indexes pertaining to the XQuery of the XMLQUERY function
This section shows usage conditions for substructure indexes pertaining to the XQuery of the XMLQUERY function.
An index defined as follows is used in the examples of items 1 and 2.
create index idx1 on t1(c1) key using unique tag from '/root/elm1' as varchar(10)
- The SQL code fulfills all the following conditions.
- It is a SELECT statement or code from an INSERT statement to a SELECT statement.
- There is a single main query SELECT expression.
- That main query SELECT expression is XMLQUERY (the XMLSERIALIZE argument can be XMLQUERY).
- That XMLQUERY XML query argument is a single XML query variable, and the value expression passed to the variable is XMLAGG.
- The XMLAGG argument specified in that XML query variable is an independent column specification.
- The code specifies no table join.
- The code specifies no set operation.
- The code specifies no subqueries.
- The code specifies no set function.
- The code specifies no GROUP BY clause.
- The code specifies no HAVING clause.
- The code specifies a WHERE clause, but specifies AND 255 times or fewer.
See example 2 for SQL examples.
- The XQuery specified in the XMLQUERY function satisfies all the following conditions.
a. It is a path expression that uses an XML query variable as a route.
b. There is only one XQuery predicate specification at the outermost level.
c. A comparison is performed between the predicate of b. and =, using a general comparison.
d. The comparison of c. is a path expression comparison whose route is an XML column specific substructure and an XQuery variable.
Example
select xmlserialize(xmlquery('$VAR1/root[elm1 = $VAR1/root[elm2 = "ABC"]/elm1]/elm1'
passing by value xmlagg(c1) as VAR1 empty on empty) as varchar(32000)) from t1
Note: The underlined part is the location that matches the above conditions.
- A substructure index is defined with the same data type as the path expression in 2d. whose route is an XML column specific substructure and an XQuery variable (these indexes can be identical).
(d) Usage conditions for XML-type full-text search indexes
The following are usage conditions for XML -type full-text search indexes.
- A full-text search index is defined in the XML type column that is the search target.
Example
create index idx3 using type ixxml on t2(c1) in (LOB1)
- The substructure path that contains the first argument of the fn:contains, fn:starts-with, or fn:ends-with function is specified in the following format. The first argument is specified in the following text step expression end or attribute step expression end format. Alternatively, the substructure path that is the subject of an = comparison is specified in the following format, and the substructure path specified in the XQuery predicate is specified in the following text step expression end or attribute step expression end format.
substructure-path:: = [XML-namespace-declaration]... substructure-path-expression
XML-namespace-declaration:: = {declare default element namespace
"http://www.w3.org/XML/1998/namespace";
|declare namespace prefix = XML-namespace-URI;#
|declare default element namespace XML-namespace-URI;#}
substructure-path-expression:: = [{/ | //#}step-expression ...]
{/ | //#}{text-step-expression | attribute-step-expression-end}
step-expression:: = {[child::] name-test | context-item-expression}
text-step-expression:: = [{child::|descendant::}]text-test
/text-step-expression-end
text-step-expression-end:: = context-item-expression
attribute-step-expression-end:: = {{attribute:: | @} name-test
| [{attribute:: | @}] attribute-test}
context-item-expression:: = period
period:: = .
name-test:: = {qualifier-name|*#|prefix:*#|*:local-name#}
qualifier-name:: = [prefix:]local-name
#: Can be specified if the HiRDB XML Extension version is 08-04 or later.
The index is used even if another XQuery function is specified in the fn:contains function in the following example.
Example 1: @ or attribute:: and a name test are specified in the first argument (@ and attribute:: have the same meaning).
select c2 from t2
where xmlexists('/root/child::elm1[fn:contains(@attr1,"ABC")]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
Example 2: @ or attribute:: and an attribute test are specified in the first argument (@ and attribute:: have the same meaning).
select c2 from t2
where xmlexists('/root/child::elm1[fn:contains(@attribute(),"ABC")]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
Example 3: Only an attribute test is specified in the first argument.
select c2 from t2
where xmlexists('/root/child::elm1[fn:contains(attribute(),"ABC")]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
Example 4: A context item expression is specified in the first argument.
select c2 from t2
where xmlexists('/root/child::elm1/text()[fn:contains( . ,"ABC")]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
- The substructure path that contains the first argument of the hi-fn:contains function is specified in the following format. The first argument is also specified in the text step expression, text step expression end, or attribute step expression end format shown below. Also, the version of HiRDB XML Extension is 08-04 or later.
substructure-path:: = [XML-namespace-declaration]... substructure-path-expression
XML-namespace-declaration:: = {declare namespace prefix = XML-namespace-URI;
|declare default element namespace XML-namespace-URI;}
substructure-path-expression:: = [{/ | //}step-expression ...]
{/ | //}{text-step-expression | attribute-step-expression-end}
step-expression:: = {[child::] name-test | context-item-expression}
text-step-expression:: = [{child::|descendant::}]text-test
[/text-step-expression-end]
text-step-expression-end:: = context-item-expression
attribute-step-expression-end:: = {{attribute:: | @} name-test
| [{attribute:: | @}] attribute-test}
context-item-expression:: = period
period:: = .
name-test:: = {qualifier-name | * | prefix:* | *:local-name}
qualifier-name:: = [prefix:]local-name
Example 1: A text test is specified in the first argument.
select c2 from t2
where xmlexists('/root/elm1[hi-fn:contains(text(),"""ABC AND DEF""")]'
passing by value c1)
select c2 from t2
where xmlexists('/root/elm1[hi-fn:contains(descendant::text(),"""ABC AND DEF""")]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
Example 2: A text test and a context item expression are specified in the first argument.
select c2 from t2
where xmlexists('/root/elm1[hi-fn:contains(text()/.,"""ABC AND DEF""")]'
passing by value c1)
select c2 from t2
where xmlexists('/root/elm1[hi-fn:contains(descendant::text()/.,"""ABC AND DEF""")]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
Example 3: A context item expression is specified in the first argument.
select c2 from t2
where xmlexists('/root/elm1/text()[hi-fn:contains( . ,"""ABC AND DEF""")]'
passing by value c1)
select c2 from t2
where xmlexists('/root/elm1/ descendant::text()[hi-fn:contains( . ,"""ABC AND DEF""")]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
Example 4: @ or attribute:: and a name test are specified in the first argument (@ and attribute:: have the same meaning).
select c2 from t2
where xmlexists('/root/elm1[hi-fn:contains(@attr1,"""ABC AND DEF""")]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
Example 5: @ or attribute:: and an attribute test are specified in the first argument (@ and attribute:: have the same meaning).
select c2 from t2
where xmlexists('/root/elm1[hi-fn:contains(@attribute(),"""ABC AND DEF""")]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
Example 6: Only an attribute test is specified in the first argument.
select c2 from t2
where xmlexists('/root/elm1[hi-fn:contains(attribute(),"""ABC AND DEF""")]'
passing by value c1)
Note: The underlined part is the substructure path that matches the above format.
- The length of the character string specified in the XQuery query of the XMLEXISTS predicate is 32,000 bytes or less.
Example
select c2 from t2
where xmlexists('/root/elm1[fn:contains(@attr1,"ABCDEF")]'
passing by value c1)
Note: The underlined part is a character string of 32,000 bytes or less.
- The value compared to the substructure path specified in the format of 2 or 3 is a character string XQuery constant.
Example
select c2 from t2
where xmlexists('/root/child::elm1[fn:contains(@attr1,"ABC")]'
passing by value c1)
Note: The underlined part is the character string XQuery constant.
- The sum of the length of the /, //, @, and local name specified in the substructure path expression of the XQuery query of the XMLEXISTS predicate (except when the first character of the substructure path expression is /) is 1,024 bytes or less.
Example
select c2 from t2
where xmlexists('/root/elm1[fn:contains(@attr1,"ABCDEF")]'
passing by value c1)
Note: The underlined part is a substructure path expression of 1,024 bytes or less.
- There is no more than one // specified in the substructure path expression specified in the XQuery query of the XMLEXISTS predicate.
Example
select c2 from t2
where xmlexists('/root/elm1[fn:contains(@attr1,"ABCDEF")]'
passing by value c1)
Note: The underlined part is the substructure path expression with no more than one // specification.
- None of the following plug-in options has been specified in the full-text search index defined in the XML type column that is the search target.
- DELcode=file-name
- NOindex=file-name
- ENGLISH
- ENGLISH_STANDARD
For details about plug-in options, see the manual HiRDB Version 9 XML Extension.
Example
create index idx6 using type ixxml on t2(c1) in (LOB1)
PLUGIN'SAMECASE=ON,SAMEWIDE=ON,SAMEY=ON,SAMED=ON,DELcode=ON'
Note: The underlined part is the full-text search index that specified only options that can use the index.
(e) If no index is used
In the following situations, no substructure index or XML-type full-text search index is used.
In items 1 and 3 below, an index defined as follows is used in the examples.
create index idx1 on t1(c1) key using unique tag from '/root/elm1/@attr1' as varchar(10)
In items 2 and 4 below, an index defined as follows is used in the examples.
create index idx3 using type ixxml on t2(c1) in (LOB1)
- If an XQuery that evaluates whether '/root[elm1/@attr1' is "ABC" or "DEF" is specified in the XQuery query of the XMLEXISTS predicate
In the following examples, not all of the XQuery Boolean expressions (AND, OR) in the XQuery query of the XMLEXISTS predicate are specified in the XQuery predicate. Consequently, no index is used. With this specification method, XQuery Boolean expressions are not specified directly in the XQuery query of the XMLEXISTS predicate argument (except within the XQuery predicate), so the XQuery query result will always be a Boolean value of TRUE or FALSE. For this reason, the XQuery query result is not a NULL sequence, so the XMLEXISTS predicate result is always TRUE and will not be the intended result (the XMLEXISTS predicate is only FALSE when the XQuery query result is a NULL sequence; otherwise, it is TRUE).
Example: Before change
select c2 from t1
where xmlexists('/root[elm1/@attr1 = "ABC"] or /root[elm1/@attr1 = "DEF"]'
passing by value c1)
Note: The underlined part is the XQuery Boolean expression (OR) not specified in the XQuery predicate.
If the index is changed as follows, it will be used.
Example: After change
select c2 from t1
where xmlexists('/root[elm1/@attr1 = "ABC" or elm1/@attr1 = "DEF"]'
passing by value c1)
Note: The underlined part is the XQuery Boolean expression (OR) specified in the XQuery predicate.
- When an XQuery that searches text nodes under '/root/elm1' is specified in an XQuery query of an XMLEXISTS predicate
In the following example, the text step expression end of the substructure path that includes the first argument of the contains function does not match the format shown in item 2 of (4)(c), so the index is not used.
Example: Before change
select c2 from t2
where xmlexists('/root[fn:contains(elm1/text(),"ABC")]' passing by value c1)
Note: The underlined part is a substructure path that does not match the format shown in item 2 of (4)(c).
If the index is changed as follows, it will be used.
Example: After change
select c2 from t2
where xmlexists('/root/elm1/text()[fn:contains( . ,"ABC")]'
passing by value c1)
Note: The underlined part is a substructure path that matches the format shown in item 2 of (4)(c).
- If an XQuery that evaluates whether '/root/elm1/@attr1/' is "ABC" is specified in the XQuery query of the XMLEXISTS predicate
In the following example, the substructure path expression of the substructure path specified as a condition in the XQuery query of the XMLEXISTS predicate does not match the format shown in item 9 of (4)(b). Consequently, no index is used.
Example: Before change
select c2 from t1
where xmlexists('$A/root/elm1[@attr1 eq "ABC"]'
passing by value c1,c1 as A)
Note: The underlined part is a substructure path that does not match the format shown in item 9 of (4)(b).
If the index is changed as follows, it will be used.
Example: After change
select c2 from t1
where xmlexists('/root/elm1[@attr1 eq "ABC"]'
passing by value c1,c1 as A)
Note: The underlined part is a substructure path that matches the format shown in item 9 of (4)(b).
- If many XMLEXISTS predicates are joined by Boolean operators (OR, AND)
Due to calculated access costs, HiRDB judges that not using the index is the optimal access path, and the index is not used to evaluate XMLEXISTS predicates. If the hi-fn:contains function was specified, an evaluation by the index alone is impossible, so an SQL error results.
Example: Before change
select c2 from t2
where xmlexists('/root/elm1[hi-fn:contains(text(),"""01ABC""")]'
passing by value c1)
or xmlexists('/root/elm1[hi-fn:contains(text(),"""02ABC""")]'
passing by value c1)
...(Omitted)...
or xmlexists('/root/elm1[hi-fn:contains(text(),"""30ABC""")]'
passing by value c1)
Note: The underlined part is the condition that specifies 30 XMLEXISTS predicates that use the index if they are specified alone.
When the used index specifies SQL optimization as shown below, the index is used.
Example: After change
select c2 from t2 with index(idx3,idx3)
where xmlexists('/root/elm1[hi-fn:contains(text(),"""01ABC""")]'
passing by value c1)
or xmlexists('/root/elm1[hi-fn:contains(text(),"""02ABC""")]'
passing by value c1)
...(Omitted)...
or xmlexists('/root/elm1[hi-fn:contains(text(),"""30ABC""")]'
passing by value c1)
Note: The underlined part is an index SQL optimization specification that specifies an index of the type required for execution using multiple indexes.