performance issue with XMLElement & XMLAgg
I have the below query
SELECT XMLELEMENT ("ns1:listOfItems",
XMLATTRIBUTES ( 'http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi",
'http://www.w3.org/2001/XMLSchema' AS "xmlns:xsd",
'http://xmlns.oracle.com/apps/ego/item/service/out' AS "xmlns:ns1"),
XMLAGG(w.XMLCONTENT.extract('//Item') ))
FROM EGO_PUB_WS_OUTPUT w where session_id =13366
And the structure of EBO_PUB_WS_OUTPUT is as follows:
Name Null Type
------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SESSION_ID NOT NULL NUMBER
ODI_SESSION_ID NOT NULL NUMBER
WEB_SERVICE_NAME VARCHAR2(100)
SEQUENCE_ID NOT NULL NUMBER
ENTITY_TYPE VARCHAR2(100)
STATUS VARCHAR2(100)
XML_ODI XMLTYPE()
XMLCONTENT XMLTYPE()
XMLCLOB CLOB()
CREATION_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER
The select takes a couple of seconds for 10records. But more than 2 min for 50. Can anything be done to improve this.
XMLCONTENT in the table is really a big.