Since Oracle 12, it supports OFFSET ... FETCH clauses (the SQL:2008 standard to LIMIT query results).

Link to tutorial / explanations: https://www.oracletutorial.com/oracle-basics/oracle-fetch/

In our oci_native_moodle_database driver we are using an old known "trick" (workaround) , based on ROWNUM and window functions/nested queries to achieve the same results.

This issue is about to remove that "trick" and start using the OFFSET ... FETCH feature.

Note that it's not expected to get any performance benefit with this, because the implementation is sort of syntax sugar that the RDBMS converts into queries similar to the "trick" that we are using now. In any case, maybe the internal RDBMS is more complete and performs better, specially with big datasets.

In the positive side, it leads to shorter and more readable SQL sentences and, also, will benefit from any improvement into the RDBMS implementation.

This can be applied to 401_STABLE and up (first version requiring Oracle 19).

Ciao