在 ORACLE 中应用 OFFSET 和 LIMIT 来处理复杂的联接查询?

问题描述

我正在使用 Oracle 11g 并且有一个复杂的连接查询.在这个查询中，我真的想应用 OFFSET 和 LIMIT 以便有效地在 Spring Batch Framework 中使用.

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.

我经历了:如何限制排序后 Oracle 查询返回的行数? 和

在 Oracle 中用于分页的 LIMIT 和 OFFSET 的替代方案

但事情对我来说不是很清楚.

But things are not very clear to me.

我的查询

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 给了我 1000 万条记录.

Above Query gives me 10 millions records.

注意:两个数据库表都没有 PK，所以我需要使用 OFFSET 和 LIMIT.

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

推荐答案

假设您需要，您可以在 Oracle 11g 的子查询中使用诸如 ROW_NUMBER() 之类的分析函数获取排在第 3 位和第 8 位之间的行，以便捕获 Oracle DB 中的 OFFSET 3 LIMIT 8 逻辑(确实这些子句包含在 12c+)，每当结果应按 CREATE_DATE 分组并按部门的 ID 排序时:

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;

正好返回 6(8-3+1) 行.如果您需要包含关系(每个创建日期的部门标识的相等值)，ROW_NUMBER() 应替换为另一个名为 DENSE_RANK() 的窗口函数查询的其他部分保持不变.在这种情况下至少会返回 6 条记录.

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.

这篇关于在 ORACLE 中应用 OFFSET 和 LIMIT 来处理复杂的联接查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！