Databases Reference
In-Depth Information
The following example shows the statement to create an XML index on XML
data, which defines an index on all movie titles in all documents in the XML
column
movieDetails
as shown:
CREATE INDEX title_idx on movies(moviedetails) generate key using
xmlpattern '/movie/title' as sql varchar(50);
The
xmlpattern
is a path, which identifies the XML nodes to be indexed. It is
called
xmlpattern
and not
xpath
because only a subset of the XPath language is
allowed in index definitions. (Wildcards //,*, and namespaces are allowed, but
XPath predicates such as /a/b[c=5] are not supported).
Since we do not require a single XML schema for all documents in an XML
column, DB2 might not know which data type to use in the index for a given
xmlpattern. Thus, you must specify the data type explicitly in the
as sql <
type
>
clause. The following types can be used:
VARCHAR(
n
)
For nodes with values of a known maximum length.
VARCHAR HASHED
For nodes with values of arbitrary length. In this case, the index contains hash
values of the actual strings. Such an index can be used for equality
predicates but not for range predicates.
The length of the data type VARCHAR specified when creating the index on
an XML node value imposes a restriction on the length of the node value for
XML data, which can be inserted into the table.
For example, if the index is created on a node with the data type as
VARCHAR(5), inserting the XML data with the same node value with more
than 5 characters throws an error.
Tip:
Creating an index with the data type VARCHAR HASHED does not
impose any restriction on the node value.
DOUBLE
For nodes with any numeric type.
DATE and TIMESTAMP
For nodes with corresponding XML values.
Search WWH ::
Custom Search