My Oracle Support Banner

XMLAggrate, XmlElements under with-clause can not materialize (Doc ID 2740711.1)

Last updated on JUNE 25, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 19.0.0.0 and later
Information in this document applies to any platform.

Symptoms

On : 19.0.0.0 version, RDBMS

ACTUAL BEHAVIOR
---------------
XMLAggrate, XmlElements under with-clause can not materialize.
set autot on
with test as (select deptno
, substr(xmlagg(xmlelement(x,',',empno) order by empno).extract('//text()'),2) xmlcol
from scott.emp
group by deptno
order by deptno)
select *
from test t1
union all
select *
from test t2;

Execution Plan
--------------------------------------------------------------------------------
  0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=28 Bytes=55K)
  1 0 UNION-ALL
  2 1 VIEW (Cost=4 Card=14 Bytes=28K)
  3 2 SORT (GROUP BY) (Cost=4 Card=14 Bytes=364)
  4 3 TABLE ACCESS (FULL) OF 'SCOTT.EMP' (TABLE) (Cost=3 Card=14 Bytes=364)
  5 1 VIEW (Cost=4 Card=14 Bytes=28K)
  6 5 SORT (GROUP BY) (Cost=4 Card=14 Bytes=364)
  7 6 TABLE ACCESS (FULL) OF 'SCOTT.EMP' (TABLE) (Cost=3 Card=14 Bytes=364)


Changes

On 11g, the same sql shows temp transformation row source on execution plan.

Execution Plan
--------------------------------------------------------------------------------
  0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=28 Bytes=55K)
  1 0 TEMP TABLE TRANSFORMATION
  2 1 LOAD AS SELECT ((CURSOR DURATION MEMORY)) OF 'SYS_TEMP_0FD9D6605_2D88B0'
  3 2 SORT (GROUP BY) (Cost=4 Card=14 Bytes=364)
  4 3 TABLE ACCESS (FULL) OF 'SCOTT.EMP' (TABLE) (Cost=3 Card=14 Bytes=364)
  5 1 UNION-ALL
  6 5 VIEW (Cost=2 Card=14 Bytes=28K)
  7 6 TABLE ACCESS (FULL) OF 'SYS.SYS_TEMP_0FD9D6605_2D88B0' (TABLE (TEMP)) (Cost=2 Card=14 Bytes=364)
  8 5 VIEW (Cost=2 Card=14 Bytes=28K)
  9 8 TABLE ACCESS (FULL) OF 'SYS.SYS_TEMP_0FD9D6605_2D88B0' (TABLE (TEMP)) (Cost=2 Card=14 Bytes=364)

 

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
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.