8 Replies Latest reply: Feb 8, 2013 2:58 PM by Ric Van Dyke RSS

    Recursive WITH (Recursive Subquery Factoring) Never Returns

    Ric Van Dyke
      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;