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

Ben Tilly btilly at gmail.com
Mon Oct 24 15:21:50 GMT 2011


An important note.  While this may work, performance is likely to be a
problem.  In my experience when trying to match an IN clause, Oracle
goes through all of values in turn to test the condition.  If there
are a lot of values, this is slow  The 1000 value limit within Oracle
should be taken as a hint from Oracle that you're doing something
wrong.  They actively want you to use a temporary table with an index.

It is ugly and a lot more work, but performance should be
significantly better if you use a nested CASE statement that broke it
into sections.  Something like this:

  1 = CASE
        WHEN your_field < 'b'
          THEN
            CASE
              WHEN your_field IN ('aaa', 'aab', ..., 'aaz')
              THEN 1
              ELSE 0
            END
        WHEN your_field < 'c'
          THEN
            CASE
              WHEN your_field IN ('baa', 'bab', ..., 'baz')
              THEN 1
              ELSE 0
            END
        ...
      END

Of course performance would be much better with a temporary table, but
bureaucracy has blocked that...

On Mon, Oct 24, 2011 at 7:15 AM, Alexander Hartmaier
<alexander.hartmaier at t-systems.at> wrote:
> Why don't you use the column IN (...) OR column IN (...) solution?
> I'd like to see this as a patch for the Oracle SQLMaker.
>
> Cheers, Alex
>
> Am 2011-10-24 16:06, schrieb Jorge Gonzalez:
>>
>> El 24/10/11 13:46, Matija Grabnar escribió:
>>>>
>>>> Question is, my server has enough RAM to slurp the resultset and
>>>> then search (which is what I'm doing now). If enough RAM is
>>>> available, no disk-based SQL server can beat that, provided that
>>>> efficient search algorithms are used (I'm using the resultset to
>>>> create several RAM based indexes - perl hashes - before doing any
>>>> searching).
>>>
>>> You would be surprised at what disk-based SQL servers can do,
>>> particularly when compared to programs written in an interpreted
>>> language. If your RAM based searches ran fast enough for your needs,
>>> I don't think you'd be writing here. If they don't run fast enough
>>> for you, you can implement a quick benchmark to see how  a DISK-based
>>> SQL server can do.
>>
>> I would not classify Perl as an interpreted language - at least not in
>> the traditional sense. My search function is the perl hash search
>> function which I suspect is highly optimized. And I maintain my words:
>> no disk-based SQL server is going to beat an in-memory search, given
>> that the data is correctly indexed in both systems. All other factors
>> equal (or similar), RAM access times and disk access times are
>> different by several orders of magnitude.
>>
>> The queries run fast enough (for now), but they will be a problem
>> sometime in the future: instead of doing a fixed number of queries
>> regardless of the number of elements to filter, I have to do one for
>> each 1000 elements: instead of constant cost, I have linear cost. For
>> now it's ok, but soon there will be not 40.000 elements but 200.000 to
>> filter, and then I'll have to do 200 queries instead of 40. And what
>> about when I have to filter by 1000000 values?
>>
>> In the long term, I suppose I'll end mirroring parts of the data
>> warehouse locally, syncing it by the night, as someone has already
>> suggested.
>>
>> Thanks to all that have responded.
>> BR
>> J.
>>
>>
>> _______________________________________________
>> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>> IRC: irc.perl.org#dbix-class
>> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>> Searchable Archive:
>> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
> T-Systems Austria GesmbH   Rennweg 97-99, 1030 Wien
> Handelsgericht Wien, FN 79340b
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
> Notice: This e-mail contains information that is confidential and may be
> privileged.
> If you are not the intended recipient, please notify the sender and then
> delete this e-mail immediately.
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>



More information about the DBIx-Class mailing list