How to optimize a XQUERY in a SELECT statement?

Question

I am using Oracle database. I have a table in which one of the Column is of XMLTYPE. Now, the problem statement is that I need to extract the count of those record that have an XML with a particular root element and one more condition. Suppose the XML stored are of following formats:

<ns1:Warehouse whNo="102" xmlns:ns1="xyz">
  <ns1:Building></ns1:Building>
</ns1:Warehouse>

and

 <ns1:Warehouse whNo="102" xmlns:ns1="xyz">
  <ns1:Building>Owned</ns1:Building>
 </ns1:Warehouse>

and there are other XMLs with Root elements other than Warehouse

Now, I need to fetch those records which have

Root element as Warehouse
Building element as empty

I wrote the following SQL query:

select count(XMLQuery('declare namespace ns1="xyz.com";
                       for $i in /*      
                       where fn:local-name($i) eq "Warehouse"  
                       and fn:string-length($i/ns1:Building ) = 0
                       return <Test>{$i/ns1:Building}</Test>'
                       PASSING xml_response RETURNING CONTENT)) countOfWareHouses
 from test

Here, test is the name of the table and *xml_response* is the name of the XMLTYPE column in the table test.

This query works fine when the records are less. I have tested it for around 500 records in the table and the time it takes is around 0.1s. But as you increase the number of records in the table, the time increases. When I increased the number of records to 5000, the time it took was ~11s. And for a production table, where the number of records currently stored are 185000, this query never completes.

Please help me to optimize this query or the xquery.

Edit 1:

I tried using this:

select count(XMLQuery(
         'declare namespace ns1 = "xyz";
         for $i in /
         return /ns1:Warehouse[not(ns1:Building/text())]'
         PASSING xml_response RETURNING CONTENT))
from test

and

 select count(XMLQuery(
         'declare namespace ns1 = "xyz";
         return /ns1:Warehouse[fn:string-length(ns1:Building)=0]'
         PASSING xml_response RETURNING CONTENT))
from test

But this is not working.

When I try to run these, it asks for binding values for Building and Warehouse.

Your xquery looks too complex. I bet one can count the empty elements in xquery. This [answer](http://stackoverflow.com/a/12448506/272735) seems to address your issue with the exception it is counting non-empty elements. Hope it helps ! — user272735, Dec 19 '13 at 07:37

score 1 · Answer 1 · answered Dec 19 '13 at 09:59

1

Instead of where you should use predicates which would work faster like:

ns1:Warehouse[string-length(ns1:Building)=0]

answered Dec 19 '13 at 09:59

Navin Rawat

3,208
1
19
31

Hi Navin, Thank you for your response. However, I tried using this. I am not sure if I am using it correctly. I have edited the Original Question with edit 1. If you can check and suggest changes, it would be really helpful. – systemhalted Dec 20 '13 at 20:15

Jens Erat · Answer 2 · 2013-12-20T21:40:06.443

1

Do not use local-name(...) if not necessary. Node tests will probably be faster and enable index use. You're also able to remove the string-length(...) call.

Search for <Warehouse/> elements, which do not have text nodes below their <Building/> node. If you also want to scan for arbitrary subnodes (including attributes!) use node() instead of text(). If you just want to make sure there's text somewhere possibly as child of other nodes, use ns1:Building//text() instead, for example in cases like this: <ns1:Building><foo>bar</foo></ns1:Building>.

This simple XPath expression is doing what you need:

/ns1:Warehouse[not(ns1:Building/text())]

If you need to construct those <Test/> elements, use

for $warehouse in /ns1:Warehouse[not(ns1:Building/text())]
return <Test>{$warehouse/ns1:Building}</Test>

which should be a real drop-in replacement to your XQuery.

I just realized all you want to know is the number, then better count in XQuery (I cannot tell you how to read the single result then though):

count(/ns1:Warehouse[not(ns1:Building/text())])

edited Dec 20 '13 at 21:40

answered Dec 19 '13 at 18:52

Jens Erat

37,523
16
80
96

Hi Jens, Thank you for your response. However, I tried using this. I am not sure if I am using it correctly. I have edited the Original Question with edit 1. If you can check and suggest changes, it would be really helpful. – systemhalted Dec 20 '13 at 20:15
1

I just realized you have been constructing your own result elements. Updated my answer respectively. Btw., if you receive an (error) message, always copy the _exact_ message including error numbers, ... and do not repeat it in your own words please. – Jens Erat Dec 20 '13 at 21:40
Jens, creating own result elements is not necessarily what I want as I just need a count. Result elements, if they can be avoided, will be a welcome solution. – systemhalted Jan 13 '14 at 18:08
1

Use my last query, it's not returning anything but the number. In "clever" implementations of XQuery (all relevant should be), there will not be any intermediate result. It will return a single number. – Jens Erat Jan 13 '14 at 23:12

How to optimize a XQUERY in a SELECT statement?

2 Answers2