Its time to get rid of some backlog about Oracle 11gR2 XML DB features. Ones of them is about the new Structured XMLIndex or also called XMLIndex Structured Component. So in Oracle 11gR2 you have the ability to use a “unstructured” XMLIndex (UXI) and a “structured” XMLIndex (SXI). The use cases for these global index types specially designed for the XML DB realm are described in the Oracle XMLDB Developers Guide for 11gR2 and in short are show in the following picture.
Click on the picture to enlarge
Also see my SlideShare presenation called “Oracle Database 11g Release 2 – XMLDB New Features” for Oracle Open World 2009.
Some general info/rules about 11gR2 XMLIndex use (in no specific order):
- There can be only ONE XMLIndex domain index per xmltype table column or xmltype table.
- In Oracle 11gR2 you have to possibilities regarding creating an XMLIndex
- A structured one (new in Oracle 11gR2), also called XMLIndex Structured Component (or more briefly) or referred to as a “structured XMLIndex” (SXI). The structured XMLIndex has one or more CONTENT TABLES
- An unstructured one (the XMLIndex introduced in Oracle 11gR1), also called XMLIndex Unstructured Component (or more briefly) or referred to as a “unstructured XMLIndex” (UXI). The unstructured XMLIndex makes use of one PATH TABLE.
- An unstructured XMLIndex should be used if your XML contains structured and semi-structured components
- A structured XMLIndex should be used if your XML is very structured in nature, almost “relational” in look and feel and well designed
- Due to the fact that you can have only one XMLIndex domain index per user, combinations of SXI and UXI are possible but only by building one “on top of the other”
- You can build multiple structured XMLIndexes (SXI) on top of each other via the “GROUP” keyword
- If you build a XMLIndex (SXI or UXI) ALWAYS TEST your select statement before you build the actual XMLIndex, while using XMLTABLE (SXI) or XPATH expressions. You won’t be the first to build an XMLIndex path table or content table structures based on a empty data result set… Be aware of XML case-sensitivity and XML namespaces…
- If you combine a structured and unstructured XMLIndex than there will be only one SYNC option available: “ALWAYS”. Probably due to, an eductated guess here, that the content tables must be in sync due to that they depend on each other and therefore also explicitly valid for� combinations of SXI and UXI indexes.
- For partitioning the LOCAL parameter is mandatory so that automatically locally partitioned XMLIndex structured will be created based on the used partitioning option in the XMLType table or column
- A structured XMLIndex is based on the XMLTABLE operator and can be nested only once (via result XMLTYPE data set that� feeds a second XMLTABLE operator).
- Package DBMS_XMLINDEX can be used for adding group(s), deleting groups, maintenance of XMLIndexes, add a structured XMLIndex to an already existing unstructured XMLIndex
- You are in 11gR2 not allowed to create an XMLIndex on a HASH partitioned XMLType table or column
- If you don’t define a GROUP in your structured XMLIndex statement, a default GROUP name will be used called – “DEFAULT_GROUP”
- The normal “single quotes” used in a XMLTABLE syntax, while creating a structured xmlindex, will have to be “two single quotes” in the create XMLIindex syntax. See examples given here. This due to the fact that the PARAMETERS clause is a string that is passed on, so ” becomes ultimately ” (this isn’t a double quote !!!)
- You can create secondairy indexes on the path table and content tables, for example, bitmap, btree or text indexes.
Besides the Oracle XMLDB Developers Guide, some extra in depth info about the structured XMLIndex can be found in the “New Features in Oracle XML DB for Oracle Database 11g Release 2” whitepaper.
HTH
🙂
