Oracle 11g hierarchical queries with limit, offset and order by to achieve pagination on the root level

Question

I'm using Oracle 11g (unfortunately not 12c, i.e. OFFSET n ROWS FETCH NEXT m ROWS ONLY is not available) and I'm trying to achieve pagination on a hierarchical query.

The aim is to sort the root level entries and retrieve for a given number of root entries all connected children. The pagination (offset and limit) should be performed by the root entries.

Is there any way to achieve this?

I managed to limit the number of root entries but I cannot achieve the sorting on the root level (START WHITH) or specify the offset only for the root level.

SELECT *
FROM my_table
START WITH (
    column_a = 'some value'
    AND column_b = 'some other value' 
)
AND ROWNUM <= 10 -- limit rows on top level, however the sorting is arbitrary
                 -- and offset is not possible
CONNECT BY
    PRIOR childid = parentid
    AND column_b = 'whatever'
ORDER BY sort_column  -- applies to overall result, alternatively you could 
                      -- use ORDER SIBLINGS BY to sort the entries by their level

Of course it is possible to to some pagination on the overall result by using a subselect like this:

SELECT * FROM (
    SELECT *, rownum as row_num
    FROM my_table
    START WITH (
        column_a = 'some value'
        AND column_b = 'some other value' 
    )
    CONNECT BY
        PRIOR childid = parentid
        AND column_b = 'whatever'
    ORDER BY sort_column
)
WHERE row_num BETWEEN <offset> and <offset+limit>

however, this statement would not limit the number of root entries but the overall entries, i.e. it chops off child nodes, as I do not know, how many children exist for a root node.

Answer 1

With the help of Philᵀᴹ's and Joe Obbish's comments I was able to create a query based on recursive CTEs (Common Table Expressions) that allows pagination on the top level of a hierarchical table.

The SQL statement looks like this:

WITH my_hierarchy (<column1>, <column2>, ...) AS (
  SELECT <column1>, <column2>, ... 
  FROM (
    SELECT <column1>, <column2>, ... , row_number() OVER (ORDER BY <order_column> ASC) row_num
    FROM my_table mt
    WHERE mt.<column1> = 'some value'
      AND mt.<column2> = 'other value'
  )
  WHERE row_num between <offset> and <offset+limit> -- pagination on top level
  UNION ALL
  SELECT mt.<column1>, mt.<column2>, ...
    FROM my_table mt
    INNER JOIN my_hierarchy mh   -- recursive self join
        ON mt.parentId = mh.id
)
SELECT *
FROM my_hierarchy