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
