This discussion is archived
8 Replies Latest reply: Feb 8, 2013 12:58 PM by Ric Van Dyke RSS

Recursive WITH (Recursive Subquery Factoring) Never Returns

Ric Van Dyke Newbie
Currently Being Moderated
11.2.0.2 database on Windows, SQL Developer Version 3.2.20.09, build MAIN-09.87 (Database and SQL Developer are on the same machine. I have also tried connecting to a Linux 11.2 database and have the same results.)

I've been doing some simple testing with recursive WITH (Recursive Subquery Factoring) and when I run this following statement in SQL*Plus it returns instantly. However when running in SQL Developer it never returns, I've let it run for quite a long time (172 seconds) and gotten nothing, I finally kill the statement. Once I ran it and even killing the job didn't come back. I can get an explain plan but if I try to run it, run as script or autotrace it never returns. I have only one plan in the plan_table for this test, and it's only 4 lines long. No errors, no messages.

WITH get_plan (query_plan, id, planlevel) as
(
select ' '||operation||' '||options||' '||object_name query_plan, id, 1 planlevel
from plan_table
where id = 0
union all
select lpad(' ',2*planlevel)||p.operation||' '||p.options||' '||p.object_name query_plan, p.id, planlevel+1
from get_plan g, plan_table p
where g.id = p.parent_id
)
SELECT QUERY_PLAN FROM GET_PLAN ORDER BY PLANLEVEL;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points