0

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.

2
  • Do the root entries have a PK, or a field that you can ORDER BY? You'll be able to use a CTE for this
    – Philᵀᴹ
    Oct 18, 2016 at 8:20
  • OFFSET n ROWS FETCH NEXT m ROWS ONLY is just syntactic sugar for ROW_NUMBER(). ROW_NUMBER() can be used as an efficient replacement for that syntax in 11g, but I don't know how it interacts with the START WITH/CONNECT BY stuff.
    – Joe Obbish
    Oct 18, 2016 at 15:58

1 Answer 1

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 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.