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
3 223
4 001 002 003 224
8 005 006 002
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
3 223
4 001 002 003 224
8 005 006 002
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
🙂
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.
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.
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.
I created an example for you on how to do it.
See the blog post (if I come to it to write one on this subject) or investigate the code posted here:
http://forums.oracle.com/forums/thread.jspa?threadID=2234618
HTH
😎
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.
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.