HOWTO: Create a Structured XMLIndex, using the In-Memory Column Store

In Oracle database version 12.1.0.2, Oracle introduced the Oracle In-Memory Database option. It is possible from this version onwards to put selectively, via for example, an Structured XMLIndex in this section of your database memory. You can achieve therefore to selectively put some XML index data in memory and other XML sections / data accessible via regular access paths.

As explained in the Oracle XMLDB Developers Guide (and on this site), Structured XMLIndex structures are created based on an XMLTABLE statement. This XMLTABLE / create XMLIndex statement will cause a “content table” to be created that is the basis for the data in the index. From database version 12.1.0.2 this “content table” index data can be pushed into In-Memory Database store.

The following shows an example of, the extension on the current syntax; a PURCHASEORDER table with a Securefile Binary XMLType column called “XML_COLUMN”:

CREATE INDEX "LINEITEMS_XI" 
ON "PURCHASEORDER_T_BIN" ("XML_COLUMN")
INDEXTYPE IS "XDB"."XMLINDEX" 
PARAMETERS (' GROUP LINEITEMS_GROUP
              XMLTABLE LINEITEM_SXI_T (inmemory memcompress for query priority critical)
              ''/PurchaseOrder/LineItems/LineItem''
              COLUMNS
               lineitem VARCHAR2(30) PATH ''@ItemNumber'',
               description VARCHAR2(100) PATH ''Part/@Description'',
               partid VARCHAR2(30) PATH ''Part'',
               unitprice VARCHAR2(30) PATH ''Part/@UnitPrice'',
               quantity VARCHAR2(30) PATH ''Quantity''
            ');
.

To put the XML index content in the In-Memory store, add after “XMLTABLE” and your choice regarding the name for the XMLIndex “content table“, in this example called “LINEITEM_SXI_T“, your needed In-Memory clause. In this example this is “(inmemory memcompress for query priority critical)” (brackets included!).

The example above will put all lineitem element and attribute data (lineitem, descrtiption, partid, unitprice, quantity), on which the XMLIndex is based, in the In-Memory DB store. For clarity this post shows only the extension on the “create index” statement for XMLIndex, but the method using package DBMS_XMLINDEX creating Structured XML indexes follows the same syntax.

If you now have both sections, the PURCHASEORDER XML column part and LINEITEM_SXI_T content table content, in the In-Memory column store, access paths will reflect this.

Currently a known bug prevents you from using the “alter index” statement. So the following will fail with a ORA-31020:

SQL> alter table "LINEITEM_SXI_T" inmemory memcompress for query priority critical;

Error report:

ORA-31020: "The operation is not allowed, Reason: Illegal operation on XMLIndex storage table

31020. 00000 - "The operation is not allowed, Reason: %s"
*Cause: The operation attempted is not allowed
*Action: See reason and change to a valid operation.
.

Although Oracle XML DB functionality is a “no cost option” in all versions of the database, the “In-Memory Column Store” functionality is not and needs a separate / extra database license.

More information regarding pro’s and/or con’s regarding this approach can be learned via my presentation during UKOUG’s Tech Conference, follow-ups via my Slideshare account and/or on this site.

HTH.