Connect by level vs recursive with (CTE) for generating values

Hi All,

Have you ever wondered what is the difference between generating records using connect by level and recursive with in CTE (available from Oracle 11R2)? I did short ora-investigation 🙂

CONNECT BY LEVEL – often used SQL method for generating values

I have executed:

SQL> select level from dual connect by level>0;

It produced an error:

ERROR:
ORA-30009: Not enough memory for CONNECT BY operation

Action suggested by an Oracle for this error:

“Action: In WORKAREA_SIZE_POLICY=AUTO mode, set PGA_AGGREGATE_TARGET to a reasonably larger value. Or, in WORKAREA_SIZE_POLICY=MANUAL mode, set SORT_AREA_SIZE to a reasonably larger value.”

(source: http://docs.oracle.com/cd/E11882_01/server.112/e17766/e29250.htm)

To it looks like Oracle keep it in PGA memory 🙂

And some part of statistics for this method:

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
   34651841  bytes sent via SQL*Net to client
    1349255  bytes received via SQL*Net from client
     122613  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    1839180  rows processed

Generating using CTE (Common Table Expression) – on the same environment.

SQL> with z(lvl) as (
  2  select 1 lvl from dual
  3  union all
  4  select lvl+1  from z
  5  where lvl>=1)
  6  select * from z;
ERROR:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP6


5094180 rows selected.

Statistics
----------------------------------------------------------
        397  recursive calls
   50712651  db block gets
          0  consistent gets
        790  physical reads
          0  redo size
   97765959  bytes sent via SQL*Net to client
    3736255  bytes received via SQL*Net from client
     339613  SQL*Net roundtrips to/from client
    5094195  sorts (memory)
          0  sorts (disk)
    5094180  rows processed

SQL>

As you can see there is a big difference between generating values using CTE and Connect by level – such as physical reads, db block gets. During this test size of temporary tablespace was increasing. No space in temporary tablespace caused an error

Summary:

It looks like that:

• Connect by level rows generating consumes memory
• Generating values by CTE consume temporary tablespace

Have a nice day!

RS