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

Last updated on APRIL 17, 2023

Applies to:

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

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