Oracle SQL hierarchical query from bottom to top

I have a table where I want to go from bottom to top using hierarchical queries.

The problem is that I need the get the value of one column from root (top) using CONNECT_BY_ROOT, but since I reverse the way the hierarchical query works (reverse the prior in connect by and the start with), this function (CONNECT_BY_ROOT) consider my ‘start with’ row as level 1 (root) then gets me this value.

In other words, I want a way to reverse the CONNECT_BY_ROOT to get me the value of a column from the last possible level and not the root.

+----+-----------+-------+
| ID | ID_PARENT | VALUE |
+----+-----------+-------+
|  1 |      null |     5 |
|  2 |         1 |     9 |
|  3 |         2 |  null |
+----+-----------+-------+

I want to get the value of ID = 1 (5) to the ID = 3 like this:

+----+-------+------------+
| ID | VALUE | VALUE_root |
+----+-------+------------+
|  1 |  5    |      5     |
|  2 |  9    |      5     | 
|  3 |  null |      5     |
+----+-------+------------+

I tried this but all I get is null as value_root:

SELECT id,
CONNECT_BY_ROOT VALUE as VALUE_root
FROM my_table
START WITH ID = 3
CONNECT BY ID = PRIOR ID_PARENT

EDIT: I forgot to mention that in my real system I’m dealing with millions of rows of data, the reason why I’m reversing the hierachical queries in first place is to make it better in terms of performance!

Answer

You may retrieve the root (which is a bottom node in your case) for all the tree upwards and then apply analytical function partitioned by the root to translate parent value to all the tree nodes. This is also possible for multiple nodes in start with.

with src (id, parentid, val) as (
  select 1, cast(null as int), 5 from dual union all
  select 2, 1, 9 from dual union all
  select 3, 2, null from dual union all
  select 4, 2, null from dual union all
  select 5, null, 10 from dual union all
  select 6, 5, 7 from dual
  
)
select
  connect_by_root id as tree_id
  , id
  , parentid
  , val
  , max(decode(connect_by_isleaf, 1, val))
      over(partition by connect_by_root id) as val_root
from src
start with id in (3, 4, 6)
connect by id = prior parentid

order by 1, 2, 3

TREE_ID	ID	PARENTID	VAL	VAL_ROOT
3	1	–	5	5
3	2	1	9	5
3	3	2	–	5
4	1	–	5	5
4	2	1	9	5
4	4	2	–	5
6	5	–	10	10
6	6	5	7	10