Re: maximum number of expressions in a list is 1000

• Original Message ----- From: "Jose Soares" <jose.soares_at_sferacarta.com> To: <oracle-l_at_freelists.org> 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}
where

    {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())

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

--
http://www.freelists.org/webpage/oracle-l