There is a serious performance issue with my query using XMLAGG
CREATE TABLE tmp_test_xml
(
acc_ID NUMBER(12),
CUS_DTLS CLOB
)
INSERT INTO tmp_test_xml
SELECT tab.acc_id acc_id
, XMLSERIALIZE(DOCUMENT XMLELEMENT ("AccountHolders"
,XMLAGG (XMLELEMENT ("AccountHolder"
,XMLELEMENT ("Gender" , tab.sex_cde )
,XMLELEMENT ("Name" , tab.name )
,XMLFOREST (tab.drivers_licence AS "DL" )
,XMLFOREST (tab.empr_name AS "emp_name" )
,XMLELEMENT ("Address" , tab.addr )
..
...
...
) )) ) AS cus_dtls
FROM TABLE tab
group by tab.acc_id
table 'TABLE' has 3 Million records
The performance of the Insert degrades as follows :
INSERT
10K recs - 1 sec
30K recs - 45 secs
50K recs - 3 mins
100K recs - 16 mins
Please let me know if I can improve the performance somehow. I can imagine how i can insert 3 million records in there ..
There is no table space issues. Tried a million recs without XMLAGG- 2 mins.
Is there any other way to aggregate my xml data . Actually I am trying to aggregate data for all customers for one account .
Version info :
------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Cheers !!
Saxena