1 2 3 Previous Next 33 Replies Latest reply: Nov 29, 2010 4:12 PM by mdrake RSS

    XMLIndex Value Index on repository

    811001
      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
          Marco Gralike
          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
            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
              Marco Gralike
              XMLCAST/CAST
              • 4. Re: XMLIndex Value Index on repository
                811001
                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
                  Marco Gralike
                  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
                    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
                      Marco Gralike
                      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
                        Marco Gralike
                        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
                          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
                            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
                              Marco Gralike
                              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
                                Marco Gralike
                                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
                                  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
                                    Marco Gralike
                                    Will make a run at it tomorrow in my 11.2.0.2.0 instance
                                    1 2 3 Previous Next