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  <face>
  3  <faceid>223</faceid>
  4  <facetype><facetypeid>001</facetypeid></facetype>
  5  <facetype><facetypeid>002</facetypeid></facetype>
  6  <facetype><facetypeid>003</facetypeid></facetype>
  7  <faceid>224</faceid>
  8  <facetype><facetypeid>005</facetypeid></facetype>
  9  <facetype><facetypeid>006</facetypeid></facetype>
 10  <facetype><facetypeid>002</facetypeid></facetype>
 11  </face>
 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
---------------------------------
<facetype>
  <facetypeid>001</facetypeid>
</facetype>
<facetype>
  <facetypeid>002</facetypeid>
</facetype>
<facetype>
  <facetypeid>003</facetypeid>
</facetype>
<facetype>
  <facetypeid>005</facetypeid>
</facetype>
<facetype>
  <facetypeid>006</facetypeid>
</facetype>
<facetype>
  <facetypeid>002</facetypeid>
</facetype>
 
 
-- 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

🙂

Marco Gralike Written by:

3 Comments

  1. Sid
    December 1

    Hi Marco,

    Thanks for demonstrating the creation and usage of structured XML Indexes.
    If in this example we were to add another XML ‘face’ as:

    INSERT INTO FACE_MASKS(CSXML_DOC) VALUES(XMLTYPE(‘

    223
    001
    002
    003
    224
    005
    006
    002

    224
    011
    022
    033
    224
    055
    066
    022

    ‘));

    Now we need to index :
    – faceid inside the repeating xml face
    – facetypeid inside the repeating xml facetype.

    But we are unable to do that..Tried something like:

    CREATE INDEX FaceTypeId_SXI
    ON FACE_MASKS(CSXML_DOC)
    INDEXTYPE IS XDB.XMLIndex
    PARAMETERS (‘XMLTABLE fm_content_table_01
    ”*”
    COLUMNS
    xmlresult XMLTYPE PATH ”faces/face” VIRTUAL
    XMLTABLE fm_content_table_02
    ”*”
    PASSING xmlresult
    COLUMNS
    FaceId_col number PATH ”faceId/text()”,
    FaceTypeXML XMLTYPE PATH ”facetype” VIRTUAL
    XMLTABLE fm_content_table_03
    ”*”
    PASSING FaceTypeXML
    COLUMNS
    FaceTypeID number PATH ”facetypeid/text()”
    ‘);

    SELECT vt.FaceTypeId_col
    FROM FACE_MASKS fm
    , XMLTABLE(‘*’
    PASSING fm.csxml_doc
    COLUMNS
    xmlresult XMLTYPE PATH ‘faces/face’
    ) xt
    , XMLTABLE(‘*’
    PASSING xt.xmlresult
    COLUMNS
    faceid number PATH ‘faceid/text()’,
    FaceTypeXML XMLTYPE PATH ‘facetype’
    ) xy ,
    XMLTABLE(‘*’
    PASSING xy.FaceTypeXML
    COLUMNS
    FaceTypeId_col NUMBER PATH ‘facetypeid/text()’
    ) vt

    But Select query doesn’t return anything.
    Although the query uses the Content Tables as follows:

    SQL_ID 26xqpy6g7n8nf, child number 0
    ————————————-
    SELECT vt.FaceTypeId_col FROM FACE_MASKS fm , XMLTABLE(‘*’
    PASSING fm.csxml_doc COLUMNS
    xmlresult XMLTYPE PATH ‘faces/face’
    ) xt , XMLTABLE(‘*’ PASSING
    xt.xmlresult COLUMNS faceid
    number PATH ‘faceid/text()’, FaceTypeXML XMLTYPE
    PATH ‘facetype’ ) xy ,
    XMLTABLE(‘*’ PASSING xy.FaceTypeXML
    COLUMNS FaceTypeId_col NUMBER PATH
    ‘facetypeid/text()’ ) vt

    Plan hash value: 2810788700

    ————————————————————————————————————-
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————————————————-
    | 0 | SELECT STATEMENT | | | | 3 (100)| |
    | 1 | NESTED LOOPS | | 1 | 2045 | 3 (34)| 00:00:01 |
    | 2 | NESTED LOOPS | | 1 | 2033 | 2 (50)| 00:00:01 |
    | 3 | NESTED LOOPS | | 1 | 1518 | 2 (50)| 00:00:01 |
    | 4 | VIEW | index$_join$_011 | 1 | 514 | 2 (50)| 00:00:01 |
    |* 5 | HASH JOIN | | | | | |
    | 6 | INDEX FAST FULL SCAN | SYS3327181_3327182_RID_IDX | 1 | 514 | 0 (0)| |
    | 7 | INDEX FAST FULL SCAN | SYS3327181_3327182_KEY_IDX | 1 | 514 | 1 (0)| 00:00:01 |
    | 8 | TABLE ACCESS BY INDEX ROWID| FM_CONTENT_TABLE_02 | 1 | 1004 | 0 (0)| |
    |* 9 | INDEX RANGE SCAN | SYS3327181_3327185_PKY_IDX | 1 | | 0 (0)| |
    | 10 | TABLE ACCESS BY INDEX ROWID | FM_CONTENT_TABLE_03 | 1 | 515 | 0 (0)| |
    |* 11 | INDEX RANGE SCAN | SYS3327181_3327189_PKY_IDX | 1 | | 0 (0)| |
    | 12 | TABLE ACCESS BY USER ROWID | FACE_MASKS | 1 | 12 | 1 (0)| 00:00:01 |
    ————————————————————————————————————-

    Query Block Name / Object Alias (identified by operation id):
    ————————————————————-

    1 – SEL$5BAF7FBA
    4 – SEL$40C3ED9E / SYS_SXI_3@SEL$E20BB5D8
    5 – SEL$40C3ED9E
    6 – SEL$40C3ED9E / indexjoin$_alias$_001@SEL$40C3ED9E
    7 – SEL$40C3ED9E / indexjoin$_alias$_002@SEL$40C3ED9E
    8 – SEL$5BAF7FBA / SYS_SXI_4@SEL$099778A2
    9 – SEL$5BAF7FBA / SYS_SXI_4@SEL$099778A2
    10 – SEL$5BAF7FBA / SYS_SXI_5@SEL$73B70E59
    11 – SEL$5BAF7FBA / SYS_SXI_5@SEL$73B70E59
    12 – SEL$5BAF7FBA / FM@SEL$1

    Outline Data
    ————-

    /*+
    BEGIN_OUTLINE_DATA
    IGNORE_OPTIM_EMBEDDED_HINTS
    OPTIMIZER_FEATURES_ENABLE(‘11.2.0.3’)
    DB_VERSION(‘11.2.0.3’)
    ALL_ROWS
    FORCE_XML_QUERY_REWRITE
    XML_DML_RWT_STMT
    XMLINDEX_REWRITE
    XMLINDEX_REWRITE_IN_SELECT
    NO_COST_XML_QUERY_REWRITE
    OUTLINE_LEAF(@”SEL$40C3ED9E”)
    OUTLINE_LEAF(@”SEL$5BAF7FBA”)
    MERGE(@”SEL$099778A2″)
    MERGE(@”SEL$73B70E59″)
    MERGE(@”SEL$E20BB5D8″)
    OUTLINE(@”SEL$1″)
    OUTLINE(@”SEL$099778A2″)
    OUTLINE(@”SEL$73B70E59″)
    OUTLINE(@”SEL$E20BB5D8″)
    INDEX_JOIN(@”SEL$5BAF7FBA” “SYS_SXI_3″@”SEL$E20BB5D8” (“FM_CONTENT_TABLE_01″.”RID”)
    (“FM_CONTENT_TABLE_01″.”KEY”))
    INDEX_RS_ASC(@”SEL$5BAF7FBA” “SYS_SXI_4″@”SEL$099778A2” (“FM_CONTENT_TABLE_02″.”PKEY”))
    INDEX_RS_ASC(@”SEL$5BAF7FBA” “SYS_SXI_5″@”SEL$73B70E59” (“FM_CONTENT_TABLE_03″.”PKEY”))
    ROWID(@”SEL$5BAF7FBA” “FM”@”SEL$1″)
    LEADING(@”SEL$5BAF7FBA” “SYS_SXI_3″@”SEL$E20BB5D8” “SYS_SXI_4″@”SEL$099778A2”
    “SYS_SXI_5″@”SEL$73B70E59” “FM”@”SEL$1″)
    USE_NL(@”SEL$5BAF7FBA” “SYS_SXI_4″@”SEL$099778A2″)
    USE_NL(@”SEL$5BAF7FBA” “SYS_SXI_5″@”SEL$73B70E59″)
    USE_NL(@”SEL$5BAF7FBA” “FM”@”SEL$1”)
    END_OUTLINE_DATA
    */

    Predicate Information (identified by operation id):
    —————————————————

    5 – access(ROWID=ROWID)
    9 – access(“SYS_SXI_3″.”KEY”=”SYS_SXI_4″.”PKEY”)
    11 – access(“SYS_SXI_4″.”KEY”=”SYS_SXI_5″.”PKEY”)

    Column Projection Information (identified by operation id):
    ———————————————————–

    1 – “SYS_SXI_5”.”FACETYPEID”[NUMBER,22]
    2 – “SYS_SXI_3”.”RID”[ROWID,10], “SYS_SXI_5”.”FACETYPEID”[NUMBER,22]
    3 – “SYS_SXI_3”.”RID”[ROWID,10], “SYS_SXI_4”.”KEY”[RAW,1000]
    4 – “SYS_SXI_3”.”KEY”[RAW,1000], “SYS_SXI_3”.”RID”[ROWID,10]
    5 – (#keys=1) “SYS_SXI_3”.”RID”[ROWID,10], “SYS_SXI_3”.”KEY”[RAW,1000]
    6 – ROWID[ROWID,10], “SYS_SXI_3”.”RID”[ROWID,10]
    7 – ROWID[ROWID,10], “SYS_SXI_3”.”KEY”[RAW,1000]
    8 – “SYS_SXI_4”.”KEY”[RAW,1000]
    9 – “SYS_SXI_4”.ROWID[ROWID,10]
    10 – “SYS_SXI_5”.”FACETYPEID”[NUMBER,22]
    11 – “SYS_SXI_5”.ROWID[ROWID,10]

    Note
    —–
    – dynamic sampling used for this statement (level=2)

    Please suggest what’s missing here.

    Thanks,
    Sid

  2. Sid
    December 1

    Hi Marco,

    Sorry but the XMLdidn’t get posted in the previous message.
    It’s something like:

    –Truncate table face_masks

    INSERT INTO FACE_MASKS(CSXML_DOC) VALUES(XMLTYPE(‘

    223
    001007
    002
    003
    224
    005
    006
    002

    223
    001007
    002
    003
    224
    005
    006
    002

    ‘));

  3. December 2

    Try creating secondary indexes on the specific content table(s) columns and see if the optimizer will pick these up regarding being more “cost” effective.

Comments are closed.