Apply Offset And Limit In Oracle For Complex Join Queries?

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 th

Solution 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 (PARTITIONBY DEPT.CREATE_DATE ORDERBY DEPT.ID) AS rn
          FROM TEST.DEPT_TABLE DEPT
          LEFTJOIN TEST.STATUS_TABLE statusT
            ON DEPT.STATUS = statusT.STATUS
          LEFTJOIN TEST.C_REL_DEPT rel
            ON DEPT.ID = REL.ROWID_DEPT2
          LEFTJOIN TEST.DEPT_TABLE DEPT2
            ON REL.ROWID_DEPT1 = DEPT2.ID) q
 WHERE rn BETWEEN3AND8;

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.