0

My table looks like this:

CREATE TABLE "Content" (
    "id" VARCHAR(36) NOT NULL,
    "xml" XML
);

I have an index defined

create index "idx_creation_date" on "Content"("xml") generate keys 
    using xmlpattern '/*/dateCreated' as sql timestamp;

This index works fine when using it in comparisons (XMLEXISTS), but now I want to use that index to sort my table according with the date my content was created and select the first 20 items.

Sadly, this didn't work

SELECT "Content".* FROM "Content" 
    ORDER BY XMLCAST(XMLQUERY('$xml/*/dateCreated' PASSING "xml" as "xml")
    as TIMESTAMP) DESC FETCH FIRST 20 ROWS ONLY

because XMLQUERY doesn't use indexes. The same goes for

xquery
let $items :=
   for $i in db2-fn:xmlcolumn("Content.xml")
   order by $i/*/dateCreated
   return $i
for $i at $count in subsequence($items, 1, 20)
return $i

because let and return can't use indexes.

Anyone has an idea how I can use the existing (and working) index on dateCreated in a ORDER BY query?

  • I don't think an XML index can ever be used for ordering relational rows, because its entries point to individual XML nodes within each XML document (and there can be multiple matching nodes in a document) and not to a table row. – mustaccio Oct 6 '16 at 16:32
  • @mustaccio True, but at least the xquery should work, shouldn't it? There no relational data is involved. Again: the index works fine on existential predicates (xmlexists). All the query optimizer would have to do is to order the index data by date, return the first 20 node pointers which correspond with certain relational rows. The 20 rows could be loaded traditionally then. – Martin Müller Oct 7 '16 at 11:41
0

Indexes over XML data (a.k.a. "XML Index") differ from relational indexes in several ways. They are used to identify documents and the position of the nodes within for the indexed pattern. Several nodes within a single XML document may match that pattern. For a relational index each row is indexed, for an XML index there might be zero, one or more matches per document (which is stored as part of a relational row). Some more information can be found in the DB2 documentation on Indexing XML Data.

By SQL Standard the XML data type is kind of a black box. It does not allow comparison and hence sorting. What you are trying to do is sort an XML fragment by a specific node. The fragment is already derived from the stored XML value/document. At the time of the processing there could be no connection between the XML index and the fragment. A good reference on XML indexes and their usage was written by Matthias Nicola.

With that background information we can now focus on your specific question. How would DB2 know that a timestamp/date value is present for each row and the related "Content"? How should the sorting be organized? Because of the index entry/document/row relationship outlined above the sorting in the SQL statement won't work.

For your XQuery I assume that the data is untyped, no schema is used. How would DB2 know that dateCreated is of timestamp and that the index should be used? Have you tried a cast around the dateCreated to let DB2 know that timestamp data is expected?

BTW: You can specify multiple XML indexes for the same pattern, but with different data types.

  • Thank you for your answer. I tried casting to xs:dateTime but that didn't change anything.In fact, I start understanding what you said: the DBMS isn't sure if the property exists and therefore it doesn't use the index. But how can we order by a specific xml node efficiently then? – Martin Müller Oct 12 '16 at 10:10
  • I added more details. I assume that you looked at the query plan and noticed that the index wasn't used for the sort. Was it used at all? How big is your data set? – data_henrik Oct 12 '16 at 11:40
  • Indeed, the query plan showed that the XML index is not used when I use sort (however a relational index is used though I don't even touch that column). I ran several other queries to determine that the index was correctly set up and whenever I used it as a predicate (xmlexists) it worked. My data set is about 20.000 rows, the database is roughly 500MB. – Martin Müller Oct 13 '16 at 8:11
  • The index is designed to be used with xmlexists. It helps to decide whether a specific pattern is matched by a doc and where. Could you elaborate on the relational index? – data_henrik Oct 13 '16 at 11:54
  • I have an Index on "id" which is used even if the query doesn't use that column. i think this is some internal optimization and doesn't matter. Semms the only reliable way to get ordering working with index would be to replicate the xml value as a relational timestamp column. That is some extra work but it can be done. – Martin Müller Oct 13 '16 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.