Oracle 12c Top-N query with Rails

Oracle 12c finally introduces straightfoward Top-N query support. This article shows a initial version of native Oracle 12c Top-N query support with Rails.

How it works with Rails?

Arel is taking part of translating Rails such method as limit, offset code into SQL clause. Arel::Visitors::Oracle
has Oracle specific implementation. To support older version of Oracle, current implementations require a lot of "dirty" hacks.

If you are using Rails 4.1 let's give it a try by updating your Gemfile and bundle install.

gem 'arel', github: 'yahonda/arel', branch: 'sandbox_limit'

SQL statements generated

Let's take a look at one example and see how Rails code is translated into SQL by comparing with current Oracle implementation, PostgreSQL and Oracle 12c native support.

  • Rails code
Entrant.order("id ASC").limit(2)
  • Current Oracle
SELECT * FROM (SELECT "ENTRANTS".* FROM "ENTRANTS" ORDER BY id ASC) WHERE ROWNUM <= 2
  • PostgreSQL
SELECT "entrants".* FROM "entrants" ORDER BY id ASC LIMIT 2
  • Oracle 12c native support
SELECT "ENTRANTS".* FROM "ENTRANTS" ORDER BY id ASC FETCH FIRST 2 ROWS ONLY

You can find the 12c native support is very easy to understand.

Restrictions and notes

  • Oracle does not support select for update statements with FETCH FIRST <n> ROWS ONLY then ORA-02014 error generated. This implementation just removes limit when it used with lock.

  • This build is an experimental one. So far no plan to open a pull request to github.com:rails/arel yet. I'd appreciate if you test it, but not recommend to use in production environment.

References

Thanks for reading my initial article.