In Oracle SQL 19, I have a table nodes that, together with parents, you can construct a tree (without an unique root). The parent of a node is in node.parent, or in parents.parent if node.parent is null. If both parent and parents.parent is null then the node is a root.

create table parents as (
    child int not null,
    parent int not null
);

create table nodes (
    id int not null,
    parent int, -- foreign key to `info.id`
    type int not null,
    prop1 int,
    prop2 int,
    prop3 varchar(255)
);

The set of nodes are organized like a hierarchical pyramid, where ~95% of nodes are lvl0, ~5% of nodes lvl1, ~0.5% lvl2, ~0.08% lvl3 and ~0.04% lvl4. The level at which a node belongs to is determined by its type.

The parent of a node from a level is always a node from “some” superior level, but not neccesarily from exactly the next level; and reaching lvl4 isn’t mandatory. For example, the parent of a lvl1 node could be a lvl3 node that has no parent. In short: if a node has a parent, then it’s parent lives in some superior level, and that’s the only guarantee.

Now, I want to create a view like this:

select
     N.id      id,  N.type      type, /* and the 3 props */, -- element
    L1.id lvl1_id, L1.type lvl1_type, /* and the 3 props */  -- parent from lvl1
    L2.id lvl2_id, L2.type lvl2_type, /* and the 3 props */  -- parent from lvl2
    L3.id lvl3_id, L3.type lvl3_type, /* and the 3 props */  -- parent from lvl3
    L4.id lvl4_id, L4.type lvl4_type, /* and the 3 props */  -- parent from lvl4
from ...;

where in the first “row of columns” I place the properties of the node I’m asking for, and then locate all their parents and place their columns in the positions according to the levels they belong to. The level of the node I’m asking for itself is not relevant, because their properties always go on the first “row of columns”. Only the levels of the parents of a node matters to know where to place their properties.

The way I solve it so far is:

create or replace view vparents as 
select N.id id, N.rowid rid, N.type, N.prop1, N.prop2, N.prop3,
    nvl(N.parent, N.parent) parent,
    decode(N.type, 20, 1, 16, 2, 18, 2, 100, 3, 101, 4, 200, 4, 0) lvl,
from info N
    left join parents P on P.child = N.id /* NOTE 1 */
;

create or replace view hierarchy as /* That's what I'm trying to simplify */
select
     N.id      id,  N.type      type, /* and the 3 props */, -- element
    L1.id lvl1_id, L1.type lvl1_type, /* and the 3 props */  -- parent from lvl1
    L2.id lvl2_id, L2.type lvl2_type, /* and the 3 props */  -- parent from lvl2
    L3.id lvl3_id, L3.type lvl3_type, /* and the 3 props */  -- parent from lvl3
    L4.id lvl4_id, L4.type lvl4_type, /* and the 3 props */  -- parent from lvl4
from vparents N
    left join vparents P1 on P1.id = N.parent
    left join vparents P2 on P2.id = P1.parent
    left join vparents P3 on P3.id = P2.parent
    left join vparents P4 on P4.id = P3.parent
    left join vparents L1 on L1.rid = decode(1, P1.lvl, P1.rid)
    left join vparents L2 on L2.rid = decode(2, P1.lvl, P1.rid, P2.lvl, P2.rid)
    left join vparents L3 on L3.rid = decode(3, P1.lvl, P1.rid, P2.lvl, P2.rid, P3.lvl, P3.rid)
    left join vparents L4 on L4.rid = decode(4, P1.lvl, P1.rid, P2.lvl, P2.rid, P3.lvl, P3.rid, P4.lvl, P4.rid)
;

Basically, I first “walk the parent path”, and then see in which LY does each PX belong to according to its level, which I previously calculated in the vparent view. For example, if node N has 2 parents, P1 from L2 and P2 from L3, then P3, P4, L1 and L4 will be null. That’s why the decode function grows in arguments: if P1 is not null but didn’t belong to L1, then it must belong to L2, L3 or L4. I can’t lose the track of the potential parents that hasn’t found their levels yet, and hence the ever growing decode argument list. Glad our table is only 5 levels deep and not 50.

Three questions:

• Is there any way to simplify this?!!!! Could using the connect by clause help somehow?
• Since I’m artificially doubling the number of joins, I don’t want Oracle to artificially double the number of lookups. That’s why I added the rowid in the vparent view and use it in the main view, as a way to tell Oracle: hey, all the rows from L1 or L4 are rows that you already has located in the very same query. After all, the actual lookup is locating the parents. The L1 to L4 tables are just an artefact to reorder the rows that I have already found, because what I actually want is to reorder the columns so they fall into their right places. I have no idea how Oracle execute queries in practice.
• Regarding NOTE 1 in the vparents view, ~70% of elements have node.parents set, which means 70% of nodes doesn’t need to join with the parents table because their contents will be completely ignored (in a majority of cases where node.parents exists, parents.parent will contain its grandparent instead, and so the join will succeed to be completely ignored afterwards). Is there a way to avoid that? For example, if I do left join parents P on P.child = nvl2(N.parent, null, N.id), P.child = null would be the join coindition 70% of the time. Could that help Oracle understand that searching within parents is guaranteed to fail so it doesn’t even try?