Nested Xmlagg Query Running Slow

Last updated on FEBRUARY 08, 2017

Applies to:

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