Search

Top 60 Oracle Blogs

Recent comments

Structured XMLIndex (Part 3) – Building Multiple XMLIndex Structures

You will probably never build only one structured XMLIndex. A practical use case would be an unstructured XMLIndex, indexing the semi-structured parts of your XML, multiple structured XMLIndexes, indexing the highly structured XML islands of data and maybe even a Oracle Text Context index indexing unstructured XML data.

So the next example’s will show how to build an unstructured XMLIndex and build multiple structured XMLIndexes on top of the first one. Also it will give some examples on what to do if you have made mistakes and/or how to apply some maintenance on the XMLIndex structures. You start of by determining which sections should be addressed by the Unstructured XMLIndex and via path subsetting restrict the index to that part (also see “Oracle 11g – XMLIndex (Part 2) – XMLIndex Path Subsetting” for more info on path subsetting). There should be, I think, a good reason for indexing the same node path via multiple structured or unstructured XMLIndexes. One I can think of is to support different kind of XML Queries, but be aware that it, multiple XMLIndex structures on the same nodes, will come with an extra index maintenance overhead.

Anyway, lets say you want most part (haven’t used path subsetting here for the unstructured XMLIndex, but as said I should have done) of the XML document indexed via a unstructured XMLIndex and an extra of two structured XMLIndexes on top of the domain XMLIndex…

Structured and Unstructured XMLIndex structures combined

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;">> #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;">> #993333; font-weight: bold;">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;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">DROP #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX;
#993333; font-weight: bold;">DROP #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX
           #66cc66;">*
ERROR at line #cc66cc;">1:
ORA#66cc66;">-01418: specified #993333; font-weight: bold;">INDEX does #993333; font-weight: bold;">NOT exist
 
 
SQL#66cc66;">> #993333; font-weight: bold;">CREATE #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX
  #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;">'PATH TABLE          FACE_PATHTABLE           (TABLESPACE SYSAUX NOLOGGING)
  5              PIKEY INDEX         FACE_PATHTABLE_PIKEY_IX  (TABLESPACE USERS PARALLEL 2)
  6              PATH ID INDEX       FACE_PATHTABLE_ID_IX     (TABLESPACE USERS)
  7              VALUE INDEX         FACE_PATHTABLE_VALUE_IX  (TABLESPACE USERS)
  8              ORDER KEY INDEX     FACE_PATHTABLE_KEY_IX    (TABLESPACE USERS)
  9              ASYNC (SYNC ALWAYS) STALE (FALSE)
 10             '#66cc66;">)
 #cc66cc;">11  ;
 
#993333; font-weight: bold;">INDEX created#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_name#66cc66;">, object_type #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
#808080; font-style: italic;">-------------------------------------------------- -------------------
FACE_MASKS                                         #993333; font-weight: bold;">TABLE
SYS_LOB0000074634C00002$$                          LOB
FACE_PATHTABLE                                     #993333; font-weight: bold;">TABLE
FACE_PATHTABLE_ID_IX                               #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_KEY_IX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_PIKEY_IX                            #993333; font-weight: bold;">INDEX
MY_UXI_FACEMASK_INDEX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_VALUE_IX                            #993333; font-weight: bold;">INDEX
 
#cc66cc;">8 rows selected#66cc66;">.
 
SQL#66cc66;">> BEGIN
  #cc66cc;">2    DBMS_XMLINDEX#66cc66;">.registerParameter#66cc66;">(#ff0000;">'MY_XSI_GROUP_PARAMETER'
  #cc66cc;">3                                     #66cc66;">, #ff0000;">'ADD_GROUP GROUP MY_XSI_GROUP
  4                                       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;">);
 #cc66cc;">14  END;
 #cc66cc;">15  #66cc66;">/ 
 
PL#66cc66;">/SQL procedure successfully completed#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_name#66cc66;">, object_type #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
#808080; font-style: italic;">-------------------------------------------------- -------------------
FACE_MASKS                                         #993333; font-weight: bold;">TABLE
SYS_LOB0000074634C00002$$                          LOB
FACE_PATHTABLE                                     #993333; font-weight: bold;">TABLE
FACE_PATHTABLE_ID_IX                               #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_KEY_IX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_PIKEY_IX                            #993333; font-weight: bold;">INDEX
MY_UXI_FACEMASK_INDEX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_VALUE_IX                            #993333; font-weight: bold;">INDEX
 
#cc66cc;">8 rows selected#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">ALTER #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX PARAMETERS#66cc66;">(#ff0000;">'PARAM MY_XSI_GROUP_PARAMETER'#66cc66;">);
 
#993333; font-weight: bold;">INDEX altered#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_name#66cc66;">, object_type #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
#808080; font-style: italic;">-------------------------------------------------- -------------------
FACE_MASKS                                         #993333; font-weight: bold;">TABLE
SYS_LOB0000074634C00002$$                          LOB
FACE_PATHTABLE                                     #993333; font-weight: bold;">TABLE
FACE_PATHTABLE_ID_IX                               #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_KEY_IX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_PIKEY_IX                            #993333; font-weight: bold;">INDEX
MY_UXI_FACEMASK_INDEX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_VALUE_IX                            #993333; font-weight: bold;">INDEX
FM_CONTENT_TABLE_01                                #993333; font-weight: bold;">TABLE
FM_CONTENT_TABLE_02                                #993333; font-weight: bold;">TABLE
SYS74637_74643_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74646_PKY_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74646_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS_C0011060                                       #993333; font-weight: bold;">INDEX
SYS_C0011062                                       #993333; font-weight: bold;">INDEX
 
#cc66cc;">15 rows selected#66cc66;">.
 
SQL#66cc66;">> BEGIN
  #cc66cc;">2    DBMS_XMLINDEX#66cc66;">.registerParameter#66cc66;">(#ff0000;">'MY_SECOND_XSI_GROUP_PARAMETER'
  #cc66cc;">3                                     #66cc66;">, #ff0000;">'ADD_GROUP GROUP MY_SECOND_XSI_GROUP
  4                                       XMLTABLE fm_content_01
  5                                         '#ff0000;">'*'#ff0000;">'
  6                                         COLUMNS
  7                                           xmlresult      XMLTYPE PATH '#ff0000;">'/Face/FaceType'#ff0000;">' VIRTUAL
  8                                       XMLTABLE fm_content_02
  9                                         '#ff0000;">'*'#ff0000;">'
 10                                         PASSING xmlresult
 11                                         COLUMNS
 12                                           FaceTypeId_col number  PATH '#ff0000;">'FaceTypeId/text()'#ff0000;">'
 13                                   '#66cc66;">);
 #cc66cc;">14  END;
 #cc66cc;">15  #66cc66;">/ 
 
 
PL#66cc66;">/SQL procedure successfully completed#66cc66;">.
 
SQL#66cc66;">>  #993333; font-weight: bold;">SELECT object_name#66cc66;">, object_type #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
#808080; font-style: italic;">-------------------------------------------------- -------------------
FACE_MASKS                                         #993333; font-weight: bold;">TABLE
SYS_LOB0000074634C00002$$                          LOB
FACE_PATHTABLE                                     #993333; font-weight: bold;">TABLE
FACE_PATHTABLE_ID_IX                               #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_KEY_IX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_PIKEY_IX                            #993333; font-weight: bold;">INDEX
MY_UXI_FACEMASK_INDEX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_VALUE_IX                            #993333; font-weight: bold;">INDEX
FM_CONTENT_TABLE_01                                #993333; font-weight: bold;">TABLE
FM_CONTENT_TABLE_02                                #993333; font-weight: bold;">TABLE
SYS74637_74643_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74646_PKY_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74646_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS_C0011060                                       #993333; font-weight: bold;">INDEX
SYS_C0011062                                       #993333; font-weight: bold;">INDEX
 
#cc66cc;">15 rows selected#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">ALTER #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX PARAMETERS#66cc66;">(#ff0000;">'PARAM MY_SECOND_XSI_GROUP_PARAMETER'#66cc66;">);
 
#993333; font-weight: bold;">INDEX altered#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SELECT object_name#66cc66;">, object_type #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
#808080; font-style: italic;">-------------------------------------------------- -------------------
FACE_MASKS                                         #993333; font-weight: bold;">TABLE
SYS_LOB0000074634C00002$$                          LOB
FACE_PATHTABLE                                     #993333; font-weight: bold;">TABLE
FACE_PATHTABLE_ID_IX                               #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_KEY_IX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_PIKEY_IX                            #993333; font-weight: bold;">INDEX
MY_UXI_FACEMASK_INDEX                              #993333; font-weight: bold;">INDEX
FACE_PATHTABLE_VALUE_IX                            #993333; font-weight: bold;">INDEX
FM_CONTENT_TABLE_01                                #993333; font-weight: bold;">TABLE
FM_CONTENT_TABLE_02                                #993333; font-weight: bold;">TABLE
SYS74637_74643_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74646_PKY_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74646_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS_C0011060                                       #993333; font-weight: bold;">INDEX
SYS_C0011062                                       #993333; font-weight: bold;">INDEX
FM_CONTENT_01                                      #993333; font-weight: bold;">TABLE
FM_CONTENT_02                                      #993333; font-weight: bold;">TABLE
SYS74637_74650_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74653_PKY_IDX                             #993333; font-weight: bold;">INDEX
SYS74637_74653_RID_IDX                             #993333; font-weight: bold;">INDEX
SYS_C0011064                                       #993333; font-weight: bold;">INDEX
SYS_C0011066                                       #993333; font-weight: bold;">INDEX
 
#cc66cc;">22 rows selected#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">DROP #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX;
 
#993333; font-weight: bold;">INDEX dropped#66cc66;">.
 
SQL#66cc66;">>  #993333; font-weight: bold;">SELECT object_name#66cc66;">, object_type #993333; font-weight: bold;">FROM user_objects
  #cc66cc;">2  #993333; font-weight: bold;">ORDER #993333; font-weight: bold;">BY created#66cc66;">, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
#808080; font-style: italic;">-------------------------------------------------- -------------------
FACE_MASKS                                         #993333; font-weight: bold;">TABLE
SYS_LOB0000074634C00002$$                          LOB

XMLIndex Maintenance

Say you made a mistake after the creation of the first structured XMLIndex then you could rectify this via:

SQL#66cc66;">> BEGIN
   #cc66cc;">2   DBMS_XMLINDEX#66cc66;">.registerParameter#66cc66;">(#ff0000;">'MY_XSI_GROUP_PARAMETER'#66cc66;">,#ff0000;">'DROP_GROUP GROUP MY_XSI_GROUP'#66cc66;">);
   #cc66cc;">3 END;
   #cc66cc;">4 #66cc66;">/ 
 
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;">------------------- --------------------------------------------------
LOB                 SYS_LOB0000074706C00002$$
#993333; font-weight: bold;">TABLE               FACE_MASKS
#993333; font-weight: bold;">INDEX               MY_UXI_FACEMASK_INDEX
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_ID_IX
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_KEY_IX
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_PIKEY_IX
#993333; font-weight: bold;">TABLE               FACE_PATHTABLE
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_VALUE_IX
#993333; font-weight: bold;">INDEX               SYS_C0011086
#993333; font-weight: bold;">INDEX               SYS74709_74715_RID_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011088
#993333; font-weight: bold;">INDEX               SYS74709_74718_RID_IDX
#993333; font-weight: bold;">INDEX               SYS74709_74718_PKY_IDX
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_02
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_01
 
#cc66cc;">15 rows selected#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">ALTER #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX PARAMETERS#66cc66;">(#ff0000;">'PARAM MY_XSI_GROUP_PARAMETER'#66cc66;">);
 
#993333; font-weight: bold;">INDEX altered#66cc66;">.
 
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;">------------------- --------------------------------------------------
LOB                 SYS_LOB0000074706C00002$$
#993333; font-weight: bold;">TABLE               FACE_MASKS
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_KEY_IX
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_PIKEY_IX
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_ID_IX
#993333; font-weight: bold;">INDEX               MY_UXI_FACEMASK_INDEX
#993333; font-weight: bold;">TABLE               FACE_PATHTABLE
#993333; font-weight: bold;">INDEX               FACE_PATHTABLE_VALUE_IX
 
#cc66cc;">8 rows selected#66cc66;">.

The optimizer (CBO) is picky regarding what you select… In standard “SQL” the CBO would rewrite the varchar to number to allow the statement but now it follows the correct rules (varchar is not equal to a number)… The following show you the effect of some choices you could make choosing the datatype for “COLUMNS” in you XMLTABLE statement, using it to create a structured XMLIndex. Also it demonstrates how to add an XMLIndex in a existing structure by adding to the groups using the ADD_GROUP syntax.

 
SQL#66cc66;">> BEGIN
  #cc66cc;">2    DBMS_XMLINDEX#66cc66;">.registerParameter#66cc66;">(#ff0000;">'MY_XSI_NEW_PARAMETER'
  #cc66cc;">3                                     #66cc66;">, #ff0000;">'ADD_GROUP GROUP MY_XSI_GROUP_NEW
  4                                       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_COLUMN varchar2(3)  PATH '#ff0000;">'FaceTypeId/text()'#ff0000;">'
 13                                   '#66cc66;">);
 #cc66cc;">14  END;
 #cc66cc;">15  #66cc66;">/ 
 
PL#66cc66;">/SQL procedure successfully completed#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">ALTER #993333; font-weight: bold;">INDEX MY_UXI_FACEMASK_INDEX PARAMETERS#66cc66;">(#ff0000;">'PARAM MY_XSI_NEW_PARAMETER'#66cc66;">);
 
#993333; font-weight: bold;">INDEX altered#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">SET autotrace #993333; font-weight: bold;">ON pages #cc66cc;">5000 #993333; font-weight: bold;">LINES #cc66cc;">150
 
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  ;
 
no rows selected
 
 
Execution Plan
#808080; font-style: italic;">----------------------------------------------------------
Plan hash value: #cc66cc;">1036555850
 
#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;">1 #66cc66;">|  #cc66cc;">1534 #66cc66;">|     #cc66cc;">0   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">|          #66cc66;">|
#66cc66;">|*  #cc66cc;">1 #66cc66;">|  FILTER                      #66cc66;">|                #66cc66;">|       #66cc66;">|       #66cc66;">|            #66cc66;">|          #66cc66;">|
#66cc66;">|   #cc66cc;">2 #66cc66;">|   NESTED LOOPS               #66cc66;">|                #66cc66;">|     #cc66cc;">1 #66cc66;">|  #cc66cc;">1534 #66cc66;">|     #cc66cc;">4   #66cc66;">(#cc66cc;">0#66cc66;">)#66cc66;">| 00:00:01 #66cc66;">|
#66cc66;">|*  #cc66cc;">3 #66cc66;">|    #993333; font-weight: bold;">TABLE ACCESS #993333; font-weight: bold;">FULL         #66cc66;">| FACE_PATHTABLE #66cc66;">|     #cc66cc;">1 #66cc66;">|  #cc66cc;">1522 #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;">BY USER ROWID#66cc66;">| FACE_MASKS     #66cc66;">|     #cc66cc;">1 #66cc66;">|    #cc66cc;">12 #66cc66;">|     #cc66cc;">1   #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;">1 #66cc66;">- filter#66cc66;">(#993333; font-weight: bold;">NULL #993333; font-weight: bold;">IS #993333; font-weight: bold;">NOT #993333; font-weight: bold;">NULL#66cc66;">)
   #cc66cc;">3 #66cc66;">- filter#66cc66;">(SYS_ORDERKEY_DEPTH#66cc66;">(#ff0000;">"SYS_P0"#66cc66;">.#ff0000;">"ORDER_KEY"#66cc66;">)#66cc66;">=#cc66cc;">1 #993333; font-weight: bold;">AND
              SYS_PATHID_IS_ATTR#66cc66;">(#ff0000;">"SYS_P0"#66cc66;">.#ff0000;">"PATHID"#66cc66;">)#66cc66;">=#cc66cc;">0 #993333; font-weight: bold;">AND SYS_XMLI_LOC_ISNODE#66cc66;">(#ff0000;">"SYS_P0"#66cc66;">.#ff0000;">"LOCATOR"#66cc66;">)#66cc66;">=#cc66cc;">1#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;">760  recursive calls
          #cc66cc;">0  db block gets
        #cc66cc;">569  consistent gets
          #cc66cc;">0  physical reads
          #cc66cc;">0  redo size
        #cc66cc;">294  bytes sent via SQL#66cc66;">*Net #993333; font-weight: bold;">TO client
        #cc66cc;">405  bytes received via SQL#66cc66;">*Net #993333; font-weight: bold;">FROM client
          #cc66cc;">1  SQL#66cc66;">*Net roundtrips #993333; font-weight: bold;">TO#66cc66;">/#993333; font-weight: bold;">FROM client
         #cc66cc;">61  sorts #66cc66;">(memory#66cc66;">)
          #cc66cc;">0  sorts #66cc66;">(disk#66cc66;">)
          #cc66cc;">0  rows processed
 
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 varchar2#66cc66;">(#cc66cc;">3#66cc66;">) PATH #ff0000;">'FaceTypeId/text()'
 #cc66cc;">12                 #66cc66;">) vt
 #cc66cc;">13  ;
 
 
FAC
#808080; font-style: italic;">---
001
002
003
005
006
002
 
#cc66cc;">6 rows selected#66cc66;">.
 
 
Execution Plan
#808080; font-style: italic;">----------------------------------------------------------
Plan hash value: #cc66cc;">521366288
 
#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;">2062 #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;">2062 #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;">| SYS74709_74722_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;">| SYS74709_74725_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;">3030 #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_2"#66cc66;">.#ff0000;">"RID"#66cc66;">)
   #cc66cc;">7 #66cc66;">- access#66cc66;">(#ff0000;">"SYS_ALIAS_2"#66cc66;">.#ff0000;">"KEY"#66cc66;">=#ff0000;">"SYS_ALIAS_3"#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;">72  recursive calls
          #cc66cc;">0  db block gets
        #cc66cc;">410  consistent gets
          #cc66cc;">0  physical reads
          #cc66cc;">0  redo size
        #cc66cc;">504  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

Structured XMLIndex structures ONLY

One of the things that took me initially some time to figure out, and wasn’t properly described in the (beta) manuals, howto add structured xmlindex structures. So hereby an example with only structured XMLIndexes…

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;">> #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;">> #993333; font-weight: bold;">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;">.
 
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;">------------------- --------------------------------------------------
LOB                 SYS_LOB0000074729C00002$$
#993333; font-weight: bold;">TABLE               FACE_MASKS
 
SQL#66cc66;">> #993333; font-weight: bold;">CREATE #993333; font-weight: bold;">INDEX structured_xmlindexes_only
  #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;">'GROUP MY_XSI_FIRST_GROUP
  5               XMLTABLE fm_content_table_01
  6                 '#ff0000;">'*'#ff0000;">'
  7                 COLUMNS
  8                   xmlresult      XMLTYPE PATH '#ff0000;">'/Face/FaceType'#ff0000;">' VIRTUAL
  9               XMLTABLE fm_content_table_02
 10                 '#ff0000;">'*'#ff0000;">'
 11                 PASSING xmlresult
 12                 COLUMNS
 13                   FaceTypeId_col number  PATH '#ff0000;">'FaceTypeId/text()'#ff0000;">'
 14              '#66cc66;">);
 
#993333; font-weight: bold;">INDEX created#66cc66;">.
 
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;">------------------- --------------------------------------------------
LOB                 SYS_LOB0000074729C00002$$
#993333; font-weight: bold;">TABLE               FACE_MASKS
#993333; font-weight: bold;">INDEX               STRUCTURED_XMLINDEXES_ONLY
#993333; font-weight: bold;">INDEX               SYS_C0011099
#993333; font-weight: bold;">INDEX               SYS74741_74745_RID_IDX
#993333; font-weight: bold;">INDEX               SYS74741_74745_PKY_IDX
#993333; font-weight: bold;">INDEX               SYS74741_74742_RID_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011101
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_02
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_01
 
#cc66cc;">10 rows selected#66cc66;">.
 
SQL#66cc66;">> #993333; font-weight: bold;">ALTER #993333; font-weight: bold;">INDEX structured_xmlindexes_only
  #cc66cc;">2  parameters #66cc66;">(#ff0000;">'ADD_GROUP GROUP MY_XSI_SECOND_GROUP
  3                         XMLTABLE fm_content_table_03
  4                         '#ff0000;">'*'#ff0000;">'
  5                         COLUMNS
  6                           xmlresult      XMLTYPE PATH '#ff0000;">'/Face/FaceType'#ff0000;">' VIRTUAL
  7                         XMLTABLE fm_content_table_04
  8                         '#ff0000;">'*'#ff0000;">'
  9                         PASSING xmlresult
 10                         COLUMNS
 11                           NEW_COLUMN_NAME_HERE number  PATH '#ff0000;">'FaceTypeId/text()'#ff0000;">'
 12                         '#66cc66;">);
 
#993333; font-weight: bold;">INDEX altered#66cc66;">.
 
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;">------------------- --------------------------------------------------
LOB                 SYS_LOB0000074729C00002$$
#993333; font-weight: bold;">TABLE               FACE_MASKS
#993333; font-weight: bold;">INDEX               SYS74741_74745_RID_IDX
#993333; font-weight: bold;">INDEX               STRUCTURED_XMLINDEXES_ONLY
#993333; font-weight: bold;">INDEX               SYS_C0011099
#993333; font-weight: bold;">INDEX               SYS74741_74742_RID_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011101
#993333; font-weight: bold;">INDEX               SYS74741_74745_PKY_IDX
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_02
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_01
#993333; font-weight: bold;">INDEX               SYS74741_74752_RID_IDX
#993333; font-weight: bold;">INDEX               SYS74741_74752_PKY_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011105
#993333; font-weight: bold;">INDEX               SYS_C0011103
#993333; font-weight: bold;">INDEX               SYS74741_74749_RID_IDX
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_04
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_03
 
#cc66cc;">17 rows selected#66cc66;">.
 
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  object_type;
 
OBJECT_TYPE         OBJECT_NAME
#808080; font-style: italic;">------------------- --------------------------------------------------
#993333; font-weight: bold;">INDEX               STRUCTURED_XMLINDEXES_ONLY
#993333; font-weight: bold;">INDEX               SYS74741_74752_RID_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011105
#993333; font-weight: bold;">INDEX               SYS74741_74749_RID_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011103
#993333; font-weight: bold;">INDEX               SYS74741_74745_PKY_IDX
#993333; font-weight: bold;">INDEX               SYS74741_74752_PKY_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011099
#993333; font-weight: bold;">INDEX               SYS74741_74742_RID_IDX
#993333; font-weight: bold;">INDEX               SYS_C0011101
#993333; font-weight: bold;">INDEX               SYS74741_74745_RID_IDX
LOB                 SYS_LOB0000074729C00002$$
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_03
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_04
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_01
#993333; font-weight: bold;">TABLE               FACE_MASKS
#993333; font-weight: bold;">TABLE               FM_CONTENT_TABLE_02
 
#cc66cc;">17 rows selected#66cc66;">.

The 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