XMLIndex (Part 3) – XMLIndex Syntax Dissected

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
 

 
Marco Gralike Written by:

2 Comments

  1. Johannes
    October 8

    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

  2. October 12

    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.

Comments are closed.