If you start working with the XMLIndex functionality, than you start noticing that the syntax is to read in the XMLDB Developers Guide (chapter 5 of the manual). Despite to most ingredients are already known, because they are the same as during creation of normal indexes, the parameter clause can be a hassle.
Syntax
The manual describes it as follows:
PARAMETERS Clause for CREATE INDEX and ALTER INDEX
This section presents the syntax for the PARAMETERS clause of SQL statements CREATE INDEX and ALTER INDEX for use with XMLIndex.
PARAMETERS Clause Syntax for CREATE INDEX and ALTER INDEX
The PARAMETERS clause is PARAMETERS (‘XMLIndex_parameters’), where XMLIndex_parameters is one or more repetitions of XMLIndex_parameter_clause:
XMLIndex_parameters ::=
XMLIndex_parameter_clause [XMLIndex_parameter_clause]...XMLIndex_parameter_clause ::=
{ { PATHS { create_index_paths_clause | alter_index_paths_clause } } | { path_table_clause | path_id_clause | order_key_clause | xmlindex_value_clause } [parallel_clause] | ASYNC ( SYNC { ALWAYS | MANUAL | EVERY repeat_interval | ON COMMIT } [ STALE ({FALSE | TRUE}) ] ) }create_index_paths_clause ::=
( { INCLUDE | EXCLUDE } (Xpaths_list) [namespace_mapping_clause] )alter_index_paths_clause ::=
( { INDEX_ALL_PATHS | { INCLUDE | EXCLUDE} {ADD | REMOVE} ( Xpaths_list ) [namespace_mapping_clause] } )namespace_mapping_clause ::=
NAMESPACE MAPPING ( { namespace } ... )path_table_clause ::=
PATH TABLE [identifier] [ (segment_attributes_clause table_properties) ]path_id_clause ::=
PATH ID [INDEX [identifier] [(index_attributes)]]order_key_clause ::=
ORDER KEY [INDEX [identifier] [(index_attributes)]]xml_index_value_clause ::=
VALUE [INDEX [identifier] [(index_attributes)]].
Example
As always, if you have figured it out then it isn’t that difficult anymore. So to prevent some frustration, hereby I share my knowledge regarding the syntax.
See below for an (almost) complete example of an XMLIndex.
CREATE INDEX XMLINDEX_IX ON XMLBINARY_TBL (object_value) INDEXTYPE IS XDB.XMLIndex PARAMETERS ('PATHS (INCLUDE (/ROOT/ID /ROOT/INFO/INFO_ID ) NAMESPACE MAPPING (xmlns="http://localhost/xmlschema_bin.xsd") ) PATH TABLE root_path_table PATH ID INDEX root_pathid_ix ORDER KEY INDEX root_orderkey_ix ASYNC (SYNC ALWAYS) STALE (FALSE) ') PARALLEL;
Some pointers regarding this example:
- The XML Binary table is of type XMLType table
- The XML Schema used for this XML Binary table is registered for binary use
- The XMLIndex was created in parallel. I noticed that you are not allowed to insert extra data, after the index has been created in PARALLEL. I got the following warning:
ERROR at line 1:
ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
ORA-12838: cannot read/modify an object after modifying it in parallel - A reference to an XML Schema namespace reference has been added for completeness
- Using ASYNC or SYNC creates an “pending table” called something like “PEND_TABLE_NAME”
- The STALE option is for future reference (Optional ASYNC syntax parameter STALE is intended for possible future use; you need never specify it explicitly…)
Synchronization
Though the default synchronization proces is: “indexing is updated (maintained) at each DML operation, so that it remains in sync with the base table”
A “pending table“, just like the “path table” of an XMLIndex, can not be queried (it can be renamed though, the “rename” statement works, but I have no idea if this officially supported).
SQL> SELECT * FROM SYS63335_XMLBIN_XML_PEND_TABLE; SELECT * FROM SYS63335_XMLBIN_XML_PEND_TABLE * ERROR at line 1: ORA-30958: operation directly ON the Pending TABLE IS disallowed SQL> DESC SYS63335_XMLBIN_XML_PEND_TABLE Name NULL? Type -------------------- -------- ---------------------------- RID ROWID OPERATION VARCHAR2(2) ERRNUM NUMBER ERRMSG VARCHAR2(2000) .
You are allowed to manually sync this process or schedule this via the Oracle scheduler (DBMS_SCHEDULER) method. Probably that is the reason that a “pending table” is created automatically. The data inserted here will be used by the scheduler and can be queried as show in the following example:
SQL> CREATE INDEX XMLBIN_IX ON XMLBIN (object_value) 2 INDEXTYPE IS XDB.XMLIndex 3 PARAMETERS ('PATHS (INCLUDE (/ROOT/ID 4 /ROOT/INFO/INFO_ID 5 ) 6 NAMESPACE MAPPING (xmlns="http://localhost/public/xsd/myapp.xsd")) 7 PATH TABLE xmlbin_path_table 8 PATH ID INDEX xmlbin_pathid_ix 9 ORDER KEY INDEX xmlbin_orderkey_ix 10 ASYNC (SYNC EVERY "FREQ=HOURLY; INTERVAL = 1") 11 ') 12 PARALLEL 4; SQL> SELECT * FROM user_segments WHERE segment_name LIKE "%PEND%'; SEGMENT_NAME -------------------------------------------------------------------------------- PARTITION_NAME SEGMENT_TYPE SEGMENT_SU ------------------------------ ------------------ ---------- TABLESPACE_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT ------------------------------ ---------- ---------- ---------- -------------- NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE RETENTI MINRETENTION PCT_INCREASE ----------- ----------- ----------- ---------- ------- ------------ ------------ FREELISTS FREELIST_GROUPS BUFFER_ ---------- --------------- ------- SYS63362_XMLBIN_IX_PEND_TABLE TABLE ASSM USERS 65536 8 1 65536 1 2147483645 2147483645 DEFAULT SQL> select * from user_xml_indexes; INDEX_NAME TABLE_OWNER ------------------------------ ------------------------------ TABLE_NAME PATH_TABLE_NAME ------------------------------ ------------------------------ PARAMETERS -------------------------------------------------------------------------------- ASYNC STALE PEND_TABLE_NAME TYPE EX_OR_IN --------- ----- ------------------------------ ---------- -------- XMLBIN_IX MARCO XMLBIN XMLBIN_PATH_TABLE <parameters> <paths> <path>/ROOT/ID</path> <path>/ROOT/INFO/INFO_ID</path> <nsmap count="1">xmlns="http://localhost/public/xsd/myapp.xsd"</nsmap> </paths> <async> <job> <name>SYS63362_XMLBIN_IX_XIDX_SYNCJ</name> <interval>FREQ=HOURLY; INTERVAL = 1</interval> </job> </async> </parameters> EVERY SYS63362_XMLBIN_IX_PEND_TABLE CSX INCLUDE SQL> set head off SQL> select * from user_scheduler_jobs; SYS63362_XMLBIN_IX_XIDX_SYNCJ REGULAR MARCO PLSQL_BLOCK dbms_xmlindex.SyncIndex('MARCO', 'XMLBIN_IX'); 0 CALENDAR 20-AUG-07 03.39.01.234700 PM EUROPE/VIENNA FREQ=HOURLY; INTERVAL = 1 DEFAULT_JOB_CLASS TRUE TRUE FALSE SCHEDULED 3 1 0 0 20-AUG-07 03.39.01.322236 PM EUROPE/VIENNA +000000000 00:00:00.078865 20-AUG-07 04.39.01.300000 PM EUROPE/VIENNA OFF FALSE TRUE FALSE 1 NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC Y='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT ='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH. MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT= 'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_ DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_E XCP='FALSE' FALSE 133172
You are also allowed to synchronize the XMLIndex by hand via the DBMS_XMLINDEX.SyncIndex method.
EXEC DBMS_XMLINDEX.SyncIndex(USER, 'XMLBIN_IX');
Index Synchronization
(Chapter 5, XMLDB Developers Guide for Oracle 11g)
When to Synchronize ASYNC Clause Syntax ============================= ===================================== Always ASYNC (ALWAYS) This is the default behavior. You can specify it explicitly, to cancel a previous ASYNC specification. Upon commit ASYNC (ON COMMIT) Periodically ASYNC (EVERY "repeat_interval") repeat_interval is the same as for the calendaring syntax of DBMS_SCHEDULER. To use EVERY, you must have the CREATE JOB privilege. Manually, on demand ASYNC (MANUAL) You can manually synchronize the index using PL/SQL procedure DBMS_XMLINDEX.SyncIndex.
I hope this was of help.
Related Posts
- Oracle 11g – XMLIndex (Part 2) – XMLIndex Path Subsetting
- Oracle 11g – XMLIndex (part 1) – An Introduction
- Oracle 11g – XMLType Storage Options
- XMLIndex related
