WITH CTE (NAME, ID, PARENT_ID, POINT_ID, POINT_NAME) AS (
    SELECT T.NAME, T.ID, T.PARENT_ID, T.ID, T.NAME
    FROM TEST T
    WHERE KIND IN (333, 111, 222)
    UNION ALL
    SELECT T1.NAME, T1.ID, T1.PARENT_ID, CTE.POINT_ID, CTE.POINT_NAME
    FROM TEST T1
             INNER JOIN CTE ON T1.PARENT_ID = CTE.ID
    )
SELECT * FROM CTE;

|-----------------------------------------------------------------------------------------------------------------|
| Id| Operation                                 | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
|-----------------------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT                          |                 |    88G|   164T|       |   239M  (1)| 02:36:15 |
| 1 |  VIEW                                     |                 |    88G|   164T|       |   239M  (1)| 02:36:15 |
| 2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|                 |       |       |       |            |          |
| 3 |    INLIST ITERATOR                        |                 |       |       |       |            |          |
| 4 |     TABLE ACCESS BY INDEX ROWID BATCHED   | TEST            |    93 | 96813 |       |     6   (0)| 00:00:01 |
|*5 |      INDEX RANGE SCAN                     | TEST_KIND_INDEX |    27 |       |       |     3   (0)| 00:00:01 |
|*6 |    HASH JOIN                              |                 |    88G|   165T|  6816K|   239M  (1)| 02:36:15 |
| 7 |     BUFFER SORT (REUSE)                   |                 |       |       |       |            |          |
| 8 |      TABLE ACCESS FULL                    | TEST            |  6708 |  6734K|       |    68   (0)| 00:00:01 |
| 9 |     RECURSIVE WITH PUMP                   |                 |       |       |       |            |          |
|-----------------------------------------------------------------------------------------------------------------|