Apply OFFSET and LIMIT in ORACLE for complex Join Queries?

Question

I'm using Oracle 11g and have a complex join query. In this query I really wanted to apply OFFSET and LIMIT in order to be get used in Spring Batch Framework effectively.

I went through: How do I limit the number of rows returned by an Oracle query after ordering? and

Alternatives to LIMIT and OFFSET for paging in Oracle

But things are not very clear to me.

My Query

SELECT DEPT.ID rowobjid, DEPT.CREATOR createdby, DEPT.CREATE_DATE createddate, DEPT.UPDATED_BY updatedby, DEPT.LAST_UPDATE_DATE updateddate, 
DEPT.NAME name, DEPT.STATUS status, statusT.DESCR statusdesc, 
REL.ROWID_DEPT1 rowidDEPT1, REL.ROWID_DEPT2 rowidDEPT2, DEPT2.DEPT_FROM_VAL parentcid, DEPT2.NAME parentname 
FROM TEST.DEPT_TABLE DEPT 
LEFT JOIN TEST.STATUS_TABLE statusT ON DEPT.STATUS = statusT.STATUS 
LEFT JOIN TEST.C_REL_DEPT rel ON DEPT.ID=REL.ROWID_DEPT2 
LEFT JOIN TEST.DEPT_TABLE DEPT2 ON REL.ROWID_DEPT1=DEPT2.ID
ORDER BY rowobjid asc;

Above Query gives me 10 millions records.

Note: Neither database table has PK, so I would need to use OFFSET and LIMIT.

Answer 1

You can use Analytic functions such as ROW_NUMBER() within a subquery for Oracle 11g assuming you need to get the rows ranked between 3rd and 8th in order to capture the OFFSET 3 LIMIT 8 logic within the Oracle DB(indeed those clauses are included for versions 12c+), whenever the result should be grouped by CREATE_DATE and ordered by the ID of the departments :

SELECT q.*
  FROM (SELECT DEPT.ID rowobjid,
               DEPT.CREATOR createdby,
               DEPT.CREATE_DATE createddate,
               DEPT.UPDATED_BY updatedby,
               DEPT.LAST_UPDATE_DATE updateddate,
               DEPT.NAME name,
               DEPT.STATUS status,
               statusT.DESCR statusdesc,
               REL.ROWID_DEPT1 rowidDEPT1,
               REL.ROWID_DEPT2 rowidDEPT2,
               DEPT2.DEPT_FROM_VAL parentcid,
               DEPT2.NAME parentname,
               ROW_NUMBER() OVER (PARTITION BY DEPT.CREATE_DATE ORDER BY DEPT.ID) AS rn
          FROM TEST.DEPT_TABLE DEPT
          LEFT JOIN TEST.STATUS_TABLE statusT
            ON DEPT.STATUS = statusT.STATUS
          LEFT JOIN TEST.C_REL_DEPT rel
            ON DEPT.ID = REL.ROWID_DEPT2
          LEFT JOIN TEST.DEPT_TABLE DEPT2
            ON REL.ROWID_DEPT1 = DEPT2.ID) q
 WHERE rn BETWEEN 3 AND 8;

which returns exactly 6(8-3+1) rows. If you need to include the ties(the equal values for department identities for each creation date), ROW_NUMBER() should be replaced with another window function called DENSE_RANK() as all other parts of the query remains the same. At least 6 records would return in this case.