EclipseLink with Oracle："limit by rownum"不使用索引

Question

我们在使用分页访问Oracle 12.1表时，遇到了EclipseLink 2.7.7的性能问题。调查显示，Oracle并没有将其索引用于EclipseLink分页。

我已经提取了发送到数据库的sql，并能够使用数据库工具(DataGrip(重现该问题。

示例：

-- #1: without paging
SELECT col1 AS a1, col2 AS a2, col3 AS a3, ...
FROM <TABLE>
WHERE colN > to_timestamp('2021-12-08', 'yyyy-mm-dd'))
ORDER BY col1 DESC;

解释计划显示使用了colN上的索引。好的

当使用分页执行相同的查询时，原始查询被封装在两个子选择中：

-- #2 with EclipseLink paging
SELECT * FROM (
SELECT a.*, ROWNUM rnum  FROM (
SELECT col1 AS a1, col2 AS a2, col3 AS a3, ...
FROM <TABLE>
WHERE colN > to_timestamp('2021-12-08', 'yyyy-mm-dd'))
ORDER BY col1 DESC    
) a WHERE ROWNUM <= 100
) WHERE rnum > 0;

对于此查询，解释计划显示未使用colN上的索引。因此，查询一个包含数百万行的表需要50-90秒(具体取决于硬件(。附带说明：在我的测试数据库中，此查询返回0条记录，因为colN值在2021-12-08之前。

Oracle12c引入了OFFSET/FETCH语法：

-- #3
SELECT col1 AS a1, col2 AS a2, col3 AS a3, ...
FROM <TABLE>
WHERE colN > to_timestamp('2021-12-08', 'yyyy-mm-dd'))
ORDER BY col1 DESC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

使用这种语法，索引至少有时会按预期使用。当使用它们时，执行时间低于1秒，这是可以接受的。然而，我不知道如何说服EclipseLink使用这种语法。

如果从原始分页查询(#2(中删除了ORDER BY col1 DESC，则使用索引—查询返回的速度足够快。但是，它不会返回所需的记录，因此没有帮助。

如何使用EclipseLink和Oracle12实现高性能分页查询？使用分页和order by时，如何强制oracle使用colN上的索引？

Answer 1

OraclePlatformprintSQLSelectStatement方法负责构建所使用的查询，嵌套查询以使用您看到的查询的rownum。要使用新的表单，您需要扩展您正在使用的OraclePlatform类之一(可能是Oracle12Platform(，并重写该方法以附加您想要的语法。类似于：

@Override
public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) {
int max = 0;
int firstRow = 0;
ReadQuery query = statement.getQuery();
if (query != null) {
max = query.getMaxRows();
firstRow = query.getFirstResult();
}
if (!(this.shouldUseRownumFiltering()) || (!(max > 0) && !(firstRow > 0))) {
super.printSQLSelectStatement(call, printer, statement);
return;
}
call.setFields(statement.printSQL(printer));
printer.printString("OFFSET ");
printer.printParameter(DatabaseCall.MAXROW_FIELD);
printer.printString(" ROWS FETCH NEXT ");
printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD);
printer.printString(" ROWS ONLY");
call.setIgnoreFirstRowSetting(true);
call.setIgnoreMaxResultsSetting(true);
}

然后，您将使用一个持久属性指定您的自定义OraclePlatform类：

<property name="eclipselink.target-database" value="my.package.MyOracle12Platform"/>

如果这样的东西对你有效，请将其作为增强请求提交——尽管你可能想以某种方式将旧的行为用于其中，因为你所经历的性能差异可能取决于所涉及的查询/数据。

Answer 2

感谢@Chris，我提出了以下Oracle12Platform。该解决方案目前忽略了"；错误#453208-带有查询行限制的悲观锁定在Oracle数据库上不起作用；。详见OraclePlatform.printSQLSelectStatement(：

public class Oracle12Platform extends org.eclipse.persistence.platform.database.Oracle12Platform {
/**
* the oracle 12c `OFFSET x ROWS FETCH NEXT y ROWS ONLY` requires `maxRows` to return the row count
*/
@Override
public int computeMaxRowsForSQL(final int firstResultIndex, final int maxResults) {
return maxResults - max(firstResultIndex, 0);
}
@Override
public void printSQLSelectStatement(final DatabaseCall call, final ExpressionSQLPrinter printer, final SQLSelectStatement statement) {
int max = 0;
int firstRow = 0;
final ReadQuery query = statement.getQuery();
if (query != null) {
max = query.getMaxRows();
firstRow = query.getFirstResult();
}
if (!(this.shouldUseRownumFiltering()) || (!(max > 0) && !(firstRow > 0))) {
super.printSQLSelectStatement(call, printer, statement);
} else {
statement.setUseUniqueFieldAliases(true);
call.setFields(statement.printSQL(printer));
if (firstRow > 0) {
printer.printString(" OFFSET ");
printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD);
printer.printString(" ROWS");
call.setIgnoreFirstRowSetting(true);
}
if (max > 0) {
printer.printString(" FETCH NEXT ");
printer.printParameter(DatabaseCall.MAXROW_FIELD); //see #computeMaxRowsForSQL
printer.printString(" ROWS ONLY");
call.setIgnoreMaxResultsSetting(true);
}
}
}
}

• 我必须重写CCD_ 9以便获得行计数而不是"0"；lastRowNum"呼叫printer.printParameter(DatabaseCall.MAXROW_FIELD);时
• 我还尝试处理丢失的firstRow或maxResults