I already see one simplification:

>
> SELECT <Version fields>
> FROM Version V
> WHERE V.oid IN (
> SELECT E.currentVersionID
> FROM Element E
> JOIN Node N ON E.nodeId = N.oid
> JOIN Link L ON N.oid = L.childId
> JOIN Path P ON L.parentId = P.decsendantId
> WHERE P.ancestorId = 12345 );
>

Can be rewritten as:

SELECT <Version fields>
FROM Version V
WHERE V.oid IN (
SELECT E.currentVersionID
FROM Element E
JOIN Link L ON E.nodeId = L.childId
JOIN Path P ON L.parentId = P.decsendantId
WHERE P.ancestorId = 12345 );

However, while it is simpler, I'm not sure it would perform better
E.nodeId = L.childId will have to more variations as L.childId will
include groups if a node is deeply or multiply nested whereas E.nodeId
= N.oid will only try for actual nodes.

Thoughts?

R.