(a) Function

Defines an index for a column of type XML, with a specified substructure as the key.

(b) Usage privileges

Owner of a table

A user can define an index for a table they own in the public user RDAREA.

Owner of a table with private user RDAREA usage privileges

This user can define an index for a table they own in the private user RDAREA for which they have usage privileges.

(c) Format (Define substructure index)

 
CREATE [UNIQUE] INDEX [authorization-identifier.]index-identifier
   ON [authorization-identifier.]table-identifier (column-name [{ASC|DESC}])
   [IN{RDAREA-name
     |(RDAREA-name)
     |((RDAREA-name)[, (RDAREA-name)]...)
     |RDAREA-specification-for-the-matrix-partitioning-index}]
   KEY [USING UNIQUE TAG]FROM substructure-specification AS data-type
   [index-option]...
 
RDAREA-specification-for-the-matrix-partitioning-index ::=RDAREA-specification-for-two-dimensional-storage
RDAREA-specification-for-two-dimensional-storage::=
       (matrix-partitioning-RDAREAs-list [, matrix-partitioning-RDAREAs-list]...)
matrix-partitioning-RDAREAs-list ::= (RDAREA-name [, RDAREA-name]...)
substructure-specification ::= character-string-literal
index-option ::={PCTFREE=percentage-of-unused-space
             |UNBALANCED SPLIT
             |EMPTY}
 

(d) Operands

For rules and other details about operands other than the KEY clause, see CREATE INDEX Format 1 (Define index) in Chapter 3.

• KEY [USING UNIQUE TAG] FROM substructure-specification AS data-type

USING UNIQUE TAG

Specifies to uniquely determine the substructure in substructure-specification from among the values in a single XML type column. However, if the substructure is an XML attribute, XML elements that have that XML attribute must be uniquely determined.

If the UNIQUE operand immediately follows CREATE, this operand is assumed.

substructure-specification ::= character-string-literal

Specifies that the substructure to be used as the key is expressed as a character string literal in the format of a substructure path.

The substructure path format is shown below. Note that all keywords in the substructure path are specified in lower case.

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 ::= [@] qualified-name qualified-name ::= [prefix:]local-name

• declare namespace prefix = XML-namespace-URI;
  If the substructure path expression contains qualified names with a prefix, this operand declares the XML namespace of the prefix. The same prefix cannot have multiple XML namespace declarations.
  prefix
  Specifies the prefix of the qualified name in the substructure path expression.
  This operand is case-sensitive.
  XML-namespace-URI
  The URI of the XML namespace associated with the prefix is specified, enclosed in double quotation marks (") or single quotation marks (').
  This operand is case-sensitive.
  
• declare default element namespace XML-namespace-URI;
  Declares the default XML namespace for the substructure path expression. Any unprefixed qualified names in the substructure path expression will be searched for in the XML namespace declared here.
  You cannot declare multiple default XML namespaces in a substructure path.
  If omitted, the default XML namespace URI http://www.w3.org/XML/1998/namespace is assumed.
  XML-namespace-URI
  Specifies the URI of the XML namespace for any unprefixed qualified names, enclosed in double quotation marks (") or single quotation marks (').
  This operand is case-sensitive.
  
• substructure-path-expression
  Specifies the path of the substructure that is issued as the key.
  The following table describes the meaning of each element in the specification:
  

  Element	Meaning
  Leading forward slash (/)	Indicates that the element in the step expression after the forward slash is the topmost XML element of the XML type value.
  Middle forward slash (/)	Indicates that the XML element or attribute in the step expression after the forward slash is a child element or an attribute of the XML expression in the step expression specified before the forward slash.
  step-expression	Indicates an XML element or attribute. However, a step expression referring to an XML attribute can be specified only at the end of the expression.
  @	Indicates that the qualified name after the at mark (@) is the name of an XML attribute. A qualified name without @ refers to an XML element.
  qualified-name	Indicates the name of an XML element or XML attribute.
  prefix:	Indicates that the immediately following local-name is a local name within the XML namespace indicated by the prefix. The prefix that is bound to the XML namespace is specified in the XML namespace declaration. If prefix: is omitted, local-name is the name of the default XML namespace.
  local-name	Indicates the name of an XML element or attribute in the XML namespace.

  The following table provides examples of substructure specifications:
  

  No.	Example	Meaning
  1	'/bookinfo/category'	The key is the XML element category, which is a child of bookinfo, the topmost XML element.
  2	'/bookinfo/@book_id'	The key is the attribute book_id, which is a child of bookinfo, the topmost XML element.
  3	'declare namespace b="http://www.foo.co.jp/bookinfo"; /b:bookinfo/b:category'	The key is the XML element category, which is a child of bookinfo, the topmost XML element. However, the names of the XML elements bookinfo and category are located in the XML namespace whose URI is http://www.foo.co.jp/bookinfo.
  4	'declare default element namespace "http://www.foo.co.jp/bookinfo"; /bookinfo/category'	Same as No. 3.

  data-type
  Specifies the data type of the key value.
  The following data types can be specified:
  INTEGER
  DECIMAL
  FLOAT
  VARCHAR (cannot include a character set specification)
  The value of the key is determined after the values of the typed-value properties of the nodes in the XQuery data model substructure that is used as the key are converted to the SQL data type specified here.
  When an index is defined, if the values stored in the column cannot be converted to the SQL data type specified for the substructure that is used as the key, the index cannot be defined. Furthermore, when inserting or updating values in the column that defines the index, if the values to be inserted or updated cannot be converted to the SQL data type specified for the substructure that is used as the key, the insert or update operation cannot be performed.
  For details about the value of the typed-value property for each kind of node, see 1.15.1 XQuery data model. The following table indicates the relationship between the values of the typed-value properties of the XQuery data types and their corresponding SQL data types.
  

  Table 1-41 Correspondence between XQuery data types and SQL data types

  XQuery data type of typed-value properties of nodes in an XQuery data model substructure	SQL data type
  	INTEGER	DECIMAL	FLOAT	VARCHAR
  xs:untypedAtomic	C	C	C	C
  xs:int	E	NC	NC	NC
  xs:decimal	NC	E	NC	NC
  xs:double	NC	NC	E	NC
  xs:string	NC	NC	NC	E
  Other	NC	NC	NC	NC

Legend:

E: Equivalent types, so the key value is used without conversion.

C: When the value of a typed-value property is recognized as a character string, if the character string representation corresponds to an SQL data type, the value is converted and becomes the key value. In this case, it may produce different results than if no index was defined. Otherwise it cannot be converted.

NC: Cannot be converted.

(e) Common rules

1. A maximum of 255 indexes can be created for a table.
2. An index can be defined for columns that contain the null value, columns that do not have any rows, and columns that include XML type values that are not part of the specified substructure.
3. A substructure index can be specified only for XML type columns.
4. The length of the key value must satisfy the following formula:
   Length of key value
   MIN((page size of index storage RDAREAs 2) - 1242, 4036)
   The following table indicates the length of each key value.
   

   Table 1-42 Lengths of key values

   Data type		Length of key value
   INTEGER		4
   DECIMAL[(m[, n])]		m 2 + 1
   FLOAT		8
   VARCHAR	Actual data length is 255 bytes or less	n1 + 1
   	Actual data length is 256 bytes or more	n1 + 2

   Legend:

   m, n: Positive integers

   n1: Actual data length

5. Only one index can be defined with the same substructure of the same column being used as the key.
6. When the substructure in which the index is defined is evaluated in XQuery, it is evaluated as a value typed in the XQuery data type that corresponds to the SQL data type specified in AS data-type. The following table indicates the XQuery data type evaluated from each of these SQL data types.
   

   Table 1-43 XQuery data type when an SQL data type is evaluated in XQuery

   SQL data type	XQuery data type
   INTEGER	xs:int
   DECIMAL	xs:decimal
   FLOAT	xs:double
   VARCHAR	xs:string

   If the XQuery data type of the typed-value property of the node in the XQuery data model node substructure in which the index is defined is the xs:untypedAtomic type, the XQuery data type evaluated in XQuery will differ depending on whether an index corresponding to that substructure is defined. Therefore, the results may differ if the index is deleted.
   
7. If a procedure and a trigger are already defined for the table in which you are defining an index, the index information in the SQL object is lost (invalidated) and the trigger cannot be executed. Because the affected procedure or trigger cannot be executed from another procedure, you must re-create the SQL object.
8. The same index option cannot be specified more than once.
9. CREATE INDEX cannot be executed from a Java procedure if the execution invalidates the index information of the SQL object being executed.
10. You cannot mix RDAREAs that use the inner replica facility with those that do not using the facility in the index storage RDAREAs. When specifying an RDAREA that use the inner replica facility, specify the name of the original RDAREA.
11. For details about execution conditions affecting a CREATE INDEX statement that uses the inner replica facility, see the HiRDB Version 9 Staticizer Option Description and User's Guide.
12. A maximum of 500 indexes can be stored per RDAREA.

(f) Notes

1. When a value in an indexed column is updated, the associated index is also updated.
2. The CREATE INDEX statement cannot be specified from an X/Open-compliant UAP running under OLTP.
3. When an index that specifies the EMPTY option is defined, it must be re-created with the database reorganization utility; for details, see the manual HiRDB Version 9 Command Reference.
4. Using the CREATE INDEX statement to create an index for a table containing a large quantity of data can require substantial processing time. Before executing the CREATE INDEX statement, set a timer for monitoring purposes, as follows:
   
   • System definition pd_watch_time
     Either specify 0 or omit.
     
   • Client environment definition PDCWAITTIME
     If you can estimate how long CREATE INDEX will take to execute based on the amount of data and past experience, specify a non-zero value with some extra leeway.
     If you cannot estimate how long it will take to execute, specify 0 or omit.
     
5. When defining an index on a table with rows partitioned across servers on a HiRDB/Parallel Server, if the RDAREA for tables or the RDAREA for the specified index are in a blocked state, the RDAREA state changes to lock-release pending. At this time, even if the lock-release waiting time reaches the value specified in the pd_lck_wait_timeout operand of the system definition and times out, it may not immediately return an error. To avoid this situation, cancel the blocked state of the RDAREA for tables or the RDAREA for the specified index before executing the CREATE INDEX statement.

(g) Example

1. For the bookinfo column in BOOK_MANAGEMENT_TABLE, define the index INDX1 whose key is converted into VARCHAR type from the XML element category, which is a child of the XML element bookinfo.
   

    
     CREATE INDEX INDX1 ON BOOK_MANAGEMENT_TABLE(bookinfo)
       KEY FROM '/bookinfo/category' AS VARCHAR (100)
    

2. For the bookinfo column in BOOK_MANAGEMENT_TABLE, define the index INDX1 whose key is converted into INTEGER type from the XML element price, which is a child of the XML element bookinfo. The XML element price, which is a child of the XML element bookinfo, must be uniquely determined in the values of each row.
   

    
     CREATE INDEX INDX2 ON BOOK_MANAGEMENT_TABLE(bookinfo)
       KEY USING UNIQUE TAG FROM '/bookinfo/price' AS INTEGER
    

3. For the bookinfo column in BOOK_MANAGEMENT_TABLE, define the index INDX3 whose key is converted into INTEGER type from the value of the XML attribute book_id, which is a child of the XML element bookinfo. The XML element bookinfo is uniquely determined among the values of each row, and the key value must be different in every row.
   

    
     CREATE UNIQUE INDEX INDX3 ON BOOK_MANAGEMENT_TABLE(bookinfo)
       KEY FROM '/bookinfo/@book_id' AS INTEGER