Re: maximum number of expressions in a list is 1000

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Wed, 2 Jan 2013 14:55:11 +0000
Message-ID: <CABe10saSbmVkJDc7kA81Bq+6cCeGkf6_YjXfnTUB7FEcKKthyg_at_mail.gmail.com>

I don't see why..
[oracle_at_dev ~]$ sqlplus niall/niall

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 2 04:10:14 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t1(
  2 id number
  3 , content varchar2(50));

Table created.

SQL> insert into t1 (id,content) values (1, 'My Row');

1 row created.

SQL> commit;

Commit complete.

SQL> select content from t1 where id in (1);

CONTENT

---

My Row

SQL> c/(1/(1,1,1
  1* select content from t1 where id in (1,1,1) SQL> / CONTENT

---

My Row

SQL>
SQL>
SQL> create global temporary table gtt_id (id number);

Table created.

SQL> insert into gtt_id values (1);

1 row created.

SQL> / 1 row created.

SQL> / 1 row created.

SQL> select content from t1 where id in (   2 select id from gtt_id);

CONTENT

---

My Row

obviously if the select also accessed the table to which we are joining then YMMV as they say.

On Wed, Jan 2, 2013 at 2:31 PM, Job Miller <jobmiller_at_yahoo.com> wrote:

> If you put all those rows into a GTT and did a join, you'd get the same
> row back from the join 3 times, instead of only once.
>
> You'd have to have a unique constraint on the GTT i believe.
>
>
> ------------------------------
> *From:* Niall Litchfield <niall.litchfield_at_gmail.com>
> *To:* Timo Raitalaakso <rafu_at_iki.fi>
> *Cc:* ORACLE-L <oracle-l_at_freelists.org>
> *Sent:* Wednesday, January 2, 2013 7:13 AM
> *Subject:* Re: maximum number of expressions in a list is 1000
>
> Nice link thanks Timo. I also like the pointer to jdbc batching in the
> comments to your update article.I don't understand your duplicates comment
> though. For example given table t1
> create table t1(
> id number
> , content varchar2(500));
>
> insert into t1(id,content) values( 1, 'My Row');
> commit;
>
> The output of
>
> select content from t1 where id in (1,1,1);
>
> is the same single row as
>
> select content from t1 where id in (1);
>
>
>
>
> On Wed, Jan 2, 2013 at 11:29 AM, Timo Raitalaakso <rafu_at_iki.fi> wrote:
>
> >
> > If you are using Java here is more information on top of Adrian
> > Billington's approach
> > http://rafudb.blogspot.fi/2011/10/variable-inlist.html Describing
> > ArrayDescriptor.createDescriptor to use the table of sql type as a bind
> > from Java.
> >
> > I would prefer sticking with in list instead of changing the query to a
> > join. It might be that someone is binding the same values several times
> > and so your query result changes to include duplicates.
> >
> > --
> > Timo Raitalaakso
> >
> > On 2.1.2013 12:40, Niall Litchfield wrote:
> > > On Wed, Jan 2, 2013 at 10:22 AM, Norman Dunbar <oracle_at_dunbar-it.co.uk
> > >wrote:
> > >>> Is there a workaround to avoid this error in oracle?
> > >>
> > > or even try approaching that problem as
> > >
> > > select <the columns I really want in the end>
> > > from t1 JOIN t2
> > > where FILTER
> > >
> > > replacing JOIN and FILTER as appropriate.
> > >
> > > ISTM that the most appropriate answer to the original question will
> > depend
> > > upon where your list of ID values actually comes from. You may well
> find
> > > Adrian Billington's article on binding in lists useful as well.
> > > http://www.oracle-developer.net/display.php?id=301
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


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

Received on Wed Jan 02 2013 - 15:55:11 CET