My Oracle Support Banner

Tuning X Path Queries Using B-Tree Indexes (Doc ID 553358.1)

Last updated on DECEMBER 26, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
Checked as Top Hit on 10-NOV-2011

Purpose

This is a worked example of the tuning issues mentioned in the Oracle XML DB Developer's Guide Chapter 3 under Understanding and Optimizing XPath Rewrite, specifically using B-Tree indexes on the Nested Tables associated with an XMLType table.

I have deliberately simplified the XML structures here to just include points of interest. In real life applications the XML definition would be a lot more complex. However the principles remain the same.

Scope

Requirement:

Given this xml file - the requirement is to quickly query Quotes for a given  set of Origin ID's:

<?xml version="1.0"?>
<Quote>
<Identifier>TKY06223003</Identifier>
  <Version>2</Version>
  <CorridorRates>
  <PointPairs>
  <OriginID>461802935876668</OriginID>
  <DestinationID>461802935876871</DestinationID>
  <OutboundMode>Y</OutboundMode>
  <InboundMode>Y</InboundMode>
  </PointPairs>
  <PointPairs>
  <OriginID>461802935876684</OriginID>
  <DestinationID>461802935876871</DestinationID>
  <OutboundMode>Y</OutboundMode>
  <InboundMode>Y</InboundMode>
  </PointPairs>
  <PointPairs>
  <OriginID>461802935876743</OriginID>
  <DestinationID>461802935876871</DestinationID>
  <OutboundMode>Y</OutboundMode>
  <InboundMode>Y</InboundMode>
  </PointPairs>
  </CorridorRates>
  <CorridorRates>
  <PointPairs>
  <OriginID>461802935876678</OriginID>
  <DestinationID>461802935876882</DestinationID>
  <OutboundMode>Y</OutboundMode>
  <InboundMode>Y</InboundMode>
  </PointPairs>
 </CorridorRates>
</Quote>

i.e Optimize queries similar to this:

select count(*) from tnm_agreement_xml
where existsNode(object_value, '/Quote/CorridorRates/PointPairs[OriginID="461802935876668"]') = 1;


Note here that the Origin Id's appear in the PointPairs repeating group which is itself in the CorridorRates repeating group.

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Scope
Details
 Creating your own schema based table:
 Sample output:
 Conclusions
 Additional Scripts Used Above

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.