Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-78016

Use Oracle 12 support for OFFSET ... FETCH clauses (LIMITs)


      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).


            stronk7 Eloy Lafuente (stronk7)
            stronk7 Eloy Lafuente (stronk7)
            Paul Holden Paul Holden
            Andrew Lyons Andrew Lyons
            CiBoT CiBoT
            0 Vote for this issue
            3 Start watching this issue


                Original Estimate - Not Specified
                Not Specified
                Remaining Estimate - 0 minutes
                Time Spent - 6 hours, 20 minutes
                6h 20m

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.