This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Nov 29, 2010 2:12 PM by mdrake RSS

XMLIndex Value Index on repository

811001 Newbie
Currently Being Moderated
Hello,

I'm still fighting with that peculiar "value index" feature. Unfortunately I cannot really find comprehensive in-depth information on it at all.
Has anyone ever gotten this to work with the repository?
What I have found out so far:

1) When you have an normal table with an XMLType column and an XMLIndex on this column, then the only way to make VALUEIX show up in the EXPLAIN plan is when I write sth. like this:

SELECT rowid FROM exampletable WHERE EXISTSNODE(xmlcolumn,'/xml[leaf="green"]')=1;

2) It does NOT work if I write:

SELECT rowid FROM exampletable WHERE EXTRACTVALUE(xmlcolumn,'/xml/leaf')='green';
For some reason Oracle isn't able to detect it can use the VALUE index in the latter case.
Only PATHIX is used.

3) The type seems to be important. It does NOT work either if I write:

SELECT rowid FROM exampletable WHERE EXISTSNODE(xmlcolumn,'/xml[leaf=green]')=1;

That's OK, though. According to the documentation the value index created by default is for strings only.

4) Now, with the repository, the first thing to note is that you MUST use UNDER_PATH in your query to limit results on the folders you have created an XMLIndex on.

SELECT rowid FROM resource_view
WHERE
EXTRACTVALUE(res,'/r:Resource/r:Contents/xml/leaf,'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"')='green'
AND UNDER_PATH(res,'/public')=1

This uses only PATHIX, though. This follows the logic from above which is that EXTRACTVALUE never uses a VALUE index.

5) OK. Let's try EXISTSNODE then:

SELECT rowid FROM resource_view
WHERE
EXTRACTVALUE(res,'/r:Resource/r:Contents/xml[leaf="green"],'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"')=1
AND UNDER_PATH(res,'/public')=1

This doesnt use any xml index at all :-(

Why?
  • 1. Re: XMLIndex Value Index on repository
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    The information you need is summerized here:

    http://www.liberidu.com/blog/?p=2140

    Especially readup on storage use-case and "*Oracle XML DB : Best Practices to Get Optimal Performance out of XML Queries* (PDF)"

    If you are able to use XMLIndex than DO NOT USE extractvalue/extract/existnode etc anymore. For alternatives see the operators used in the pdf mentioned above.
  • 2. Re: XMLIndex Value Index on repository
    811001 Newbie
    Currently Being Moderated
    Interesting PDF with lots of useful information. Thank you! My original problem, though, is still not solved, unfortunately.
    Following your recommendation and the PDF I'm now trying this on a simple repository where I stored some XML documents:

    SELECT rowid
    FROM resource_view
    WHERE XMLEXISTS(
    '
    declare namespace r="http://xmlns.oracle.com/xdb/XDBResource.xsd";
    /r:Resource/r:Contents/xml[leaf="green"]
    '
    passing res
    )
    AND UNDER_PATH(res,'/public')=1

    No XML index is used.
  • 3. Re: XMLIndex Value Index on repository
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    XMLCAST/CAST
  • 4. Re: XMLIndex Value Index on repository
    811001 Newbie
    Currently Being Moderated
    Mmm. Call me stupid but for the life of me I cannot understand the short hint you've given me.
    There is XMLCAST and CAST. But all examples I can find are not used like in my query with XMLEXISTS.
    Furthermore, I'm not even using a bind statement but running the query in SQL developer instead.
    So, any errors caused by a wrong bind type can be ruled out AFAICT.
    I tried this:

    SELECT rowid
    FROM resource_view
    WHERE XMLEXISTS(
    '
    declare namespace r="http://xmlns.oracle.com/xdb/XDBResource.xsd";
    /r:Resource/r:Contents/xml/properties[username=$un]
    '
    passing res, CAST('username' AS VARCHAR2(200)) AS "un"
    )
    AND UNDER_PATH(res,'/public/users')=1

    which works but doesnt use the XML index either.
    All examples from the PDF you have recommended talk about dates, numbers, etc
    This is great to know in case I need such things in the future, but at the moment I'd be more than happy if I could compare a simple string ;-)
  • 5. Re: XMLIndex Value Index on repository
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Don't have the energy today (I'm wasted / too much traffic jams today) but I hope if you're rewriting the whole towards XQuery and than do the final check towards the XMLCAST, it maybe picked up.
    Also see odies statement here regarding a simple example: Re: xquery with Dates

    Still not sure though if the CBO (cost based optimizer) thinks its selective enough. You can force it though via a hint and see if the costs (indeed) increases...
  • 6. Re: XMLIndex Value Index on repository
    811001 Newbie
    Currently Being Moderated
    I've tried all variants over the last hours to no avail.
    Interestingly, Example 5-31 here is nearly the same query I'm doing:

    http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb_indexing.htm#CHDBGBEG

    Unfortunately, they don't give an EXPLAIN plan with that query so I don't know what they think should happen.
    Can the VALUE INDEX be used at all with a repository? My case is the simplest one I could think of. My steps:

    1) Take any XML DB repository
    2) Insert an XML file (for instance with WebDAV, doesnt matter though)
    3) Create an XMLIndex with CALL DBMS_XDB_ADMIN.CreateRepositoryXMLIndex(); and DBMS_XDB_ADMIN.XMLIndexAddPath(...)
    4) Try to make a simple XMLExists() query

    Doesnt work. No VALUE INDEX used. :-(
    Is there any way to query the VALUE INDEX of a repository manually in case this is an Oracle bug? I somehow need to make this work,
    because the repository is pretty useless otherwise if I can't query it in an efficient manner.
  • 7. Re: XMLIndex Value Index on repository
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    In principal, if I read it correctly you are going for content search within XML. You could go for the text index approach on top of the xdb resource environment. On the other hand, have you updated the statistics of the XDB schema after building the XMLIndex on top of the xdb reource environment (just checking / as said tired / dont want to reed up on all of it again)
  • 8. Re: XMLIndex Value Index on repository
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Text index on xdb hierarchy: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_xdbt.htm#sthref10905
  • 9. Re: XMLIndex Value Index on repository
    811001 Newbie
    Currently Being Moderated
    Just a quick follow-up on your proposal, Marco:

    SELECT ANY_PATH FROM RESOURCE_VIEW WHERE UNDER_PATH(res,'/public')=1 AND xmlcast(
    xmlquery('
    declare namespace r="http://xmlns.oracle.com/xdb/XDBResource.xsd";
    /r:Resource/r:Contents/xml/leaf
    ' passing res returning content)
    as varchar2(255)
    )='green';

    Doesnt work either. I dont want to use the Oracle Text index, because I'm planning to do comparisons (less, greater) etc. in the future. And I need to have absolutely exact matching. Perhaps it can be done, but it would be a very ugly workaround.
    I'd like to update statistics but - next problem - the method describe by Oracle on their own pages doesnt seem to exist:

    http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb16fol.htm

    They talk about

    DBMS_XDB_ADMIN.gatherRepositoryStats

    I dont have such a procedure in DBMS_XDB_ADMIN.
  • 10. Re: XMLIndex Value Index on repository
    811001 Newbie
    Currently Being Moderated
    Another follow-up: While DBMS_XDB_ADMIN.gatherRepositoryStats obviosuly doesnt exists, I tried

    DBMS_STATS.GATHER_SCHEMA_STATS ('XDB');

    It didnt change the EXPLAIN plan, however. Sigh.
  • 11. Re: XMLIndex Value Index on repository
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    That should have been good enough for these 3 babies http://www.liberidu.com/blog/?p=310 but apparently that didn't do the job yet.
  • 12. Re: XMLIndex Value Index on repository
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Never done it yet, but maybe you can force the use of the path table or path table needed index via hints like
    select /*+ leading(t) */
    from mytab t
    
    or
    
    select /*+ index........
    from ....
  • 13. Re: XMLIndex Value Index on repository
    811001 Newbie
    Currently Being Moderated
    I've tried lots of hints and different variations. Nothing works. Unfortunately it is completely undocumented which hints are applicable
    for the repository. Giving up for today. What puzzles me the most is that nobody seems to have thought of this before. If I used a complex query with different predicates I would much easier understand why Oracle isnt using some index. But it seems XMLIndex isnt used in the repository at all. But, then again, why are there such commands like createRepositoryIndex if such an index isnt used even with the simplest statement you can think of? Very strange. My only option is to try 11.2 and see if this improves the situation. I havent one readily installed, though.
  • 14. Re: XMLIndex Value Index on repository
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Will make a run at it tomorrow in my 11.2.0.2.0 instance
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points