Oracle version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
We're posting Site Configuration and Stock Level data as XML to an Azure API, and storing the XML transmitted in API_LOG.API_XML (of type XMLTYPE). Updates are sent throughout the day, and we've been XMLTable'ing API_LOG.API_XML to retrieve previously sent data to exclude those rows without changes. As the data sent each hour has increased, we hit contention issues (due to hard parse?) and an escalation of Cursor pin s wait on x events, causing the queries to take over an hour, prompting this look at XMLIndex. (I'm not the DBA here, so am foggy on the specifics.)
XMLIndexing everything speeds up both queries dramatically, but I'd like to use XML path subsetting to stop the index ballooning, given the volume of data we send. The XML sent to the API uses two namespaces, Site_Config_Report and Stock_Level_Report, and I'm struggling to create an XMLIndex that encompasses only those paths of interest and that includes more than one namespace, running into ORA-29879: cannot create multiple domain indexes on a column list using same indextype.
That XMLIndex'ing everything speeds up both queries suggests this is possible. Based on helpful posts including those from odie_63 and Marco Gralike, creating an XMLIndex for each format separately also works, but finding the syntax that combines all paths with two namespaces is proving elusive. Thank you for any help at all on offer!
N.B. The code here has had to be sanitised for client confidentiality and pared for brevity, so whilst there may be typos and missing fields, it's (hopefully) broadly correct. And whilst there are two namespaces used, more may be added. Thank you.
Sample Site Config XML
<SiteConfig xmlns="Site_Config_Report">
<Sites>
<Site>
<SiteNumber>12345</SiteNumber>
<SiteName>Some Customer Site</SiteName>
<TankGroups>
<TankGroup>
<TankGroupNumber>1</TankGroupNumber>
<ProductName>Unleaded</ProductName>
<Tanks>
<Tank>
<TankNumber>1</TankNumber>
<TankShape>4</TankShape>
</Tank>
<Tank>
<TankNumber>2</TankNumber>
<TankShape>4</TankShape>
</Tank>
</Tanks>
</TankGroup>
<TankGroup>
<TankGroupNumber>2</TankGroupNumber>
<ProductName>Diesel</ProductName>
<Tanks>
<Tank>
<TankNumber>3</TankNumber>
<TankShape>1</TankShape>
</Tank>
</Tanks>
</TankGroup>
</TankGroups>
</Site>
<Site>
..
</Site>
</Sites>
</SiteConfig>
Sample Stock Level XML
<StockLevels xmlns="Stock_Level_Report">
<Sites>
<Site>
<SiteNumber>12345</SiteNumber>
<TankGroups>
<TankGroup>
<TankGroupNumber>1</TankGroupNumber>
<Tanks>
<Tank>
<TankNumber>1</TankNumber>
<StockQuantity>7256</StockQuantity>
</Tank>
<Tank>
<TankNumber>2</TankNumber>
<StockQuantity>9000</StockQuantity>
</Tank>
</Tanks>
</TankGroup>
<TankGroup>
<TankGroupNumber>2</TankGroupNumber>
<Tanks>
<Tank>
<TankNumber>3</TankNumber>
<StockQuantity>15616</StockQuantity>
</Tank>
</Tanks>
</TankGroup>
</TankGroups>
</Site>
<Site>
...
</Site>
</Sites>
</StockLevels>
A subset of the SQL used to retrieve Site Config XML already posted to the API
select site_number, site_name, ...
from (
select api_id,
api_xml,
created_date
from api_log
where api_type = 1
and created_date >= trunc(cast(sys_extract_utc(systimestamp) as date))
and response_status between 200 and 299) ap,
XmlTable(XmlNamespaces(default 'Site_Config_Report'),
'/SiteConfig/Sites/Site' passing api_xml
columns site_number varchar2(10) path './SiteNumber',
site_name varchar2(40) path './SiteName',
tank_group xmltype path 'TankGroups/TankGroup') xs,
XmlTable('./TankGroup' passing tank_group
columns tank_group_number varchar2(10) path './TankGroupNumber',
product_name varchar2(20) path './ProductName',
tank xmltype path 'Tanks/Tank') xg,
XmlTable('./Tank' passing tank
columns tank_number varchar2(10) path './TankNumber',
tank_shape varchar2(1) path './TankShape') xt))
A subset of the SQL used to retrieve Stock Level XML already posted to the API
select site_number, tank_number, stock_quantity
from (select site_number,
tank_number,
stock_quantity,
rank() over(partition by site_number, tank_number order by created_date desc, rownum) as reading_rank
from (select api_xml,
created_date
from api_log
where api_type = 2
and created_date >= trunc(cast(sys_extract_utc(systimestamp) as date), 'hh')
and response_status between 200 and 299) ap,
XmlTable(XmlNamespaces(default 'Stock_Level_Report'),
'/StockLevels/Sites/Site' passing api_xml
columns site_number varchar2(10) path './SiteNumber',
tank xmltype path 'TankGroups/TankGroup/Tanks/Tank') xs,
XmlTable('./Tank' passing tank
columns tank_number varchar2(10) path './TankNumber',
stock_quantity varchar2(16) path './StockQuantity') xt)
where reading_rank = 1
XMLIndex EVERYTHING
create index api_log_xml on api_log(api_xml) indextype is xdb.xmlindex
parameters ('PATH TABLE API_LOG_PATHTAB');
XMLIndex just the data required by Site Config
create index api_log_xml on api_log(api_xml) indextype is xdb.xmlindex
parameters ('PATHS (INCLUDE (/SiteConfig/Sites/Site
/SiteConfig/Sites/Site/SiteNumber
/SiteConfig/Sites/Site/SiteName
/SiteConfig/Sites/Site/TankGroups/TankGroup
/SiteConfig/Sites/Site/TankGroups/TankGroup/TankGroupNumber
/SiteConfig/Sites/Site/TankGroups/TankGroup/ProductName
/SiteConfig/Sites/Site/TankGroups/TankGroup/Tanks/Tank/*)
NAMESPACE MAPPING (xmlns="Site_Config_Report"))');
XMLIndex just the data required by Stock Level
create index api_log_xml on api_log(api_xml) indextype is xdb.xmlindex
parameters ('PATHS (INCLUDE (/StockLevels/Sites/Site
/StockLevels/Sites/Site/SiteNumber
/StockLevels/Sites/Site/TankGroups/TankGroup/Tanks/Tank/TankNumber
/StockLevels/Sites/Site/TankGroups/TankGroup/Tanks/Tank/StockVolume)
NAMESPACE MAPPING (xmlns="Stock_Level_Report"))');