Structured XMLIndex (Part 2) – Howto build a structured XMLIndex

As said in the “rule of numb” post, test your statement before you build an XMLIndex (structured or unstructured) on you column or table XML store. The database will check on the syntax you will use but NOT on the outcome. So if you statement doesn’t have the proper result set or is even empty, than the content table(s) or path table will be indexing the wrong element values or even a null data set. Be aware that XML in Oracle is case-sensitive and critical on calling a namespace reference if one if demanded by the W3C rules.

The following example will build a single structured XMLIndex on a binary xml column.

SQL> create user otn identified by otn account unlock;
 
User created.
 
SQL> grant dba, xdbadmin to otn;
 
Grant succeeded.
 
SQL> conn otn/otn
Connected.
 
SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
 
SQL> CREATE TABLE FACE_MASKS
  2  (CSXML_DOC "SYS"."XMLTYPE" NOT NULL ENABLE)
  3  TABLESPACE "USERS"
  4  XMLTYPE COLUMN "CSXML_DOC" STORE AS SECUREFILE BINARY XML;
 
Table created.
 
SQL> set long 1000000
SQL> set pages 5000
 
SQL> --Populate data
SQL>INSERT INTO FACE_MASKS(CSXML_DOC) VALUES(XMLTYPE('
  2  
  3  223
  4  001
  5  002
  6  003
  7  224
  8  005
  9  006
 10  002
 11  
 12  '));
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
-- The following is causing the problem described by you...
 
SQL> select xt.FaceTypeId
  2  from   FACE_MASKS fm
  3  ,      XMLTABLE('*'
  4                  PASSING fm.csxml_doc
  5                  COLUMNS
  6                    FaceTypeId number PATH '/Face/FaceType/FaceTypeId'
  7                 ) xt
  8  ;
 
select xt.FaceTypeId
*
ERROR at line 1:
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence
- got multi-item sequence
 
 
-- So one step "back" - the resultset in xml would be
 SQL> select xt.XMLRESULT
   2  from   FACE_MASKS fm
   3  ,      XMLTABLE('*'
   4                  PASSING fm.csxml_doc
   5                  COLUMNS
   6                    XMLRESULT XMLTYPE PATH '/Face/FaceType'
   7                 ) xt
   8  ;
 
XMLRESULT
---------------------------------

  001


  002


  003


  005


  006


  002

 
 
-- This resultset I now can pass (later while using the SXI) on via...
 
-- Test XMLTABLE structure to nest XMLTABLE result data set
 
SQL> select vt.FaceTypeId_col
  2  from   FACE_MASKS fm
  3  ,      XMLTABLE('*'
  4                  PASSING fm.csxml_doc
  5                   COLUMNS
  6                     xmlresult      XMLTYPE PATH '/Face/FaceType'
  7                 ) xt
  8  ,      XMLTABLE('*'
  9                   PASSING xt.xmlresult
 10                   COLUMNS
 11                     FaceTypeId_col number PATH 'FaceTypeId/text()'
 12                 ) vt
 13  ;
 
FACETYPEID_COL
--------------
             1
             2
             3
             5
             6
             2
 
6 rows selected.
 
-- Create Structured XMLIndex syntax see Example 6-26: 
-- http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10492/xdb_indexing.htm#BCGJCEAF
 
/* Example 6-26 from the Oracle 11gR2 XMLDB Developers Guide

CREATE INDEX po_struct ON po_binxml (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex
  PARAMETERS ('XMLTable po_ptab 
                 XMLNAMESPACES(DEFAULT ''http://www.example.com/po''), 
                 ''/purchaseOrder'' 
                 COLUMNS orderdate   DATE          PATH ''@orderDate'', 
                         id          BINARY_DOUBLE PATH ''@id'', 
                         items       XMLType       PATH ''items/item'' VIRTUAL 
               XMLTable li_tab 
                 XMLNAMESPACES(DEFAULT ''http://www.example.com/po''), 
                 ''/item'' PASSING items 
                 COLUMNS partnum     VARCHAR2(15)  PATH ''@partNum'', 
                         description CLOB          PATH ''productName'', 
                         usprice     BINARY_DOUBLE PATH ''USPrice'', 
                         shipdat     DATE          PATH ''shipDate'''); 

*/
  
SQL> CREATE INDEX FaceTypeId_SXI
  2    ON FACE_MASKS(CSXML_DOC)
  3    INDEXTYPE IS XDB.XMLIndex
  4  PARAMETERS ('XMLTABLE fm_content_table_01
  5                 ''*''
  6                 COLUMNS
  7                   xmlresult      XMLTYPE PATH ''/Face/FaceType'' VIRTUAL
  8               XMLTABLE fm_content_table_02
  9                 ''*''
 10                 PASSING xmlresult
 11                 COLUMNS
 12                   FaceTypeId_col number  PATH ''FaceTypeId/text()''
 13              ');
 
Index created.
 
 
SQL> col OBJECT_NAME for a50
 
SQL> select object_type, object_name from user_objects
  2  order by created, object_type;
 
OBJECT_TYPE         OBJECT_NAME
------------------- --------------------------------------------------
 
-- Binary XMLType table
LOB                 SYS_LOB0000077441C00002$$
TABLE               FACE_MASKS
 
-- Content tables
INDEX               SYS_C0011985
INDEX               SYS77447_77451_RID_IDX
INDEX               SYS77447_77448_RID_IDX
TABLE               FM_CONTENT_TABLE_02
TABLE               FM_CONTENT_TABLE_01
INDEX               SYS_C0011987
 
-- The "rest" of the Structured XML Index part
INDEX               FACETYPEID_SXI
INDEX               SYS77447_77451_PKY_IDX
 
10 rows selected.

So for example, if I now use the XML statement that was the base for the structured XMLIndex, you will see that is used by getting its results from the content tables…

SQL> set lines 200
SQL> set autotrace on
 
 
SQL> select vt.FaceTypeId_col
  2  from   FACE_MASKS fm
  3  ,      XMLTABLE('*'
  4                  PASSING fm.csxml_doc
  5                   COLUMNS
  6                     xmlresult      XMLTYPE PATH '/Face/FaceType'
  7                 ) xt
  8  ,      XMLTABLE('*'
  9                   PASSING xt.xmlresult
 10                   COLUMNS
 11                     FaceTypeId_col number PATH 'FaceTypeId/text()'
 12                 ) vt
 13  ;
 
FACETYPEID_COL
--------------
             1
             2
             3
             5
             6
             2
 
6 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2332841822
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                        |     2 |  2082 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |                        |       |       |            |          |
|   2 |   NESTED LOOPS                 |                        |     2 |  2082 |     3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |                        |     1 |   526 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL          | FACE_MASKS             |     1 |    12 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| FM_CONTENT_TABLE_01    |     1 |   514 |     0   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN          | SYS77447_77448_RID_IDX |     1 |       |     0   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN            | SYS77447_77451_PKY_IDX |     6 |       |     0   (0)| 00:00:01 |
|   8 |   TABLE ACCESS BY INDEX ROWID  | FM_CONTENT_TABLE_02    |     6 |  3090 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("FM".ROWID="SYS_ALIAS_0"."RID")
   7 - access("SYS_ALIAS_0"."KEY"="SYS_ALIAS_1"."PKEY")
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        498  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

This post is based on a OTN thread were I gave some examples on how to build a structured XMLIndex or multiple structured XMLIndex on a binary XML column. The examples are easily rewritten for an XMLType table storage solution using the virtual column OBJECT_VALUE instead of using the xmltype column name.

HTH

🙂