Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    bangalore
    Posts
    14

    Unanswered: Limitation in using Connect By Prior

    Hi,

    Is there a limitation in using Connect By Prior statement in Oracle? I mean is there a limitation on how many levels I can go when I am defining the Hierarchy or does oracle suggests the maximum levels without compromising on performance?


    Murali.
    Murali Mohan Rao. M

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: Limitation in using Connect By Prior

    Originally posted by manduva
    Hi,

    Is there a limitation in using Connect By Prior statement in Oracle? I mean is there a limitation on how many levels I can go when I am defining the Hierarchy or does oracle suggests the maximum levels without compromising on performance?


    Murali.

    As far as I know you can go down or up as many levels as you would like..just remember that the CONNECT BY / START WITH statement does not support joins.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Limitation in using Connect By Prior

    No limitation really. For example, I just created a simple hierarchy with 44300 levels like this:
    Code:
    create table t1( id int, parent_id int );
    
    insert /*+ append */ into t1
    select rownum, rownum-1
    from all_objects;
    
    commit;
    Then I ran this:
    Code:
    select id, level
    from t1
    start with parent_id = 0
    connect by prior id = parent_id;
    And got:
    Code:
            ID      LEVEL
    ---------- ----------
             1          1
             2          2
             3          3
    ...
         44297      44297
         44298      44298
         44299      44299
         44300      44300
    
    44300 rows selected.
    It took about 10 minutes, compared to 77 seconds for "select * from t1".

    Here's another experiment:
    Code:
    truncate table t1;
    
    insert /*+ append */ into t1
    select rownum, trunc(rownum/10)
    from all_objects
    where rownum <= 5000;
    
    commit;
    That created a hierarchy where 0 is the parent of 1-9, 1 is the parent of 10-19, 10 is the parent of 100-109, etc.
    Code:
    set timing on 
    
    select id, parent_id
    from t1
    start with parent_id = 0
    connect by prior id = parent_id;
    It took 56 seconds to produce a result (no indexes), or 19 seconds with an index on (parent_id).

    But that only had 4 levels, so I did it again like this:
    Code:
    truncate table t1;
    
    insert /*+ append */ into t1
    select rownum, trunc(rownum/2)
    from all_objects
    where rownum <= 5000;
    
    commit;
    That has 13 levels. With the same index on parent_id, the query above took 16 seconds.

    For comparison, a simple "select * from t1" took 12 seconds.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •