EclipseLink with Oracle: "limit by rownum" does not use index

Question

we're facing performance issues with EclipseLink 2.7.7 when accessing Oracle 12.1 tables with paging. Investigation showed that Oracle does not use its indexes with EclipseLink paging.

I've extracted the sql sent to the database and was able to reproduce the issue using a database tool (DataGrip).

Example:

-- #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;

Explain plan shows that the index on colN is used. Fine.

When the same query is executed with paging, the original query is wrapped in two subselects:

-- #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;

For this query, the explain plan shows that the index on colN is not used. As a result, querying a table with millions of rows takes 50-90 seconds (depending on the hardware). Side note: on my test database, this query returns 0 records since colN values are before 2021-12-08.

Oracle 12c introduced the OFFSET/FETCH syntax:

-- #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;

Using this syntax, indexes are at least sometimes used as expected. When they are used, execution time is below 1s which is acceptable. However, I could not figure out how to convince EclipseLink to use this syntax.

If ORDER BY col1 DESC is removed from the original paged query (#2), the index is used the query returns fast enough. However, it will not return the desired records, so that does not help.

How can I implement performant paged queries using EclipseLink and Oracle 12? How can I force oracle to use the index on colN when using paging and order by?

Answer 1

Thanks to @Chris, I came up with the following Oracle12Platform. This solution currently ignores "Bug #453208 - Pessimistic locking with query row limits does not work on Oracle DB". See OraclePlatform.printSQLSelectStatement for details):

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);
            }
        }
    }
}

• I had to override computeMaxRowsForSQL in order to get the row count instead of "lastRowNum" when calling printer.printParameter(DatabaseCall.MAXROW_FIELD);
• I also try to deal with missing firstRow xor maxResults