Search notes:
Oracle SQL Plan operation RECURSIVE WITH PUMP
explain plan for
with rec(n) as (
select 1 from dual
union all
select n+1 from rec where n<=1000
)
select * from rec;
select * from table(dbms_xplan.display(format=>'basic +predicate'));
-- ----------------------------------------------------------
-- | Id | Operation | Name |
-- ----------------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | VIEW | |
-- | 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| |
-- | 3 | FAST DUAL | |
-- |* 4 | RECURSIVE WITH PUMP | |
-- ----------------------------------------------------------
--
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--
-- 4 - filter("N"<=1000)