OraFAQ Forum: SQL & PL/SQL » How specify more than 1000 values in IN operator

Home » SQL & PL/SQL » SQL & PL/SQL » How specify more than 1000 values in IN operator

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How specify more than 1000 values in IN operator [message #200750]

Wed, 01 November 2006 03:42

sreehari
Messages: 101
Registered: May 2006

Senior Member

Hi all

How to specify more than 1000 values in IN operator in SELECT statement,

eg:select * from emp where empid in (3,4,66,74,...upto more than 1000 values)

i am getting
ORA-01795: maximum number of expressions in a list is 1000

is there any alternative in SQL for above query.

Thanks

Report message to a moderator

Re: How specify more than 1000 values in IN operator [message #200752 is a reply to message #200750]

Wed, 01 November 2006 03:47

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

If you can get the values into a temporary table, you can use a sub select:

select * 
from   emp 
where empid in (SELECT id from global_temporary_table)

If you can get the values into a userdefined table type (needs to be defined in SQL, not in a package, you can do

SELECT * 
from   emp 
where empid in (SELECT * from table(user_table))

If you can't do any of these, you can use an OR

SELECT * 
from   emp 
where (empid in (1,2,3....1000)
    OR empid in (1001,1002....))

Report message to a moderator

Re: How specify more than 1000 values in IN operator [message #200754 is a reply to message #200750]

Wed, 01 November 2006 04:00

sreehari
Messages: 101
Registered: May 2006

Senior Member

Thank for ur reply..

I think 3rd solution fit to my issue..

Report message to a moderator

Re: How specify more than 1000 values in IN operator [message #200756 is a reply to message #200754]

Wed, 01 November 2006 04:01

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Well... it's the worst of the lot.

If you're using that many values, you'd almose certainly be better off using a global temporary table instead.

Report message to a moderator

Re: How specify more than 1000 values in IN operator [message #200759 is a reply to message #200756]

Wed, 01 November 2006 04:09

sreehari
Messages: 101
Registered: May 2006

Senior Member

ok,,but how to create global temporary table ...

Thanks

Report message to a moderator

Re: How specify more than 1000 values in IN operator [message #200760 is a reply to message #200759]

Wed, 01 November 2006 04:20

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Just to add,
JRowbottom's first method using GTT should work very good.
Recently i was asked to look into a performance problem.
The batch job has anywhere between 50,000 to 100,000 values to be used in IN clause and broken into 1000 column sql statements.
We recomended to use GTT and use a table join. Job completed in less than 10 seconds (load the GTT,set statistics for GTT in some cases,do the join ) where it used to be more than 1 hour.

>> ok,,but how to create global temporary table ..
search the forum, read documentation or atleast google yourself.

Report message to a moderator

Re: How specify more than 1000 values in IN operator [message #200797 is a reply to message #200750]

Wed, 01 November 2006 06:37

sreehari
Messages: 101
Registered: May 2006

Senior Member

thanks..all

i could get it..

Report message to a moderator

Previous Topic:	SQL output
Next Topic:	how I can delete a row

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 16 09:51:01 CDT 2026