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.