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> CREATE USER otn IDENTIFIED BY otn account UNLOCK;
 
USER created.
 
SQL> GRANT dba, xdbadmin TO otn;
 
GRANT succeeded.
 
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> 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.
 
SQL> DROP INDEX MY_UXI_FACEMASK_INDEX;
DROP INDEX MY_UXI_FACEMASK_INDEX
           *
ERROR at line 1:
ORA-01418: specified INDEX does NOT exist
 
 
SQL> CREATE INDEX MY_UXI_FACEMASK_INDEX
  2    ON FACE_MASKS(CSXML_DOC)
  3    INDEXTYPE IS XDB.XMLIndex
  4  PARAMETERS('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             ')
 11  ;
 
INDEX created.
 
SQL> SELECT object_name, object_type FROM user_objects
  2  ORDER BY created, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
-------------------------------------------------- -------------------
FACE_MASKS                                         TABLE
SYS_LOB0000074634C00002$$                          LOB
FACE_PATHTABLE                                     TABLE
FACE_PATHTABLE_ID_IX                               INDEX
FACE_PATHTABLE_KEY_IX                              INDEX
FACE_PATHTABLE_PIKEY_IX                            INDEX
MY_UXI_FACEMASK_INDEX                              INDEX
FACE_PATHTABLE_VALUE_IX                            INDEX
 
8 ROWS selected.
 
SQL> BEGIN
  2    DBMS_XMLINDEX.registerParameter('MY_XSI_GROUP_PARAMETER'
  3                                     , 'ADD_GROUP GROUP MY_XSI_GROUP
  4                                       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                                   ');
 14  END;
 15  / 
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT object_name, object_type FROM user_objects
  2  ORDER BY created, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
-------------------------------------------------- -------------------
FACE_MASKS                                         TABLE
SYS_LOB0000074634C00002$$                          LOB
FACE_PATHTABLE                                     TABLE
FACE_PATHTABLE_ID_IX                               INDEX
FACE_PATHTABLE_KEY_IX                              INDEX
FACE_PATHTABLE_PIKEY_IX                            INDEX
MY_UXI_FACEMASK_INDEX                              INDEX
FACE_PATHTABLE_VALUE_IX                            INDEX
 
8 ROWS selected.
 
SQL> ALTER INDEX MY_UXI_FACEMASK_INDEX PARAMETERS('PARAM MY_XSI_GROUP_PARAMETER');
 
INDEX altered.
 
SQL> SELECT object_name, object_type FROM user_objects
  2  ORDER BY created, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
-------------------------------------------------- -------------------
FACE_MASKS                                         TABLE
SYS_LOB0000074634C00002$$                          LOB
FACE_PATHTABLE                                     TABLE
FACE_PATHTABLE_ID_IX                               INDEX
FACE_PATHTABLE_KEY_IX                              INDEX
FACE_PATHTABLE_PIKEY_IX                            INDEX
MY_UXI_FACEMASK_INDEX                              INDEX
FACE_PATHTABLE_VALUE_IX                            INDEX
FM_CONTENT_TABLE_01                                TABLE
FM_CONTENT_TABLE_02                                TABLE
SYS74637_74643_RID_IDX                             INDEX
SYS74637_74646_PKY_IDX                             INDEX
SYS74637_74646_RID_IDX                             INDEX
SYS_C0011060                                       INDEX
SYS_C0011062                                       INDEX
 
15 ROWS selected.
 
SQL> BEGIN
  2    DBMS_XMLINDEX.registerParameter('MY_SECOND_XSI_GROUP_PARAMETER'
  3                                     , 'ADD_GROUP GROUP MY_SECOND_XSI_GROUP
  4                                       XMLTABLE fm_content_01
  5                                         ''*''
  6                                         COLUMNS
  7                                           xmlresult      XMLTYPE PATH ''/Face/FaceType'' VIRTUAL
  8                                       XMLTABLE fm_content_02
  9                                         ''*''
 10                                         PASSING xmlresult
 11                                         COLUMNS
 12                                           FaceTypeId_col number  PATH ''FaceTypeId/text()''
 13                                   ');
 14  END;
 15  / 
 
 
PL/SQL PROCEDURE successfully completed.
 
SQL>  SELECT object_name, object_type FROM user_objects
  2  ORDER BY created, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
-------------------------------------------------- -------------------
FACE_MASKS                                         TABLE
SYS_LOB0000074634C00002$$                          LOB
FACE_PATHTABLE                                     TABLE
FACE_PATHTABLE_ID_IX                               INDEX
FACE_PATHTABLE_KEY_IX                              INDEX
FACE_PATHTABLE_PIKEY_IX                            INDEX
MY_UXI_FACEMASK_INDEX                              INDEX
FACE_PATHTABLE_VALUE_IX                            INDEX
FM_CONTENT_TABLE_01                                TABLE
FM_CONTENT_TABLE_02                                TABLE
SYS74637_74643_RID_IDX                             INDEX
SYS74637_74646_PKY_IDX                             INDEX
SYS74637_74646_RID_IDX                             INDEX
SYS_C0011060                                       INDEX
SYS_C0011062                                       INDEX
 
15 ROWS selected.
 
SQL> ALTER INDEX MY_UXI_FACEMASK_INDEX PARAMETERS('PARAM MY_SECOND_XSI_GROUP_PARAMETER');
 
INDEX altered.
 
SQL> SELECT object_name, object_type FROM user_objects
  2  ORDER BY created, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
-------------------------------------------------- -------------------
FACE_MASKS                                         TABLE
SYS_LOB0000074634C00002$$                          LOB
FACE_PATHTABLE                                     TABLE
FACE_PATHTABLE_ID_IX                               INDEX
FACE_PATHTABLE_KEY_IX                              INDEX
FACE_PATHTABLE_PIKEY_IX                            INDEX
MY_UXI_FACEMASK_INDEX                              INDEX
FACE_PATHTABLE_VALUE_IX                            INDEX
FM_CONTENT_TABLE_01                                TABLE
FM_CONTENT_TABLE_02                                TABLE
SYS74637_74643_RID_IDX                             INDEX
SYS74637_74646_PKY_IDX                             INDEX
SYS74637_74646_RID_IDX                             INDEX
SYS_C0011060                                       INDEX
SYS_C0011062                                       INDEX
FM_CONTENT_01                                      TABLE
FM_CONTENT_02                                      TABLE
SYS74637_74650_RID_IDX                             INDEX
SYS74637_74653_PKY_IDX                             INDEX
SYS74637_74653_RID_IDX                             INDEX
SYS_C0011064                                       INDEX
SYS_C0011066                                       INDEX
 
22 ROWS selected.
 
SQL> DROP INDEX MY_UXI_FACEMASK_INDEX;
 
INDEX dropped.
 
SQL>  SELECT object_name, object_type FROM user_objects
  2  ORDER BY created, object_name;
 
OBJECT_NAME                                        OBJECT_TYPE
-------------------------------------------------- -------------------
FACE_MASKS                                         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> BEGIN
   2   DBMS_XMLINDEX.registerParameter('MY_XSI_GROUP_PARAMETER','DROP_GROUP GROUP MY_XSI_GROUP');
   3 END;
   4 / 
 
SQL> SELECT object_type, object_name FROM user_objects
  2  ORDER BY created, object_type;
 
OBJECT_TYPE         OBJECT_NAME
------------------- --------------------------------------------------
LOB                 SYS_LOB0000074706C00002$$
TABLE               FACE_MASKS
INDEX               MY_UXI_FACEMASK_INDEX
INDEX               FACE_PATHTABLE_ID_IX
INDEX               FACE_PATHTABLE_KEY_IX
INDEX               FACE_PATHTABLE_PIKEY_IX
TABLE               FACE_PATHTABLE
INDEX               FACE_PATHTABLE_VALUE_IX
INDEX               SYS_C0011086
INDEX               SYS74709_74715_RID_IDX
INDEX               SYS_C0011088
INDEX               SYS74709_74718_RID_IDX
INDEX               SYS74709_74718_PKY_IDX
TABLE               FM_CONTENT_TABLE_02
TABLE               FM_CONTENT_TABLE_01
 
15 ROWS selected.
 
SQL> ALTER INDEX MY_UXI_FACEMASK_INDEX PARAMETERS('PARAM MY_XSI_GROUP_PARAMETER');
 
INDEX altered.
 
SQL> SELECT object_type, object_name FROM user_objects
  2  ORDER BY created, object_type;
 
OBJECT_TYPE         OBJECT_NAME
------------------- --------------------------------------------------
LOB                 SYS_LOB0000074706C00002$$
TABLE               FACE_MASKS
INDEX               FACE_PATHTABLE_KEY_IX
INDEX               FACE_PATHTABLE_PIKEY_IX
INDEX               FACE_PATHTABLE_ID_IX
INDEX               MY_UXI_FACEMASK_INDEX
TABLE               FACE_PATHTABLE
INDEX               FACE_PATHTABLE_VALUE_IX
 
8 ROWS selected.

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> BEGIN
  2    DBMS_XMLINDEX.registerParameter('MY_XSI_NEW_PARAMETER'
  3                                     , 'ADD_GROUP GROUP MY_XSI_GROUP_NEW
  4                                       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_COLUMN varchar2(3)  PATH ''FaceTypeId/text()''
 13                                   ');
 14  END;
 15  / 
 
PL/SQL PROCEDURE successfully completed.
 
SQL> ALTER INDEX MY_UXI_FACEMASK_INDEX PARAMETERS('PARAM MY_XSI_NEW_PARAMETER');
 
INDEX altered.
 
SQL> SET autotrace ON pages 5000 LINES 150
 
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  ;
 
no ROWS selected
 
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1036555850
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |  1534 |     0   (0)|          |
|*  1 |  FILTER                      |                |       |       |            |          |
|   2 |   NESTED LOOPS               |                |     1 |  1534 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | FACE_PATHTABLE |     1 |  1522 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY USER ROWID| FACE_MASKS     |     1 |    12 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - FILTER(NULL IS NOT NULL)
   3 - FILTER(SYS_ORDERKEY_DEPTH("SYS_P0"."ORDER_KEY")=1 AND
              SYS_PATHID_IS_ATTR("SYS_P0"."PATHID")=0 AND SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
 
Note
-----
   - dynamic sampling used FOR this statement (level=2)
 
 
Statistics
----------------------------------------------------------
        760  recursive calls
          0  db block gets
        569  consistent gets
          0  physical reads
          0  redo SIZE
        294  bytes sent via SQL*Net TO client
        405  bytes received via SQL*Net FROM client
          1  SQL*Net roundtrips TO/FROM client
         61  sorts (memory)
          0  sorts (disk)
          0  ROWS processed
 
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 varchar2(3) PATH 'FaceTypeId/text()'
 12                 ) vt
 13  ;
 
 
FAC
---
001
002
003
005
006
002
 
6 ROWS selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 521366288
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                   | ROWS  | Bytes | Cost (%CPU)| TIME     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                        |     2 |  2062 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |                        |       |       |            |          |
|   2 |   NESTED LOOPS                 |                        |     2 |  2062 |     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          | SYS74709_74722_RID_IDX |     1 |       |     0   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN            | SYS74709_74725_PKY_IDX |     6 |       |     0   (0)| 00:00:01 |
|   8 |   TABLE ACCESS BY INDEX ROWID  | FM_CONTENT_TABLE_02    |     6 |  3030 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   6 - access("FM".ROWID="SYS_ALIAS_2"."RID")
   7 - access("SYS_ALIAS_2"."KEY"="SYS_ALIAS_3"."PKEY")
 
Note
-----
   - dynamic sampling used FOR this statement (level=2)
 
 
Statistics
----------------------------------------------------------
         72  recursive calls
          0  db block gets
        410  consistent gets
          0  physical reads
          0  redo SIZE
        504  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

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> CREATE USER otn IDENTIFIED BY otn account UNLOCK;
 
USER created.
 
SQL> GRANT dba, xdbadmin TO otn;
 
GRANT succeeded.
 
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> 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.
 
SQL> SELECT object_type, object_name FROM user_objects
  2  ORDER BY created, object_type;
 
OBJECT_TYPE         OBJECT_NAME
------------------- --------------------------------------------------
LOB                 SYS_LOB0000074729C00002$$
TABLE               FACE_MASKS
 
SQL> CREATE INDEX structured_xmlindexes_only
  2    ON FACE_MASKS(CSXML_DOC)
  3    INDEXTYPE IS XDB.XMLIndex
  4  PARAMETERS ('GROUP MY_XSI_FIRST_GROUP
  5               XMLTABLE fm_content_table_01
  6                 ''*''
  7                 COLUMNS
  8                   xmlresult      XMLTYPE PATH ''/Face/FaceType'' VIRTUAL
  9               XMLTABLE fm_content_table_02
 10                 ''*''
 11                 PASSING xmlresult
 12                 COLUMNS
 13                   FaceTypeId_col number  PATH ''FaceTypeId/text()''
 14              ');
 
INDEX created.
 
SQL> SELECT object_type, object_name FROM user_objects
  2   ORDER BY created, object_type;
 
OBJECT_TYPE         OBJECT_NAME
------------------- --------------------------------------------------
LOB                 SYS_LOB0000074729C00002$$
TABLE               FACE_MASKS
INDEX               STRUCTURED_XMLINDEXES_ONLY
INDEX               SYS_C0011099
INDEX               SYS74741_74745_RID_IDX
INDEX               SYS74741_74745_PKY_IDX
INDEX               SYS74741_74742_RID_IDX
INDEX               SYS_C0011101
TABLE               FM_CONTENT_TABLE_02
TABLE               FM_CONTENT_TABLE_01
 
10 ROWS selected.
 
SQL> ALTER INDEX structured_xmlindexes_only
  2  parameters ('ADD_GROUP GROUP MY_XSI_SECOND_GROUP
  3                         XMLTABLE fm_content_table_03
  4                         ''*''
  5                         COLUMNS
  6                           xmlresult      XMLTYPE PATH ''/Face/FaceType'' VIRTUAL
  7                         XMLTABLE fm_content_table_04
  8                         ''*''
  9                         PASSING xmlresult
 10                         COLUMNS
 11                           NEW_COLUMN_NAME_HERE number  PATH ''FaceTypeId/text()''
 12                         ');
 
INDEX altered.
 
SQL> SELECT object_type, object_name FROM user_objects
  2   ORDER BY created, object_type;
 
OBJECT_TYPE         OBJECT_NAME
------------------- --------------------------------------------------
LOB                 SYS_LOB0000074729C00002$$
TABLE               FACE_MASKS
INDEX               SYS74741_74745_RID_IDX
INDEX               STRUCTURED_XMLINDEXES_ONLY
INDEX               SYS_C0011099
INDEX               SYS74741_74742_RID_IDX
INDEX               SYS_C0011101
INDEX               SYS74741_74745_PKY_IDX
TABLE               FM_CONTENT_TABLE_02
TABLE               FM_CONTENT_TABLE_01
INDEX               SYS74741_74752_RID_IDX
INDEX               SYS74741_74752_PKY_IDX
INDEX               SYS_C0011105
INDEX               SYS_C0011103
INDEX               SYS74741_74749_RID_IDX
TABLE               FM_CONTENT_TABLE_04
TABLE               FM_CONTENT_TABLE_03
 
17 ROWS selected.
 
SQL> SELECT object_type, object_name FROM user_objects
  2  ORDER BY  object_type;
 
OBJECT_TYPE         OBJECT_NAME
------------------- --------------------------------------------------
INDEX               STRUCTURED_XMLINDEXES_ONLY
INDEX               SYS74741_74752_RID_IDX
INDEX               SYS_C0011105
INDEX               SYS74741_74749_RID_IDX
INDEX               SYS_C0011103
INDEX               SYS74741_74745_PKY_IDX
INDEX               SYS74741_74752_PKY_IDX
INDEX               SYS_C0011099
INDEX               SYS74741_74742_RID_IDX
INDEX               SYS_C0011101
INDEX               SYS74741_74745_RID_IDX
LOB                 SYS_LOB0000074729C00002$$
TABLE               FM_CONTENT_TABLE_03
TABLE               FM_CONTENT_TABLE_04
TABLE               FM_CONTENT_TABLE_01
TABLE               FACE_MASKS
TABLE               FM_CONTENT_TABLE_02
 
17 ROWS selected.

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

:-)

Marco Gralike Written by:

6 Comments

  1. tze hon
    June 6

    Hi,

    I am unable to alter my local XMLIndex to add a structured component and I was hoping you could help shed some light on the problem. The local XMLIndex was previously created successfuly on a range partitioned relational table with an XMLType column. The range partition is on a normal date column.

    -- On 11g Release 2
    -- Creating unstructured index.
    CREATE myindex_idx ON my_table(xml_column) INDEXTYPE IS XDB.XMLINDEX
    LOCAL
    PARAMETERS
    (
    'PATH TABLE my_path_table
    PATHS (INCLUDE (/a/b/c
    /d/e/f))'
    );
    -- Success

    BEGIN
    DBMS_XMLINDEX.REGISTERPARAMETER(
    'my_param',
    'ADD_GROUP GROUP my_group
    XMLTABLE TABLE_1
    ''//ZZZ''
    COLUMNS
    ZZZ_COLLECTION XMLTYPE PATH ''YYY'' VIRTUAL
    XMLTABLE TABLE_2
    ''/YYY''
    PASSING ZZZ_COLLECTION
    COLUMNS MY_ID VARCHAR2(32) PATH ''MY_ID''');
    END;
    /
    -- Completed

    ALTER INDEX myindex_idx PARAMETERS('PARAM my_param');
    -- Error: Object name has to be specified for DML statements involving system
    -- generated partitions

    As a side note, I have previously created all the unstructured and structured components for my XMLIndex on a non partitioned table successfully, so I was wondering if there is some problem with my syntax since I am now trying it on a partitioned table.

    I apologise for the lack of the exact ORA error code due to some issues arising from the fact that my internet pc is separate from my dev pc. I will update the post once I get the error code.

    Thanks in advance for any pointers.

  2. tze hon
    June 7

    Hi,

    The error msg I get when trying to alter my index to add a structured component is

    SQL Error: ORA-29874: warning in the execution of ODCIINDEXALTER routine
    ORA-29960: line 1, ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method
    29874. 00000 – “warning in the execution of ODCIINDEXALTER routine.
    *Cause: A warning was returned from the ODCIIndexAlter routine.
    *Action: Check to see if the routine has been coded correctly
    Check the user defined warning log tables for greater details.

  3. June 7

    Thats a good one.

    I guess somewhere we have to fit the LOCAL hint in there, but don’t know how to get this done via DBMS_XMLINDEX.

    I would try again not using the DBMS_XMLINDEX method but after creating the initial unstructured XMLIndex adding the structured XMLIndex via a ALTER INDEX statement while using the LOCAL hint.

  4. tze hon
    June 8

    Hi,

    Thanks for the solution and syntax. The ALTER INDEX (without DBMS_XMLINDEX) method works but it failed when my parameter string is > 1000 characters long as that is the limit for the parameter string. This occurs because I attempt to add multiple groups in one parameter string. Based on your solution, I broke it into multiple ALTER INDEX statements for each group and it works.

    As an alternative to the above solution, I registered my parameter and created the XMLIndex with the parameter in one go, creating both unstructured and structured components together. I cobbled together the following code after looking at the DBMS_XMLINDEX.REGISTERPARAMETER documentation:

    BEGIN
    DBMS_XMLINDEX.DROPPARAMETER(‘indexParam’);
    END;
    /

    BEGIN
    DBMS_XMLINDEX.REGISTERPARAMETER(
    ‘indexParam’,
    ‘PATH TABLE MY_PATH_TABLE
    PATH ID INDEX MY_PATH_ID_IDX
    PIKEY INDEX MY_PIKEY_IDX
    VALUE INDEX MY_VALUE_IDX
    ORDER KEY INDEX MY_ORDERKEY_IDX
    PATHS (INCLUDE (
    /a/b/c
    /a/b/d
    /a/e/f
    /a/e/g
    /a/h/i
    /a/j/*/k
    /a/m/*/n/o/p
    /a/m/*/n/o/q/r/s
    /a/m/*/n/o/q/r/t/u/v
    /a/m/*/n/o/q/r/w))
    GROUP group_1
    XMLTABLE MY_PARENT_LIST
    ”/a/1/*/2”
    COLUMNS
    MY_COLLECTION XMLTYPE PATH ”TOP_PATH” VIRTUAL
    XMLTABLE MY_CHILD_LIST
    ”/TOP_PATH”
    PASSING MY_COLLECTION
    COLUMNS
    MY_ID VARCHAR2(32) PATH ‘MY_ID”,
    ID_COLLECTION XMLTYPE PATH ”ID_LIST/ID” VIRTUAL
    XMLTABLE MY_INDIVIDUAL_LIST
    ”/ID”
    PASSING ID_COLLECTION
    COLUMNS
    ANOTHER_ID VARCHAR2(32) PATH ”ANOTHER_ID”,
    ID_STATUS VARCHAR2(32) PATH ”ID_STATUS”
    GROUP group_2
    XMLTABLE MY_METADATA
    ”/a”
    COLUMNS
    data VARCHAR2(32) PATH ”c/data”,
    link VARCHAR2(32) PATH ”d/link”,
    purpose VARCHAR2(32) PATH ”d/purpose”,
    urgency VARCHAR2(32) PATH ”c/urgency”,
    case_no VARCHAR2(32) PATH ”s/case_no”,
    num_1 VARCHAR2(32) PATH ”c/normalised/num_1”,
    num_2 VARCHAR2(32) PATH ”c/normalised/num2”
    ‘);
    END;
    /

    CREATE
    INDEX MY_IDX ON TEST_TABLE
    (
    “XML_DOC”
    )
    INDEXTYPE IS “XDB”.”XMLINDEX”
    LOCAL
    PARAMETERS
    (‘PARAM indexParam’);

    The interesting here is that I couldn’t get the above code to work initially when following the example in the Oracle documentation for REGISTERPARAMETER:

    DBMS_XMLINDEX.REGISTERPARAMETER (
    ‘myIndexParam’,
    ‘PATH TABLE po_ptab
    PATH ID INDEX po_pidx
    ORDER KEY INDEX po_oidx
    VALUE INDEX po_vidx
    PATHS(NAMESPACE MAPPING(xmlns:p=”http://www.example.com/IPO”))
    GROUP MASTERGROUP XMLTABLE PO_TAB
    (”/p:PurchaseOrder”
    COLUMNS
    REFERENCE VARCHAR2(30) PATH ”p:Reference”,
    REQUESTOR VARCHAR2(30) PATH ”p:Requestor” )
    GROUP ITEMGROUP XMLTABLE ITEMGROUP_TAB
    (”/p:PurchaseOrder/p:LineItems/p:LineItem”
    COLUMNS
    LINENUMBER NUMBER(38) PATH ”@p:ItemNumber”,
    QUANTITY NUMBER(38) PATH ”@p:Quantity”,
    DESCRIPTION VARCHAR2(256) PATH ”p:Description” ));

    as it kept giving me an incorrectly coded routine error.

    After some trial and error, it turns out that I had to remove the brackets that enclose the text that come after the GROUP item_name XMLTABLE xmltable_name, as observed in the example I provided. I am not sure if it is a typo in the documentation or something that has to do with an incorrect number of quotation marks because of the string etc, but it was an interesting discovery.

    In any case, thanks for the example, that really helped.

    • June 15

      I had fun setting up the example, so glad of being of help. I created an enhancement request with Oracle, a while ago, regarding the documentation and hopefully this will get better in the next release.

Comments are closed.