I think Rails 2.1 introduced a bug when eager loading of associations
with Oracle. In 2.1 ActiveRecord loads the included table with a big
'IN' query: 'where association.id in (1, 2, 3, 4...)'. The problem
is that Oracle has a hard limit of 1000 values in an IN clause. I get
this error:
'OCIError: ORA-01795: maximum number of expressions in a list is 1000
Of course getting 1000 records in one shot is probably too many; you'd
want to paginate. But still sometimes you underestimate the size your
db will grow too, and a really slow query is better than one that
blows up.
Has anyone else noticed this problem and is there a simple solution?
I know that I can specify a dummy order by or conditions clause on the
included table to force a normal join, but I'd have to change a lot of
code.
Did write the new eager loading code, or the oracle adapter?
I wrote the eager loading code - I don't know the slightest thing
about oracle. Conceivably the array could be chopped into pieces
before we try and load it.
I tried the following code, which is icky but seems to work fine.
You'd probably want to only do this for database where this is an
issue, so maybe the adapter could provide a method supplying the in
clause limit (actually its the max # of expression in any oracle sql
list).
id_sets =
condition_clauses =
limit = 1000
0.step(ids.size, limit) do |i|
id_sets << ids[i, limit]
if interface
condition_clauses <<
"#{reflection.klass.quoted_table_name}.#{connection.quote_column_name
"#{interface}_id"} IN (?)"
else
foreign_key = reflection.primary_key_name
condition_clauses <<
"#{reflection.klass.quoted_table_name}.#{foreign_key} IN (?)"
end
end
conditions = condition_clauses.join(" OR ")
if interface == reflection.options[:as]
conditions << " and
#{reflection.klass.quoted_table_name}.#{connection.quote_column_name
"#{interface}_type"} = '#{self.base_class.name.demodulize}'"
end