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.