Блог

мы сталкиваемся с проблемами производительности с EclipseLink 2.7.7 при доступе к таблицам Oracle 12.1 с помощью подкачки. Исследование показало, что 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;
 

План объяснения показывает, что используется индекс on 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;
 

Для этого запроса план объяснения показывает, что индекс on colN не используется.
В результате запрос таблицы с миллионами строк занимает 50-90 секунд (в зависимости от аппаратного обеспечения).
Примечание: в моей тестовой базе данных этот запрос возвращает 0 записей, поскольку значения ColN указаны до 2021-12-08.

Oracle 12c ввел синтаксис СМЕЩЕНИЯ / ВЫБОРКИ:

 -- #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 использовать этот синтаксис.

Если ORDER BY col1 DESC он удален из исходного постраничного запроса (# 2), используется индекс, запрос возвращается достаточно быстро. Однако он не вернет нужные записи, так что это не поможет.

Как я могу реализовать высокопроизводительные постраничные запросы с использованием EclipseLink и Oracle 12? Как я могу заставить oracle использовать индекс on colN при использовании подкачки и order by ?

printSQLSelectStatement Метод OraclePlatform отвечает за построение используемого запроса, вложение запросов для использования 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) amp;amp; !(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"/>
 

Если что-то подобное работает для вас, пожалуйста, отправьте это как запрос на улучшение — хотя вы, возможно, захотите каким-то образом использовать в нем старое поведение, поскольку различия в производительности, с которыми вы столкнулись, могут зависеть от используемого запроса / данных.

Благодаря @Chris я придумал следующее Oracle12Platform . В настоящее время это решение игнорирует «Ошибка # 453208 — пессимистическая блокировка с ограничениями строк запроса не работает в Oracle DB». 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) amp;amp; !(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);
            }
        }
    }
}
 

• Мне пришлось переопределить computeMaxRowsForSQL , чтобы получить количество строк вместо «lastRowNum» при вызове printer.printParameter(DatabaseCall.MAXROW_FIELD);
• Я также пытаюсь разобраться с отсутствующим firstRow xor maxResults