Re: maximum number of expressions in a list is 1000

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Thu, 3 Jan 2013 15:42:31 +0400
Message-ID: <CAOVevU6AgX=QK_Hx8+8Bdj7-P8AuKZWhYiU+Hg1X-2hvwDL2dA_at_mail.gmail.com>

First of all, I want to say, that, although this is a simple solution with minimal changes to the code, but still prefer to use other options such as a collection binding or gtt(but don't forget about cardinality).
About limitation: It is described here:
http://docs.oracle.com/cd/E14072_01/server.112/e10592/expressions015.htm Quote from it:
"A comma-delimited list of expressions can contain no more than 1000 expressions. A comma-delimited list of sets of expressions can contain any number of sets, but each set can contain no more than 1000 expressions."

On Thu, Jan 3, 2013 at 12:03 PM, Norman Dunbar <oracle_at_dunbar-it.co.uk> wrote:
> On 03/01/13 07:21, jo wrote:
>> Sayan Malakshinov wrote:
>>> It can be solved through: (field, -1) in((1,-1),(2,-1),(3,-1)...(n,-1))
>
>> This solution is great, Sayan.
>> I didn't know this syntax.
>> For curiosity, what does it mean the -1 in the second position?
>> I suppose it can be every value, -1 or everything else.
>
> You can do this with IN, it's not just a single column type expression:
>
> SELECT ... WHERE (id, id2) in ((1, 1), (1, 2), (2, 2), (3, 3), (4, 7), ...);
>
> So the above with the -1 is just saying:
>
> SELECT ... WHERE (id, id2) in ((1, -1), (3, -1), (2, -1), (3, -1), (4,
> -1), ...);
>
>
> However, I have to say that I wasn't aware that this could get past the
> 1000 literal expressions in an in-list restriction.
>
>
> Cheers,
> Norm.
>
> --
> Norman Dunbar
> Dunbar IT Consultants Ltd
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

-- 
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
Tel: +7 903 207-1576
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 03 2013 - 12:42:31 CET