Restrictions on indexes over XML data
Indexing over XML data is subject to restrictions, including data type support restrictions, concurrency level restrictions, XML list elements restrictions, and index compression restrictions.
- Concurrency levels
- Support for some concurrency levels is restricted during processing
of XML columns and associated indexes. The following table specifies
what concurrency levels are supported.
Table 1. Supported concurrency levels for a nonpartitioned table with at least one XML column Command Concurrency level Supported REORG INDEXES ALL FOR TABLE Command clause: ALLOW [NO | READ | WRITE] ACCESS Yes. REORG TABLE Command clause: ALLOW [READ | NO] ACCESS Yes. An index over XML data might exist. REORG TABLE INPLACE (at least one index over XML data exists on the table) Command clause: ALLOW [READ | WRITE] ACCESS No. REORG TABLE RECLAIM EXTENTS Command clause: ALLOW [NO | READ | WRITE] ACCESS Yes. Table 2. Supported concurrency levels for a partitioned table with nonpartitioned indexes and at least one XML column Command Concurrency level Supported REORG INDEX (on a nonpartitioned index over XML data) Command clause: ALLOW [NO | READ | WRITE] ACCESS Yes. REORG INDEXES ALL FOR TABLE CLEANUP|RECLAIM EXTENTS Command clause: ALLOW [NO | READ | WRITE] ACCESS Yes. REORG INDEXES ALL FOR TABLE REBUILD1 Command clause: ALLOW [READ | WRITE] ACCESS No. REORG TABLE Command clause: ALLOW NO ACCESS (for a partitioned table, the only supported access is ALLOW NO ACCESS) Yes. REORG TABLE INPLACE Command clause: ALLOW [NO | READ | WRITE] ACCESS No. For a partitioned table, the INPLACE parameter is not supported. REORG TABLE RECLAIM EXTENTS Command clause: ALLOW [NO | READ | WRITE] ACCESS Yes. Note:- The default behavior rebuilds the index. If you do not specify the REBUILD clause, the index is rebuilt.
Table 3. Supported concurrency levels for a partitioned table with partitioned indexes and XML columns Command Concurrency level Supported REORG INDEXES ALL FOR TABLE Command clause: ALLOW NO ACCESS Yes. REORG INDEXES ALL FOR TABLE CLEANUP|RECLAIM EXTENTS Command clause: ALLOW [READ | WRITE] ACCESS Yes. REORG INDEXES ALL FOR TABLE REBUILD ON DATA PARTITION1 Command clause: ALLOW [READ | WRITE] ACCESS Yes. REORG INDEXES ALL FOR TABLE REBUILD1 Command clause: ALLOW [READ | WRITE] ACCESS No. REORG TABLE Command clause: ALLOW NO ACCESS (for a data partitioned table, the only supported access is ALLOW NO ACCESS) Yes. REORG TABLE INPLACE Command clause: ALLOW [NO | READ | WRITE] ACCESS No. For a data partitioned table, the INPLACE parameter is not supported. REORG TABLE RECLAIM EXTENTS Command clause: ALLOW [NO | READ | WRITE] ACCESS Yes. Note:- The default behavior rebuilds the index. If you do not specify the REBUILD clause, the index is rebuilt.
See the CREATE INDEX statement and REORG INDEX/TABLE command for information about the clauses and options.
For multidimensional clustering (MDC) and insert time clustering (ITC) tables, online index reorganization (rebuild) with ALLOW WRITE ACCESS is not supported.
- XML list elements
- You cannot index list data type nodes. If a node is qualified by xmlpattern-clause and an XML schema exists that specifies that the node is a list data type, you cannot index the node. Issuing a CREATE INDEX statement on a list data type node returns an error (SQLSTATE 23526, SQLCODE -20306). Issuing INSERT and UPDATE statements also returns an error (SQLSTATE 23525, SQLCODE -20305).
- UNIQUE index over XML data
- In a partitioned database environment, the following rules apply to a table with one or more XML columns:
- A table with a distribution key cannot have a unique index over XML data.
- A unique index over XML data is supported only on a table without a distribution key in a single-partition database.
- If a unique index over XML data exists on a table, you cannot alter the table cannot to add a distribution key.
For a partitioned table, a unique partitioned index over XML data is not supported. If you try to create such index, you will receive error message SQL20303N (SQLSTATE=42990).
Creating indexes on XML columns is also subject to restrictions placed on the native XML data store overall.