[Dbix-class] Regarding Oracle's 1000 element limit in IN clause

Dave Howorth dhoworth at mrc-lmb.cam.ac.uk
Mon Oct 24 13:34:16 GMT 2011

There's another possibility that might be usable, which is to transform
the query set. I don't think Jorge has said what the datatype of the
filter set is, but perhaps it is numeric or alphabetic.

If numeric, it may be possible to replace some of the 40,000 individual
value tests by range tests. Even if that doesn't produce the precise
resultset wanted, it can be supplemented by additional queries to
retrieve more rows or by additional filtering to remove false hits.

Similarly if the data type is alphabetic, it may be possible to combine
multiple filters using by pattern-matching queries.

Those techniques should reduce the length of the query sent to the server.

Of course the original suggestion of IN OR IN OR IN is much simpler to
implement, as is mirroring the source if that can be done without
performance, consistency or terms-of-business problems.

Cheers, Dave

More information about the DBIx-Class mailing list