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
/ROOT/ID
/ROOT/INFO/INFO_ID
xmlns="http://localhost/public/xsd/myapp.xsd"
SYS63362_XMLBIN_IX_XIDX_SYNCJ
FREQ=HOURLY; INTERVAL = 1
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
Hi,
thanks for your great tutorial. I managed to set up an XMLIndex using 11g R2. This works fine so far. We’re using periodically synchronizsation for the index, but for some operation, we’d like to do a manual synchronisation:
EXEC DBMS_XMLINDEX.SyncIndex(‘HORUS_OWNER’, ‘XMLBIN_IX’);
This is fine when I’m logged in as HORUS_OWNER. But for security reasons, our java-application accesses the database as HORUS_PROXY instead of the schema owner. And as this user, I’m getting the following error:
java.sql.SQLException: ORA-01418: specified index does not exist
ORA-06512: at “XDB.DBMS_XMLINDEX”, line 34
ORA-06512: at line 1
Any ideas which privileges my HORUS_PROXY user needs to be able to call the sync-method? I tried lots of role- or priv-grants without success, and the documentation doesn’t say anything about needed privileges. Sys as sysdba is allowed to update the index, whereas system isn’t.
any hints?
-orgler
I didn’t try it yet and it depends how you actually have set it up (who owns what objects) but I would expect that the only thing you probably would need is to grant (as an extra) object privilges as HORUS_OWNER to the HORUS_PROXY user directly.