XmlAgg Performance Issue : How to Concatenate Clob?
1003374May 13 2013 — edited May 13 2013Hi there,
I have a query which does the following :
select XmlElement("PersonList",
XmlAgg(
XmlElement("Person", XmlAttributes(pd.PRSN_ID, pd.LAST_NAME, pd.FIRS_NAME, pd.TITL_COD, pd.PRAL_EMAI),
XmlAgg(XmlElement("Employee", XmlAttributes(ed."technical_ID")))
)
)
)
from view_person pd left outer join view_employee ed on ed.prsn_id = pd.prsn_id
group by pd.PRSN_ID, pd.LAST_NAME, pd.FIRS_NAME, pd.TITL_COD, pd.PRAL_EMAI
Both Tables contain around 40 000 lines.
The Issue :
The performance of the XmlAgg is very poor when it comes to 40 000 lines. It seems the performance is exponentially poor. That is for few thousand lines the query excutes within 3 minutes, but if it goes to 40 000 lines, the query just goes into timeout. I had even the error ORA-04030: out of process memory
I noticed that if I replace the XmlAgg(XmlElement("Employee", XmlAttributes(ed."technical_ID"))) and with XmlElement("Employee", XmlAttributes(ed."technical_ID")), the performance is fine,but then the result returned contains a table of (many lines) of XML data (Clob according to Oracle).
My question is : Is there a way to concatenate all this in a more performant way in order to retrieve a single xml
We are using the following Oracle Version :
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
Edited by: BizTalk Guy on 13 mai 2013 08:09
Edited by: BizTalk Guy on 13 mai 2013 08:09