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
offset code into SQL clause.
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
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
- Current Oracle
SELECT * FROM (SELECT "ENTRANTS".* FROM "ENTRANTS" ORDER BY id ASC) WHERE ROWNUM <= 2
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 updatestatements with
FETCH FIRST <n> ROWS ONLYthen
ORA-02014error generated. This implementation just removes
limitwhen it used with
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.
Thanks for reading my initial article.