Re: maximum number of expressions in a list is 1000

From: Jonathan Lewis <>
Date: Wed, 2 Jan 2013 10:51:45 -0000
Message-ID: <D80A07E0F65240B9971CB02EF388FB16_at_Primary>

  • Original Message ----- From: "Jose Soares" <> To: <> Sent: Wednesday, January 02, 2013 10:00 AM Subject: maximum number of expressions in a list is 1000

| Hi all,
| I'm trying a query like this:
| SELECT id, name from anagrafica WHERE id in (100,32,43,66,2,1,334,
| ...... more than 1000 values)
| and I got this error:

A human being won't want 1,000 rows to view, so step 1 is to figure out why your application wants them and work out a better strategy for getting the data.
Ideas like:

select from

    (select that gives me 1,000+ values),     {other tables}

    {join condition to list of values}

or have a global temporary table to hold the list

    insert into gtt {statement that generates 1,000+ values efficiently}

    select from gtt, {rest of tables} where {join}

One problem with any workaround that leaves you with an IN list is that the optimizer may convert your in-list to a concatenation or iterator, and effectively run a statement 1,000+ times, one for each item in the list. (You may find that (column in (list of 1000) or column in (list of 1000) or ... works - there used to be a dirty trick that simple for bypassing the equivalent limit on decodes())


Jonathan Lewis

Author: Oracle Core (Apress 2011)

Received on Wed Jan 02 2013 - 11:51:45 CET

Original text of this message