This discussion is archived
3 Replies Latest reply: Sep 11, 2012 9:37 AM by 308988 RSS

Which index to chose?

929890 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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

Legend

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