1 Reply Latest reply on Apr 7, 2020 7:59 AM by Glen Conway

    on using connect by prior program not terminating - millions of child nodes

    4118203

      CONNECTBY NOCYCLE - produces 30490986 children nodes for the below scenario.I want to process the child nodes and the program  using this query doesnot terminate.

      Create table temp_path(

          from_node varchar(2),

           to_node varchar(2));

       

          INSERT INTO temp_path values('A','B');

          INSERT INTO temp_path values('A','C');

          INSERT INTO temp_path values('A','D');

          INSERT INTO temp_path values('A','E');

          INSERT INTO temp_path values('A','F');

          INSERT INTO temp_path values('A','G');

          INSERT INTO temp_path values('A','H');

          INSERT INTO temp_path values('A','I');

          INSERT INTO temp_path values('A','J');

       

       

          INSERT INTO temp_path values('B','K');

          INSERT INTO temp_path values('B','L');

          INSERT INTO temp_path values('B','C');

          INSERT INTO temp_path values('B','E');

          INSERT INTO temp_path values('B','M');

          INSERT INTO temp_path values('B','N');

          INSERT INTO temp_path values('B','O');

          INSERT INTO temp_path values('B','J');

       

          INSERT INTO temp_path values('C','K');

          INSERT INTO temp_path values('C','N');

          INSERT INTO temp_path values('C','J');

       

       

       

          INSERT INTO temp_path values('D','K');

          INSERT INTO temp_path values('D','B');

          INSERT INTO temp_path values('D','L');

          INSERT INTO temp_path values('D','C');

          INSERT INTO temp_path values('D','E');

          INSERT INTO temp_path values('D','F');

          INSERT INTO temp_path values('D','G');

          INSERT INTO temp_path values('D','M');

          INSERT INTO temp_path values('D','P');

          INSERT INTO temp_path values('D','Q');

          INSERT INTO temp_path values('D','N');

          INSERT INTO temp_path values('D','R');

          INSERT INTO temp_path values('D','O');

          INSERT INTO temp_path values('D','J');

       

          INSERT INTO temp_path values('E','K');

          INSERT INTO temp_path values('E','L');

          INSERT INTO temp_path values('E','S');

          INSERT INTO temp_path values('E','M');

          INSERT INTO temp_path values('E','N');

          INSERT INTO temp_path values('E','O');

          INSERT INTO temp_path values('E','J');

       

          INSERT INTO temp_path values('F','K');

          INSERT INTO temp_path values('F','B');

          INSERT INTO temp_path values('F','L');

          INSERT INTO temp_path values('F','C');

          INSERT INTO temp_path values('F','G');

          INSERT INTO temp_path values('F','S');

          INSERT INTO temp_path values('F','M');

          INSERT INTO temp_path values('F','N');

          INSERT INTO temp_path values('E','O');

          INSERT INTO temp_path values('E','J');

       

          INSERT INTO temp_path values('G','K');

          INSERT INTO temp_path values('G','B');

          INSERT INTO temp_path values('G','L');

          INSERT INTO temp_path values('G','E');

          INSERT INTO temp_path values('G','M');

          INSERT INTO temp_path values('G','N');

          INSERT INTO temp_path values('G','O');

          INSERT INTO temp_path values('G','J');

       

          INSERT INTO temp_path values('H','K');

          INSERT INTO temp_path values('H','B');

          INSERT INTO temp_path values('H','L');

          INSERT INTO temp_path values('H','C');

          INSERT INTO temp_path values('H','D');

          INSERT INTO temp_path values('H','E');

          INSERT INTO temp_path values('H','F');

          INSERT INTO temp_path values('H','G');

          INSERT INTO temp_path values('H','M');

          INSERT INTO temp_path values('H','P');

          INSERT INTO temp_path values('H','Q');

          INSERT INTO temp_path values('H','R');

          INSERT INTO temp_path values('H','O');

          INSERT INTO temp_path values('H','J');

          INSERT INTO temp_path values('I','K');

          INSERT INTO temp_path values('I','B');

          INSERT INTO temp_path values('I','L');

          INSERT INTO temp_path values('I','C');

          INSERT INTO temp_path values('I','D');

          INSERT INTO temp_path values('I','E');

          INSERT INTO temp_path values('I','F');

          INSERT INTO temp_path values('I','G');

          INSERT INTO temp_path values('I','H');

          INSERT INTO temp_path values('I','M');

          INSERT INTO temp_path values('I','P');

          INSERT INTO temp_path values('I','Q');

          INSERT INTO temp_path values('I','N');

          INSERT INTO temp_path values('I','R');

          INSERT INTO temp_path values('I','O');

          INSERT INTO temp_path values('I','J');

       

       

          INSERT INTO temp_path values('J','K');

          INSERT INTO temp_path values('J','L');

          INSERT INTO temp_path values('J','N');

          INSERT INTO temp_path values('J','O');

       

          INSERT INTO temp_path values('K','N');

          INSERT INTO temp_path values('K','J');

       

                  INSERT INTO temp_path values('L','K');

          INSERT INTO temp_path values('L','N');

          INSERT INTO temp_path values('L','J');

       

          INSERT INTO temp_path values('M','K');

          INSERT INTO temp_path values('M','B');

          INSERT INTO temp_path values('M','L');

          INSERT INTO temp_path values('M','C');

          INSERT INTO temp_path values('M','E');

          INSERT INTO temp_path values('M','F');

          INSERT INTO temp_path values('M','G');

          INSERT INTO temp_path values('M','N');

          INSERT INTO temp_path values('M','O');

          INSERT INTO temp_path values('M','J');

          INSERT INTO temp_path values('M','F');

       

          INSERT INTO temp_path values('N','J');

       

          INSERT INTO temp_path values('O','K');

          INSERT INTO temp_path values('O','L');

          INSERT INTO temp_path values('O','N');

          INSERT INTO temp_path values('O','J');

       

          INSERT INTO temp_path values('P','K');

          INSERT INTO temp_path values('P','A');

          INSERT INTO temp_path values('P','B');

          INSERT INTO temp_path values('P','L');

          INSERT INTO temp_path values('P','T');

          INSERT INTO temp_path values('P','C');

          INSERT INTO temp_path values('P','D');

          INSERT INTO temp_path values('P','E');

          INSERT INTO temp_path values('P','F');

          INSERT INTO temp_path values('P','G');

          INSERT INTO temp_path values('P','H');

          INSERT INTO temp_path values('P','I');

          INSERT INTO temp_path values('P','N');

          INSERT INTO temp_path values('P','R');

          INSERT INTO temp_path values('P','O');

          INSERT INTO temp_path values('P','J');

       

          INSERT INTO temp_path values('Q','H');

          INSERT INTO temp_path values('Q','I');

          INSERT INTO temp_path values('Q','D');

          INSERT INTO temp_path values('Q','E');

          INSERT INTO temp_path values('Q','F');

          INSERT INTO temp_path values('Q','G');

          INSERT INTO temp_path values('Q','N');

          INSERT INTO temp_path values('Q','L');

          INSERT INTO temp_path values('Q','K');

          INSERT INTO temp_path values('Q','R');

          INSERT INTO temp_path values('Q','T');

          INSERT INTO temp_path values('Q','A');

          INSERT INTO temp_path values('Q','C');

          INSERT INTO temp_path values('Q','B');

          INSERT INTO temp_path values('Q','O');

          INSERT INTO temp_path values('Q','J');

       

       

          INSERT INTO temp_path values('R','K');

          INSERT INTO temp_path values('R','B');

          INSERT INTO temp_path values('R','L');

          INSERT INTO temp_path values('R','C');

          INSERT INTO temp_path values('R','G');

          INSERT INTO temp_path values('R','M');

          INSERT INTO temp_path values('R','N');

          INSERT INTO temp_path values('R','O');

          INSERT INTO temp_path values('R','J');

       

          INSERT INTO temp_path values('S','K');

          INSERT INTO temp_path values('S','B');

          INSERT INTO temp_path values('S','L');

          INSERT INTO temp_path values('S','C');

          INSERT INTO temp_path values('S','E');

          INSERT INTO temp_path values('S','F');

          INSERT INTO temp_path values('S','G');

          INSERT INTO temp_path values('S','N');

          INSERT INTO temp_path values('S','O');

          INSERT INTO temp_path values('S','J');

       

          INSERT INTO temp_path values('T','K');

          INSERT INTO temp_path values('T','A');

          INSERT INTO temp_path values('T','B');

          INSERT INTO temp_path values('T','L');

          INSERT INTO temp_path values('T','C');

          INSERT INTO temp_path values('T','D');

          INSERT INTO temp_path values('T','E');

          INSERT INTO temp_path values('T','F');

          INSERT INTO temp_path values('T','G');

          INSERT INTO temp_path values('T','H');

          INSERT INTO temp_path values('T','I');

          INSERT INTO temp_path values('T','M');

          INSERT INTO temp_path values('T','P');

          INSERT INTO temp_path values('T','Q');

          INSERT INTO temp_path values('T','R');

          INSERT INTO temp_path values('T','O');

          INSERT INTO temp_path values('T','J');

       

      Number of nodes:

                          SELECT COUNT(*) FROM(    SELECT

                          from_node,

                          to_node,

       

                          level

                         FROM

                          temp_path

                       START WITH

                          from_node = 'A'

                      CONNECT BY NOCYCLE

                         PRIOR to_node = from_node);

       

      nodes:

                   SELECT

                          from_node,

                          to_node,

       

                          level

                         FROM

                          temp_path

                       START WITH

                          from_node = 'A'

                      CONNECT BY NOCYCLE

                         PRIOR to_node = from_node;

       

      Can someone please help