January 26, 2008
When using Ruby on Rails on Oracle I noticed that my database migration tasks are much slower than on MySQL.
I just found the cause for that – rake db:schema:dump task was taking very long time on my Oracle databases (and this task is executed at the end of rake db:migrate). As a result of this task Oracle Rails adapter is executing
SELECT LOWER(table_name) FROM user_tables
statement. If your database contains a lot of other schemas with a lot of tables (as in my case I deploy Rails user schema in Oracle E-Business Suite database) then this statement is quite slow (it took more than 10 seconds in my case).
As all Rails tables are located in Rails user schema then you can get the same result with
SELECT LOWER(table_name) FROM all_tables WHERE owner = SYS_CONTEXT('userenv','session_user')
which now executes much faster than original statement.
So you can make the following patch for Oracle Rails adapter to make this change
module ActiveRecord module ConnectionAdapters class OracleAdapter def tables(name = nil) select_all("select lower(table_name) from all_tables where owner = sys_context('userenv','session_user')").inject() do | tabs, t | tabs << t.to_a.first.last end end end end end
and include it in environment.rb file or in some separate patch file which you load at Rails startup.
P.S. I have published all my patches for Oracle Rails 2.0 adapter. You can download this file, place it in Rails application lib directory and then just include
in your environment.rb file. Look at comments in this file to find out what each patch is doing.