Nested Xmlagg Query Running Slow
(Doc ID 1480941.1)
Last updated on APRIL 12, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 11.1.0.7 to 11.1.0.7 [Release 11.1]Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
Query using xmlagg nested from an inner query is having poor performance
Query is:
SELECT USAGE_NAME
,USAGE_DATE
,USAGE_CNTRY
,USAGE_LANG
,USAGE_SITEID
,CONTENTTYPEID
,CONTENTID
,FACETNAME
,xmlelement("Facet", xmlattributes(FACETNAME as "name"),xmlelement("Tags", xmlagg(Tag))
) as Facet
FROM (
SELECT USAGE_NAME
,USAGE_DATE
,USAGE_CNTRY
,USAGE_LANG
,USAGE_SITEID
,CONTENTTYPEID
, CONTENTID
, FACETNAME
, TAGNAME
, xmlelement("Tag", xmlattributes(TAGNAME as "name")
, xmlelement("Synonyms",
xmlagg(
case when SYNONYMS is not null and rn1 = 1 then
xmlelement("Synonym", xmlattributes(SYNONYMS as "name"))
end
order by SYNONYMS
)
)
, xmlelement("UsageTypes",
xmlagg(
case when UsageTypename is not null and rn2 = 1 then
xmlelement("UsageType",
xmlattributes(UsageTypename as "name", decode(UsageTypeOrder,NULL,1,UsageTypeOrder) as "order"),USAGE_VALUE
)
end
order by UsageTypename,UsageTypeOrder
)
)
) as Tag
FROM (
SELECT USAGE_NAME
,USAGE_DATE
,USAGE_CNTRY
,USAGE_LANG
,USAGE_SITEID
,CONTENTTYPEID
, CONTENTID
, FACETNAME
, TAGNAME
,SYNONYMS
,UsageTypename
,UsageTypeOrder
,USAGE_VALUE
, row_number() over( partition by USAGE_NAME,USAGE_DATE,USAGE_CNTRY,USAGE_LANG,USAGE_SITEID,CONTENTTYPEID, CONTENTID, FACETNAME, TAGNAME,SYNONYMS
order by null ) as rn1
, row_number() over( partition by USAGE_NAME,USAGE_DATE,USAGE_CNTRY,USAGE_LANG,USAGE_SITEID,CONTENTTYPEID, CONTENTID, FACETNAME, TAGNAME,USAGETYPENAME,USAGETYPEORDER,USAGE_VALUE
order by null ) as rn2
FROM T_USAGE_EXPORT_XML_JB
)
GROUP BY USAGE_NAME,USAGE_DATE,USAGE_CNTRY,USAGE_LANG,USAGE_SITEID,CONTENTTYPEID, CONTENTID, FACETNAME, TAGNAME
)
GROUP BY USAGE_NAME,USAGE_DATE,USAGE_CNTRY,USAGE_LANG,USAGE_SITEID,CONTENTTYPEID, CONTENTID, FACETNAME;
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |
References |