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

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

    XMLWordPrintable

Details

    Description

      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

      Attachments

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

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

                  Clockify

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