Eager load associations in Oracle problem with more than 1000 records

Rails
1

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.

Thanks.

Steve

2

On 3 Sep 2008, at 23:02, [email protected] wrote:

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.

Not that I know of (And i wrote that code - sorry!)

Fred

3

Fred,

Did write the new eager loading code, or the oracle adapter?

I’m not sure what the best way to fix this is, except to revert back
to the old way for oracle.

Steve

On Sep 4, 2:40 am, Frederick C. [email protected]

4

On 4 Sep 2008, at 18:54, “[email protected]”
<[email protected]

wrote:

Fred,

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.

5

On Sep 4, 2:25 pm, Frederick C. [email protected]
wrote:

Conceivably the array could be chopped into pieces
before we try and load it.

Yeah, either by issuing multiple ‘where in’ queries per 1000 ids, or
something hokey like this:

where (id in (1, 2, 3…)) or (id in (1001, 1002, 1003…)) or …

I don’t know if oracle has a limit on the maximum query string
size…

Steve

6

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).

From associations_preload.rb:

  def find_associated_records(ids, reflection, preload_options)
    options = reflection.options
    table_name = reflection.klass.quoted_table_name
    interface = reflection.options[:as]

    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

    conditions << append_conditions(options, preload_options)

    reflection.klass.find(:all,
                          :select => (preload_options[:select] ||

options[:select] || “#{table_name}.*”),
:include => preload_options[:include] ||
options[:include],
:conditions => [conditions, ids],
:joins => options[:joins],
:group => preload_options[:group] ||
options[:group],
:order => preload_options[:order] ||
options[:order])
end

On Sep 4, 5:02 pm, “[email protected]” [email protected]