XMLIndex Performance and Fuzzy XPath Searches

Learned yesterday an important lesson on the Oracle OTN XMLDB forum. A question was asked why a count via a full table scan was quicker than the same example while using an XMLIndex. “XMLIndex performance regarding // (any descendant))”. The poster used the // xpath expression to do his search.

As in most cases nowadays it is important to keep your statistics in order. An XMLIndex is not an “index” as such, it is an logical index / domain index, specially designed for use with XMLDB / XML data.

Although statistics were present in this case, the count via the full table scan was quicker. Without a full explain plan of the presented example, it is a little bit difficult to really understand what went on, but Geoff (one of the members of the XMLDB Development team) solved the issue via (re)generation of stats on three XDB X$ Tables.

These “token” tables can be found via the following statement


SQL> select table_name
  2  ,      to_char(last_analyzed,'YYYY/MM/DD') "ANALYZED"
  3  from dba_tables
  4  where table_name like 'X$%'
  5  and owner = 'XDB';

TABLE_NAME                     ANALYZED
------------------------------ ----------
X$QN31S9LBVEWT9PUP3MTHPTT2K9VT 2007/07/09
X$PT31S9LBVEWT9PUP3MTHPTT2K9VT 2007/07/09
X$NM31S9LBVEWT9PUP3MTHPTT2K9VT 2007/04/03

3 rows selected.

SQL> desc XDB.X$QN31S9LBVEWT9PUP3MTHPTT2K9VT

 Name                          Null?    Type
 ----------------------------- -------- ---------------
 NMSPCID                                RAW(8)
 LOCALNAME                              VARCHAR2(2000)
 FLAGS                                  RAW(4)
 ID                                     RAW(8)

SQL> desc XDB.X$PT31S9LBVEWT9PUP3MTHPTT2K9VT

 Name                          Null?    Type
 ----------------------------- -------- ---------------
 PATH                                   RAW(2000)
 ID                                     RAW(8)

SQL> desc XDB.X$NM31S9LBVEWT9PUP3MTHPTT2K9VT

 Name                          Null?    Type
 ----------------------------- -------- ---------------
 NMSPCURI                               VARCHAR2(2000)
 ID                                     RAW(8)

SQL> 

As noted by Geoff Lee on the OTN XMLDB forum:

For a // query, xmlindex rewrite uses the token tables. Therefore, stats should be gathered on these tables as well. There are 3 token tables. They are owned by XDB, and their names begin with ‘X$’.

and, as an answer on the two very valid questions of the poster:

  • Should I always gather table statistics on those token tables every time after I create new XMLIndexes?
  • What can cause the content of these token tables to change?

One example that a token table content may change is when unique paths are changed during DML operations.
(XMLIndex) Path subsetting is critical for keeping in check the index size, which does affect index scan performance.

Using an XMLIndex with constraints (=path subsetting) can create an very huge XMLIndex, so in that sense (but I have to check), I can imagine that the index BLEVEL or LEAF_BLOCKS increase likewise.

M.

Update

In an almost vanilla database (version 11.1.0.4.0 beta for Windows):

SQL> select count(*) 
  2  from XDB.X$QN31S9LBVEWT9PUP3MTHPTT2K9VT;

 COUNT(*)
---------
      452

1 row selected.

SQL> select INDEX_NAME,
  2         INDEX_TYPE,
  3         BLEVEL,
  4         LEAF_BLOCKS,
  5         DISTINCT_KEYS
  6  from   dba_indexes
  7  where table_name='X$QN31S9LBVEWT9PUP3MTHPTT2K9VT';

INDEX_NAME                     INDEX_  BLEVEL LEAF_BLOCKS  DISTINCT
------------------------------ ------ ------- ----------- ---------
X$QS31S9LBVEWT9PUP3MTHPTT2K9VT NORMAL       0           1        11
X$QQ31S9LBVEWT9PUP3MTHPTT2K9VT NORMAL       1           3       452
X$QI31S9LBVEWT9PUP3MTHPTT2K9VT NORMAL       0           1       452

3 rows selected.

SQL> select * from XDB.X$QN31S9LBVEWT9PUP3MTHPTT2K9VT
  2  where rownum < = 5;

NMSPCID
----------------
LOCALNAME
-----------------------------------------------------
FLAGS    ID
-------- ----------------
06FB
CADANS-JWB
00       2DAA

07
xmlns
01       1696

06FB
PERSOON
00       527F

06FB
C100-SOC-FSC-NMR
00       5909

06FB
R100-PRS
00       6808


5 rows selected.

SQL>

SQL> select count(*)
  2  from XDB.X$PT31S9LBVEWT9PUP3MTHPTT2K9VT
  3  ;

 COUNT(*)
---------
      289

1 row selected.

SQL> select INDEX_NAME,
  2         INDEX_TYPE,
  3         BLEVEL,
  4         LEAF_BLOCKS,
  5         DISTINCT_KEYS
  6  from   dba_indexes
  7  where table_name='X$PT31S9LBVEWT9PUP3MTHPTT2K9VT';

INDEX_NAME                     INDEX_  BLEVEL LEAF_BLOCKS  DISTINCT
------------------------------ ------ ------- ----------- ---------
X$PP31S9LBVEWT9PUP3MTHPTT2K9VT NORMAL       1           2       289
X$PI31S9LBVEWT9PUP3MTHPTT2K9VT NORMAL       0           1       289
X$PR31S9LBVEWT9PUP3MTHPTT2K9VT FUNCTI       1           2       289
                               ON-BAS
                               ED NOR
                               MAL


3 rows selected.

SQL> select * from XDB.X$PT31S9LBVEWT9PUP3MTHPTT2K9VT
  2  where rownum < = 5;

PATH
-----------------------------------------------------
ID
----------------
022DAA
6CCE

022DAA021696
2F9E

022DAA02527F
5D99

022DAA02527F025909
6854

022DAA02527F026808
4DF9

5 rows selected.

SQL>

SQL> select count(*)
  2  from XDB.X$NM31S9LBVEWT9PUP3MTHPTT2K9VT;

 COUNT(*)
---------
       16

1 row selected.

SQL> select INDEX_NAME,
  2         INDEX_TYPE,
  3         BLEVEL,
  4         LEAF_BLOCKS,
  5         DISTINCT_KEYS
  6  from   dba_indexes
  7  where table_name='X$NM31S9LBVEWT9PUP3MTHPTT2K9VT';

INDEX_NAME                     INDEX_  BLEVEL LEAF_BLOCKS  DISTINCT
------------------------------ ------ ------- ----------- ---------
X$NN31S9LBVEWT9PUP3MTHPTT2K9VT NORMAL       0           1        12
X$NI31S9LBVEWT9PUP3MTHPTT2K9VT NORMAL       0           1        12

2 rows selected.

SQL> select * from XDB.X$NM31S9LBVEWT9PUP3MTHPTT2K9VT
  2  where rownum < = 5;

NMSPCURI
---------------------------------------------------------
ID
----------------
http://www.gralike.com/cadans/historie/JWB/CADANS-JWB-HISTORIE
06FB

http://www.w3.org/XML/1998/namespace
01

http://www.w3.org/XML/2000/xmlns
02

http://www.w3.org/2001/XMLSchema-instance
03

http://www.w3.org/2001/XMLSchema
04


5 rows selected.

SQL>

See also $ORACLE_HOME/rdbms/admin/catxdbtm.sql

Marco Gralike Written by:

2 Comments

  1. December 28

    The token tables and indexes are created during $ORACLE_HOME/rdbms/admin/catxdbtm.sql

Comments are closed.