Hi,

better is walk throught tree

------------------------------------------------------------------

WITH RECURSIVE
R_TREE AS
(
SELECT TT.ID, TT.ID AS TOP_PARENT
FROM TABLE1 TT
WHERE TT.PARENT_ID IS NULL

UNION ALL

SELECT TT.ID, RT.TOP_PARENT
FROM TABLE1 TT JOIN R_TREE RT ON RT.ID = TT.PARENT_ID
)
SELECT
RT2.ID, CASE WHEN RT2.ID=RT2.TOP_PARENT THEN NULL ELSE RT2.TOP_PARENT END AS
TOP_PARENT
FROM
R_TREE RT2

------------------------------------------------------------------
the result plan show obvious difference

above query
PLAN (R_TREE TT NATURAL, R_TREE TT NATURAL)
instead
PLAN (R NATURAL, HASH (TREE ROOT NATURAL, TREE T NATURAL), TREE M NATURAL)

------------------------------------------------------------------
and with indexes
------------------------------------------------------------------

above query
PLAN (R_TREE TT INDEX (IXA_TABLE1__PARENT_ID), R_TREE TT INDEX
(IXA_TABLE1__PARENT_ID))

instead previus
PLAN (R INDEX (IXA_TABLE1__PARENT_ID), JOIN (TREE ROOT INDEX
(IXA_TABLE1__PARENT_ID), TREE T INDEX (IXA_TABLE1__PARENT_ID)), TREE M INDEX
(IXA_TABLE1__PARENT_ID))

Regards,
Karol Bieniaszewski