Forum Stats

  • 3,758,142 Users
  • 2,251,342 Discussions
  • 7,870,062 Comments

Discussions

Filtered hierarchical data - previous available ancestor?

2»

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,726 Black Diamond

    Actually, it can be optimized even more:

    with qryData as (

                     select 1 as ID, 'Bert' as NAME, to_number(null) as PARENT_ID from dual union

                     select 2 as ID, 'Mark' ,   1 from dual union

                     select 3 as ID, 'Brenda' , 1 from dual union

                     select 4 as ID, 'Mike' ,   3 from dual union

                     select 5 as ID, 'Steve' ,  4 from dual union

                     select 6 as ID, 'John' ,   2 from dual union

                     select 7 as ID, 'Jo' ,     6 from dual union

                     select 8 as ID, 'Jim' ,    2 from dual union

                     select 9 as ID, 'Jane' ,   7 from dual

                    ),

       ancestors as (

                     select  level lvl,

                             connect_by_root id id,

                             connect_by_root name name,

                             connect_by_root parent_id parent_id,

                             parent_id ancestor_id

                       from  qryData

                       start with id in (1,3,5,6,8,9)

                       connect by id = prior parent_id

                              and id not in (1,3,5,6,8,9)

                    )

    select  id,

            name,

            parent_id,

            min(ancestor_id) keep(dense_rank first order by case when parent_id in (1,3,5,6,8,9) then lvl end nulls last) avail_ancestor_id

      from  ancestors

      group by id,

               name,

               parent_id

      order by id,

               avail_ancestor_id

    /


    ID NAME    PARENT_ID  AVAIL_ANCESTOR_ID
    --- ------ ---------- ------------------
      1 Bert
      3 Brenda          1                  1
      5 Steve           4                  3
      6 John            2                  1
      8 Jim             2                  1
      9 Jane            7                  6

    6 rows selected.

    SQL>

    SY.

  • paul zip
    paul zip Member Posts: 26

    I was very impressed with this approach.  Yes, it can see may be slow with large datasets, but with smaller sets it'll be fine.  What impressed me is it was extremely clever in the way it only uses the filtered dataset to yield the answer,  Quite genius.

  • paul zip
    paul zip Member Posts: 26

    I can see what you are doing, but my list could contain 100s / 1000s of items, how would this transpose to your "in (1,3,5,6,8,9)" situations, without multiple table scans?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,726 Black Diamond
    paulzip wrote:
    
    I can see what you are doing, but my list could contain 100s / 1000s of items, how would this transpose to your "in (1,3,5,6,8,9)" situations, without multiple table scans?
     

    So what prevents you from using nested table/varray or a driver table:

    SQL> select  *
      2    from  qryData
      3  /

    ID NAME    PARENT_ID
    --- ------ ----------
      1 Bert
      2 Mark            1
      3 Brenda          1
      4 Mike            3
      5 Steve           4
      6 John            2
      7 Jo              6
      8 Jim             2
      9 Jane            7

    9 rows selected.

    SQL> explain plan for
      2  with  ancestors as (
      3                   select  level lvl,
      4                           connect_by_root id id,
      5                           connect_by_root name name,
      6                           connect_by_root parent_id parent_id,
      7                           parent_id ancestor_id
      8                     from  qryData
      9                     start with id in (select * from table(sys.OdciNumberList(1,3,5,6,8,9)))
    10                     connect by id = prior parent_id
    11                            and id not in (select * from table(sys.OdciNumberList(1,3,5,6,8,9)))
    12                  )
    13  select  id,
    14          name,
    15          parent_id,
    16          min(ancestor_id) keep(dense_rank first order by case when parent_id in (select * from table(sys.OdciNumberList(1,3,5,6,8,9))) then lvl end nulls last) avail_ancestor_id
    17    from  ancestors
    18    group by id,
    19             name,
    20             parent_id
    21    order by id,
    22             avail_ancestor_id
    23  /

    Explained.

    SQL> select  *
      2    from  table(dbms_xplan.display)
      3  /

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    Plan hash value: 3643621826

    ------------------------------------------------------------------------------------------------------
    | Id  | Operation                                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                           |         |     9 |   513 |     5  (40)| 00:00:01 |
    |*  1 |  COLLECTION ITERATOR CONSTRUCTOR FETCH     |         |     1 |     2 |     2   (0)| 00:00:01 |
    |   2 |  SORT ORDER BY                             |         |     9 |   513 |     5  (40)| 00:00:01 |
    |   3 |   SORT GROUP BY                            |         |     9 |   513 |     5  (40)| 00:00:01 |
    |   4 |    VIEW                                    |         |     9 |   513 |    22  (87)| 00:00:01 |
    |*  5 |     CONNECT BY NO FILTERING WITH START-WITH|         |       |       |            |          |

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |   6 |      TABLE ACCESS FULL                     | QRYDATA |     9 |   279 |     3   (0)| 00:00:01 |
    |*  7 |      COLLECTION ITERATOR CONSTRUCTOR FETCH |         |     2 |     4 |     2   (0)| 00:00:01 |
    |*  8 |      COLLECTION ITERATOR CONSTRUCTOR FETCH |         |     1 |     2 |     2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       1 - filter(VALUE(KOKBF$)=:B1)
       5 - access("ID"=PRIOR "PARENT_ID")
           filter( NOT EXISTS (SELECT 0 FROM TABLE() "KOKBF$3" WHERE LNNVL(VALUE(KOKBF$)<>:B1))

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
                  AND  EXISTS (SELECT 0 FROM TABLE() "KOKBF$0" WHERE VALUE(KOKBF$)=:B2))
       7 - filter(LNNVL(VALUE(KOKBF$)<>:B1))
       8 - filter(VALUE(KOKBF$)=:B1)

    Note
    -----
       - dynamic sampling used for this statement (level=2)

    29 rows selected.

    SQL>

    SY.

    Solomon Yakobson
This discussion has been closed.