Search

OakieTags

more tags

Who's online

There are currently 0 users and 26 guests online.

Recent comments

Affiliations

Home

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

Submitted by marco on Sun, 2010-01-17 18:42

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#66cc66;">> #993333; font-weight: bold;">CREATE user otn #993333; font-weight: bold;">IDENTIFIED #993333; font-weight: bold;">BY otn account #993333; font-weight: bold;">UNLOCK;
 
User created#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">GRANT dba#66cc66;">, xdbadmin #993333; font-weight: bold;">TO otn;
 
#993333; font-weight: bold;">GRANT succeeded#66cc66;">.
 
SQL#66cc66;">> conn otn#66cc66;">/otn
Connected#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT #66cc66;">* #993333; font-weight: bold;">FROM v$version;
 
BANNER
#808080; font-style: italic;">--------------------------------------------------------------------------------
Oracle #993333; font-weight: bold;">DATABASE 11g Enterprise Edition Release 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
PL#66cc66;">/SQL Release 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
CORE    11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0      Production
TNS #993333; font-weight: bold;">FOR Linux: Version 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
NLSRTL Version 11#66cc66;">.2#66cc66;">.0#66cc66;">.1#66cc66;">.0 #66cc66;">- Production
 
SQL#66cc66;">> #993333; font-weight: bold;">CREATE #993333; font-weight: bold;">TABLE FACE_MASKS
  #cc66cc;">2  #66cc66;">(CSXML_DOC #ff0000;">"SYS"#66cc66;">.#ff0000;">"XMLTYPE" #993333; font-weight: bold;">NOT #993333; font-weight: bold;">NULL ENABLE#66cc66;">)
  #cc66cc;">3  TABLESPACE #ff0000;">"USERS"
  #cc66cc;">4  XMLTYPE #993333; font-weight: bold;">COLUMN #ff0000;">"CSXML_DOC" STORE #993333; font-weight: bold;">AS SECUREFILE #993333; font-weight: bold;">BINARY XML;
 
#993333; font-weight: bold;">TABLE created#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SET long #cc66cc;">1000000
SQL#66cc66;">> #993333; font-weight: bold;">SET pages #cc66cc;">5000
 
SQL#66cc66;">> #808080; font-style: italic;">--Populate data
SQL#66cc66;">>INSERT #993333; font-weight: bold;">INTO FACE_MASKS#66cc66;">(CSXML_DOC#66cc66;">) #993333; font-weight: bold;">VALUES#66cc66;">(XMLTYPE#66cc66;">(#ff0000;">'
  2  
  3  223
  4  001
  5  002
  6  003
  7  224
  8  005
  9  006
 10  002
 11  
 12  '#66cc66;">)#66cc66;">);
 
#cc66cc;">1 row created#66cc66;">.
 
SQL#66cc66;">> commit;
 
Commit complete#66cc66;">.
 
#808080; font-style: italic;">-- The following is causing the problem described by you...
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT xt#66cc66;">.FaceTypeId
  #cc66cc;">2  #993333; font-weight: bold;">FROM   FACE_MASKS fm
  #cc66cc;">3  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
  #cc66cc;">4                  PASSING fm#66cc66;">.csxml_doc
  #cc66cc;">5                  #993333; font-weight: bold;">COLUMNS
  #cc66cc;">6                    FaceTypeId number PATH #ff0000;">'/Face/FaceType/FaceTypeId'
  #cc66cc;">7                 #66cc66;">) xt
  #cc66cc;">8  ;
 
#993333; font-weight: bold;">SELECT xt#66cc66;">.FaceTypeId
#66cc66;">*
ERROR at line #cc66cc;">1:
ORA#66cc66;">-#cc66cc;">19279: XPTY0004 #66cc66;">- XQuery dynamic type mismatch: expected singleton sequence
#66cc66;">- got multi#66cc66;">-item sequence
 
 
#808080; font-style: italic;">-- So one step "back" - the resultset in xml would be
 SQL#66cc66;">> #993333; font-weight: bold;">SELECT xt#66cc66;">.XMLRESULT
   #cc66cc;">2  #993333; font-weight: bold;">FROM   FACE_MASKS fm
   #cc66cc;">3  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
   #cc66cc;">4                  PASSING fm#66cc66;">.csxml_doc
   #cc66cc;">5                  #993333; font-weight: bold;">COLUMNS
   #cc66cc;">6                    XMLRESULT XMLTYPE PATH #ff0000;">'/Face/FaceType'
   #cc66cc;">7                 #66cc66;">) xt
   #cc66cc;">8  ;
 
XMLRESULT
#808080; font-style: italic;">---------------------------------
#66cc66;"><facetype#66cc66;">>
  #66cc66;"><facetypeid#66cc66;">>001#66cc66;">facetypeid#66cc66;">>
#66cc66;">facetype#66cc66;">>
#66cc66;"><facetype#66cc66;">>
  #66cc66;"><facetypeid#66cc66;">>002#66cc66;">facetypeid#66cc66;">>
#66cc66;">facetype#66cc66;">>
#66cc66;"><facetype#66cc66;">>
  #66cc66;"><facetypeid#66cc66;">>003#66cc66;">facetypeid#66cc66;">>
#66cc66;">facetype#66cc66;">>
#66cc66;"><facetype#66cc66;">>
  #66cc66;"><facetypeid#66cc66;">>005#66cc66;">facetypeid#66cc66;">>
#66cc66;">facetype#66cc66;">>
#66cc66;"><facetype#66cc66;">>
  #66cc66;"><facetypeid#66cc66;">>006#66cc66;">facetypeid#66cc66;">>
#66cc66;">facetype#66cc66;">>
#66cc66;"><facetype#66cc66;">>
  #66cc66;"><facetypeid#66cc66;">>002#66cc66;">facetypeid#66cc66;">>
#66cc66;">facetype#66cc66;">>
 
 
#808080; font-style: italic;">-- This resultset I now can pass (later while using the SXI) on via...
 
#808080; font-style: italic;">-- Test XMLTABLE structure to nest XMLTABLE result data set
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT vt#66cc66;">.FaceTypeId_col
  #cc66cc;">2  #993333; font-weight: bold;">FROM   FACE_MASKS fm
  #cc66cc;">3  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
  #cc66cc;">4                  PASSING fm#66cc66;">.csxml_doc
  #cc66cc;">5                   #993333; font-weight: bold;">COLUMNS
  #cc66cc;">6                     xmlresult      XMLTYPE PATH #ff0000;">'/Face/FaceType'
  #cc66cc;">7                 #66cc66;">) xt
  #cc66cc;">8  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
  #cc66cc;">9                   PASSING xt#66cc66;">.xmlresult
 #cc66cc;">10                   #993333; font-weight: bold;">COLUMNS
 #cc66cc;">11                     FaceTypeId_col number PATH #ff0000;">'FaceTypeId/text()'
 #cc66cc;">12                 #66cc66;">) vt
 #cc66cc;">13  ;
 
FACETYPEID_COL
#808080; font-style: italic;">--------------
             #cc66cc;">1
             #cc66cc;">2
             #cc66cc;">3
             #cc66cc;">5
             #cc66cc;">6
             #cc66cc;">2
 
#cc66cc;">6 rows selected#66cc66;">.
 
#808080; font-style: italic;">-- Create Structured XMLIndex syntax see Example 6-26: 
#808080; font-style: italic;">-- http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10492/xdb_indexing.htm#BCGJCEAF
 
#808080; font-style: italic;">/* 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#66cc66;">> #993333; font-weight: bold;">CREATE #993333; font-weight: bold;">INDEX FaceTypeId_SXI
  #cc66cc;">2    #993333; font-weight: bold;">ON FACE_MASKS#66cc66;">(CSXML_DOC#66cc66;">)
  #cc66cc;">3    INDEXTYPE #993333; font-weight: bold;">IS XDB#66cc66;">.XMLIndex
  #cc66cc;">4  PARAMETERS #66cc66;">(#ff0000;">'XMLTABLE fm_content_table_01
  5                 '#ff0000;">'*'#ff0000;">'
  6                 COLUMNS
  7                   xmlresult      XMLTYPE PATH '#ff0000;">'/Face/FaceType'#ff0000;">' VIRTUAL
  8               XMLTABLE fm_content_table_02
  9                 '#ff0000;">'*'#ff0000;">'
 10                 PASSING xmlresult
 11                 COLUMNS
 12                   FaceTypeId_col number  PATH '#ff0000;">'FaceTypeId/text()'#ff0000;">'
 13              '#66cc66;">);
 
#993333; font-weight: bold;">INDEX created#66cc66;">.
 
 
SQL#66cc66;">> col OBJECT_NAME #993333; font-weight: bold;">FOR a50
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_type#66cc66;">, object_name #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_type;
 
OBJECT_TYPE         OBJECT_NAME
#808080; font-style: italic;">------------------- --------------------------------------------------
 
#808080; font-style: italic;">-- Binary XMLType table
LOB                 SYS_LOB0000077441C00002$$
#993333; font-weight: bold;">TABLE               FACE_MASKS
 
#808080; font-style: italic;">-- Content tables
#993333; font-weight: bold;">INDEX               SYS_C0011985
#993333; font-weight: bold;">INDEX               SYS77447_77451_RID_IDX
#993333; font-weight: bold;">INDEX               SYS77447_77448_RID_IDX
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_02
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_01
#993333; font-weight: bold;">INDEX               SYS_C0011987
 
#808080; font-style: italic;">-- The "rest" of the Structured XML Index part
#993333; font-weight: bold;">INDEX               FACETYPEID_SXI
#993333; font-weight: bold;">INDEX               SYS77447_77451_PKY_IDX
 
#cc66cc;">10 rows selected#66cc66;">.

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#66cc66;">> #993333; font-weight: bold;">SET #993333; font-weight: bold;">LINES #cc66cc;">200
SQL#66cc66;">> #993333; font-weight: bold;">SET autotrace #993333; font-weight: bold;">ON
 
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT vt#66cc66;">.FaceTypeId_col
  #cc66cc;">2  #993333; font-weight: bold;">FROM   FACE_MASKS fm
  #cc66cc;">3  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
  #cc66cc;">4                  PASSING fm#66cc66;">.csxml_doc
  #cc66cc;">5                   #993333; font-weight: bold;">COLUMNS
  #cc66cc;">6                     xmlresult      XMLTYPE PATH #ff0000;">'/Face/FaceType'
  #cc66cc;">7                 #66cc66;">) xt
  #cc66cc;">8  #66cc66;">,      XMLTABLE#66cc66;">(#ff0000;">'*'
  #cc66cc;">9                   PASSING xt#66cc66;">.xmlresult
 #cc66cc;">10                   #993333; font-weight: bold;">COLUMNS
 #cc66cc;">11                     FaceTypeId_col number PATH #ff0000;">'FaceTypeId/text()'
 #cc66cc;">12                 #66cc66;">) vt
 #cc66cc;">13  ;
 
FACETYPEID_COL
#808080; font-style: italic;">--------------
             #cc66cc;">1
             #cc66cc;">2
             #cc66cc;">3
             #cc66cc;">5
             #cc66cc;">6
             #cc66cc;">2
 
#cc66cc;">6 rows selected#66cc66;">.
 
 
Execution Plan
#808080; font-style: italic;">----------------------------------------------------------
Plan hash value: #cc66cc;">2332841822
 
#808080; font-style: italic;">---------------------------------------------------------------------------------------------------------
#66cc66;">| Id  #66cc66;">| Operation                      #66cc66;">| Name                   #66cc66;">| Rows  #66cc66;">| Bytes #66cc66;">| Cost #66cc66;">(%CPU#66cc66;">)#66cc66;">| Time     #66cc66;">|
#808080; font-style: italic;">---------------------------------------------------------------------------------------------------------
#66cc66;">|   #cc66cc;">0 #66cc66;">| #993333; font-weight: bold;">SELECT STATEMENT               #66cc66;">|                        #66cc66;">|     #cc66cc;">2 #66cc66;">|  #cc66cc;">2082 #66cc66;">|     #cc66cc;">3   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">1 #66cc66;">|  NESTED LOOPS                  #66cc66;">|                        #66cc66;">|       #66cc66;">|       #66cc66;">|            #66cc66;">|          #66cc66;">|
#66cc66;">|   #cc66cc;">2 #66cc66;">|   NESTED LOOPS                 #66cc66;">|                        #66cc66;">|     #cc66cc;">2 #66cc66;">|  #cc66cc;">2082 #66cc66;">|     #cc66cc;">3   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">3 #66cc66;">|    NESTED LOOPS                #66cc66;">|                        #66cc66;">|     #cc66cc;">1 #66cc66;">|   #cc66cc;">526 #66cc66;">|     #cc66cc;">3   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">4 #66cc66;">|     #993333; font-weight: bold;">TABLE ACCESS #993333; font-weight: bold;">FULL          #66cc66;">| FACE_MASKS             #66cc66;">|     #cc66cc;">1 #66cc66;">|    #cc66cc;">12 #66cc66;">|     #cc66cc;">3   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">5 #66cc66;">|     #993333; font-weight: bold;">TABLE ACCESS #993333; font-weight: bold;">BY #993333; font-weight: bold;">INDEX ROWID#66cc66;">| FM_CONTENT_TABLE_01    #66cc66;">|     #cc66cc;">1 #66cc66;">|   #cc66cc;">514 #66cc66;">|     #cc66cc;">0   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|*  #cc66cc;">6 #66cc66;">|      #993333; font-weight: bold;">INDEX RANGE SCAN          #66cc66;">| SYS77447_77448_RID_IDX #66cc66;">|     #cc66cc;">1 #66cc66;">|       #66cc66;">|     #cc66cc;">0   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|*  #cc66cc;">7 #66cc66;">|    #993333; font-weight: bold;">INDEX RANGE SCAN            #66cc66;">| SYS77447_77451_PKY_IDX #66cc66;">|     #cc66cc;">6 #66cc66;">|       #66cc66;">|     #cc66cc;">0   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|   #cc66cc;">8 #66cc66;">|   #993333; font-weight: bold;">TABLE ACCESS #993333; font-weight: bold;">BY #993333; font-weight: bold;">INDEX ROWID  #66cc66;">| FM_CONTENT_TABLE_02    #66cc66;">|     #cc66cc;">6 #66cc66;">|  #cc66cc;">3090 #66cc66;">|     #cc66cc;">0   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#808080; font-style: italic;">---------------------------------------------------------------------------------------------------------
 
Predicate Information #66cc66;">(#993333; font-weight: bold;">IDENTIFIED #993333; font-weight: bold;">BY operation id#66cc66;">):
#808080; font-style: italic;">---------------------------------------------------
 
   #cc66cc;">6 #66cc66;">- access#66cc66;">(#ff0000;">"FM"#66cc66;">.ROWID#66cc66;">=#ff0000;">"SYS_ALIAS_0"#66cc66;">.#ff0000;">"RID"#66cc66;">)
   #cc66cc;">7 #66cc66;">- access#66cc66;">(#ff0000;">"SYS_ALIAS_0"#66cc66;">.#ff0000;">"KEY"#66cc66;">=#ff0000;">"SYS_ALIAS_1"#66cc66;">.#ff0000;">"PKEY"#66cc66;">)
 
Note
#808080; font-style: italic;">-----
   #66cc66;">- dynamic sampling used #993333; font-weight: bold;">FOR this statement #66cc66;">(level#66cc66;">=#cc66cc;">2#66cc66;">)
 
 
Statistics
#808080; font-style: italic;">----------------------------------------------------------
          #cc66cc;">0  recursive calls
          #cc66cc;">0  db block gets
         #cc66cc;">13  consistent gets
          #cc66cc;">0  physical reads
          #cc66cc;">0  redo size
        #cc66cc;">498  bytes sent via SQL#66cc66;">*Net #993333; font-weight: bold;">TO client
        #cc66cc;">416  bytes received via SQL#66cc66;">*Net #993333; font-weight: bold;">FROM client
          #cc66cc;">2  SQL#66cc66;">*Net roundtrips #993333; font-weight: bold;">TO#66cc66;">/#993333; font-weight: bold;">FROM client
          #cc66cc;">0  sorts #66cc66;">(memory#66cc66;">)
          #cc66cc;">0  sorts #66cc66;">(disk#66cc66;">)
          #cc66cc;">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

:-)

Related Posts