Pagination is extremely slow on larger datasets

[Miguel-Serejo]

Summary of problem or feature request

When paginating a query with a result set of nearly a million records, the query executes very slowly (>10 seconds).

Testing the generated query on Oracle SQL Developer, the query takes about 8 seconds.

A modification to the pagination query makes the query nearly instant, by using a combination of rownum >= $start and rownum <= $end instead of rownum between $start and end. This is the pagination method explained here: https://blogs.oracle.com/oraclemagazine/on-rownum-and-limiting-results which was tested to be the fastest pagination option by https://stackoverflow.com/a/6536249

I altered the generated query in OracleGrammar.php and got the query down from 5-12 seconds to 5-20 milliseconds:

/**
     * Compile a common table expression for a query.
     *
     * @param  string $sql
     * @param  string $constraint
     * @param Builder $query
     * @return string
     */
    protected function compileTableExpression($sql, $constraint, $query)
    {
        if ($query->limit == 1 && is_null($query->offset)) {
            return "select * from ({$sql}) where rownum {$constraint}";
        }

        if (!is_null($query->limit && !is_null($query->offset))) {
          $start  = $query->offset + 1;
          $finish = $query->offset + $query->limit;
          return "select t2.* from ( select rownum AS \"rn\", t1.* from ({$sql}) t1 where rownum <= {$finish}) t2 where t2.\"rn\" >= {$start}";
        }

        return "select t2.* from ( select rownum AS \"rn\", t1.* from ({$sql}) t1 ) t2 where t2.\"rn\" {$constraint}";
    }

Here's an image album containing screenshots from query execution both in SQL Developer and in laravel, as shown by debugbar. Please note I did not screencap "cold" queries, so the values here are somewhat faster than they normally would be for first requests:

https://imgur.com/a/c0vzsTE

Note how the between method does not make use of the STOPKEY operation, which makes it much slower.

I'm unsure if this behavior is the same on more recent versions of oracle. If anyone can test the changes on later versions I'd appreciate it.

System details

• Operating System windows 10 / ubuntu 16.04 (same behavior)
• PHP Version 7.2
• Laravel Version 6.14.0
• Laravel-OCI8 Version 6.1.0
• Oracle Server Version 11g (11.2.0.2.0 - Production)

[yajra]

Sorry for late reply. I'm definitely open to improving the pagination and will give your suggestions a try. Also, please do not hesitate to submit a PR for optimizations/suggestions. Thanks!