This method performs a selective expansion of a target item with the LEVEL value set to "1" at the highest row in the hierarchy and increasing as you descend the tree, as in this selective expansion example for item "b.c.c".

PADDED_KEY       LEVEL
--------------- ------
b                    1
  b.c                2
    b.c.c            3
      b.c.c.a        4
      b.c.c.b        4
      b.c.c.c        4
 

To accomplish this, we simply place the query developed in Method 1 - UNION ALL in the FROM clause of another hierarchical query.

execute :v_target_key := 'b.c.c'

select
  lpad( '  ', (2*level-2) ) || key as padded_key ,
  level
from
  ( --------------------------------------------------
    -- this query came from "Method 1 - UNION ALL"
    --------------------------------------------------
    select -- all rows at and above the target
      key ,
      parent_key
    from
      t
    start with
      key = :v_target_key
    connect by
      key = prior parent_key
    union all
    select -- select all rows below the target
      key ,
      parent_key
    from
      t
    where
      key != :v_target_key
    start with
      key = :v_target_key
    connect by
      parent_key = prior key
  )
start with
  parent_key is null
connect by
  prior key = parent_key
order siblings by
  key
;
 

PADDED_KEY       LEVEL
--------------- ------
b                    1
  b.c                2
    b.c.c            3
      b.c.c.a        4
      b.c.c.b        4
      b.c.c.c        4