3 Replies Latest reply: Sep 11, 2012 11:37 AM by 308988 RSS

    Which index to chose?

    929890
      I have a bunch of tables each with just two columns named id and xml.
      In the xml column complete XML documents are stored which have a different structure depending on the table they are stored into.

      What would be the proper choice of indexing strategy to better the performance when those XML documents are queried for?
      Typically XML documents are queried by a certain code/id or so, e.g.:
      SELECT xml FROM myTbl WHERE XMLEXISTS('$xmlCol/object/object[@id="xyz"]="abc"' PASSING xml as $xmlCol)
      Thank you.
        • 1. Re: Which index to chose?
          Marco Gralike
          Follow the advice given in the whitepaper: "Oracle XML DB : Best Practices to Get Optimal Performance out of XML Queries (PDF) Nov 2011" (see: http://www.oracle.com/technetwork/database/features/xmldb/index.html)
          • 2. Re: Which index to chose?
            929890
            When I conduct a search for "Oracle XML DB : Best Practices to Get Optimal Performance out of XML Queries" Oracle offers a result with a link to a PDF from 2009 which in turn leads me to the PDF "Oracle XML DB: Best Practices for querying and updating XML content using Oracle Database release 11.2.0.3.".

            Well I have already looked into [Oracle XML DB: Best Practices for querying and updating XML content using Oracle Database release 11.2.0.3.|http://www.oracle.com/technetwork/database/features/xmldb/xmlqueryoptimize11gr2-168036.pdf] but it's not so easy to understand.
            • 3. Re: Which index to chose?
              308988
              Check this presentation for some examples and sample code:
              Design flexible data models leveraging Oracle 11gR2 XML indexing capabilities
              http://www.arisprassinos.com/id1.html