DB2 pureXML index usage in ORDER BY clause

Question

I got a problem with the index usage in DB2 XML. I have a database table with users, consisting of id + xml document + timestamp. The thing i'm interested in is to find users and order them by join date.

The index definition:

create index idx_joindate on "Users"("userXML") generate keys 
    using xmlpattern '/*:User/*:joinDate' as sql timestamp;

The SQL query:

SELECT "Users".* FROM "Users" 
  ORDER BY 
   XMLCAST(
    XMLQUERY('$i/*:User/*:joinDate' PASSING "userXML" AS "i") 
   as TIMESTAMP) DESC
FETCH FIRST 20 ROWS ONLY

The XQuery:

xquery
let $sorted :=
   for $user in db2-fn:xmlcolumn("Users.userXML")
   order by xs:dateTime($user/*:User/*:joinDate) descending
   return $user
for $user in subsequence($sorted, 1, 20)
return $user

Both queries actually work fine, BUT I expect them to use the defined index on the joinDate. This does not happen for neither XQuery nor SQL query.

http://amolnpujari.wordpress.com/2008/04/12/db2-native-xml/ might help you — Amol Pujari, Mar 20 '14 at 11:14

score 0 · Answer 1 · answered Feb 27 '14 at 15:13

I'm assuming you expect DB2 to use the XML index to avoid sorting table rows, however, XML indexes store pointers to XML document nodes, possibly multiple nodes for each row, rather than RIDs. XML data are stored in separate physical structures, so I don't think XML indexes can be used the way you want.

DB2 pureXML index usage in ORDER BY clause

1 Answers1