SELECT level_num,
       parent_row_num, 
       leaf_node, 
       amount 
FROM   table t1 
WHERE  id_num = 101066 
       AND ((t1.level_num = 3) 
             OR EXISTS (SELECT NULL 
                        FROM   table t2 
                        WHERE  t2.id_num = t1.id_num 
                               AND t2.parent_row_num = t1.row_num)) 
CONNECT BY PRIOR row_num = parent_row_num 
                 AND id_num = 101066 
START WITH level_num = 1 
           AND id_num = 101066 

Execution Plan
----------------------------------------------------------
Plan hash value: 475934742

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |     2 |    42 |     2   (0)| 00:00:01 |
|*  1 |  FILTER                        |                |       |       |            |          |
|*  2 |   CONNECT BY WITH FILTERING    |                |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID | TABLE 		|       |       |            |          |
|*  4 |     INDEX RANGE SCAN           | IDT_TABLE      |     3 |    24 |     1   (0)| 00:00:01 |
|   5 |    NESTED LOOPS                |                |       |       |            |          |
|   6 |     BUFFER SORT                |                |       |       |            |          |
|   7 |      CONNECT BY PUMP           |                |       |       |            |          |
|   8 |     TABLE ACCESS BY INDEX ROWID| TABLE 		|     2 |    42 |     2   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | IDT_TABLE      |     2 |       |     1   (0)| 00:00:01 |
|* 10 |    TABLE ACCESS FULL           | TABLE 		|     2 |    42 |     2   (0)| 00:00:01 |
|* 11 |   INDEX RANGE SCAN             | IDT_TABLE      |     1 |     8 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID_NUM"=101066 AND ("T1"."LEVEL_NUM"=3 OR  EXISTS (SELECT 0 FROM
              "TABLE" "T2" WHERE "T2"."PARENT_ROW_NUM"=:B1 AND "T2"."ID_NUM"=:B2)))
   2 - filter("LEVEL_NUM"=1 AND "ID_NUM"=101066)
   4 - access("ID_NUM"=101066 AND "LEVEL_NUM"=1)
       filter("LEVEL_NUM"=1)
   9 - access("ID_NUM"=101066 AND "PARENT_ROW_NUM"=NULL)
  10 - access("ID_NUM"=101066)
  11 - access("T2"."ID_NUM"=:B1 AND "T2"."PARENT_ROW_NUM"=:B2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
        820  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
         14  rows processed

SELECT level_num,
       parent_row_num, 
       leaf_node, 
       amount 
FROM   table t1 
WHERE (id_num, row_num) IN (
    SELECT id_num, row_num
    FROM   table
    WHERE  level_num = 3
    AND    id_num = 101066
    UNION ALL
    SELECT id_num, parent_row_num
    FROM   table
    WHERE  id_num = 101066
)