Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 437 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
ORA-01795, limit on in clause

405508
Member Posts: 17
hi guys,
I have the following query,
Select
shogen_no,
shogen_desc,
disp_turn
From
shogen
where
shogen_no in ('one', 'two', 'three', ..........'more than thousand')
When I try to run it, the ORACLE (8.1.6) gives me following error:
ORA-01795 maximum number of expressions in a list is 1000
Can anyone explain the situation?
1) Cant we put more than 1000 values in 'in clause'.
2) What is the other way of doing it.
I am running this query in a PL/SQL procedure. First of all I gather the values and put them in a varchar2 (shogenList). Then I put that variable in the 'in clause' i.e. where shogen_no in ('||shogenList||')..
Thanx for any help
Omer
I have the following query,
Select
shogen_no,
shogen_desc,
disp_turn
From
shogen
where
shogen_no in ('one', 'two', 'three', ..........'more than thousand')
When I try to run it, the ORACLE (8.1.6) gives me following error:
ORA-01795 maximum number of expressions in a list is 1000
Can anyone explain the situation?
1) Cant we put more than 1000 values in 'in clause'.
2) What is the other way of doing it.
I am running this query in a PL/SQL procedure. First of all I gather the values and put them in a varchar2 (shogenList). Then I put that variable in the 'in clause' i.e. where shogen_no in ('||shogenList||')..
Thanx for any help
Omer
Comments
-
You cannot have more than 1000 literals in an IN clause. You can, however, have SELECT statements in your IN clause which can return an unlimited number of elements i.e.
SELECT shogen_no,
Justin
shogen_desc,
disp_turn
FROM shogen
WHERE shogen_no IN ( SELECT shogen_no
FROM <<some other table>> )
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC -
You might try using 'between' clause replacing 'in'... check documentation for correct syntax on using between.
-
Just add some ORs . . .
where
shogen_no in ('one', 'two', 'three') or
shogen_no in ('four', 'five', 'six') or ...
-
adding 'OR' makes the query extremely slow.....
-
Presumably, if you can gather the literals in a PL/SQL stored procedure, you can gather them in a single SQL statement and just embed that in your IN clause though. Not only will it be faster to execute, you don't have to worry about doing all that string manipulation.
Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC -
I just responded to this same issue. Don't use the IN clause if at all possible when it is driven by a subquery. First you can return too many rows for IN clause itself (which is what you are seeing). Second, the performance of IN in this context is poor. Oracle doesn't handle the relationship well.
Select
shogen_no,
shogen_desc,
disp_turn
From
shogen
where
shogen_no in ('one', 'two', 'three', ..........'more than thousand')
Change to
select ...
from shogen
where
exists (select 1 from table used to build shogenList where shogenlist.value = shogen.value)
This example would be better if you had included the original query to build shogenlist. We have rewritten a ton of our queries using this syntax. Check out the explain when you are done. -
IN and EXISTS both have their uses. If shogen is a multi-million row table, IN will probably be faster. If shogen has a few hundred rows, EXISTS will probably be faster. It it is in the middle, it's a good idea to try both.
Additionally, EXISTS doesn't work particularly well when you have hard coded values.
Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC -
Hi,
this error means
ORA-01795 maximum number of expressions in a list is 1000
Cause: More than 254 columns or expressions were specified in a list.
Action: Remove some of the expressions from the list.
One way you can use a OR conditions like
shogen_no in (...)
or shogen_no in ().....
thanks -
Hi,Firstly, well done for answering a thread that's 3.5 years old and secondly well done for answering it with an answer that's already been given and thirdly well done for answering it with an answer that does not offer the best solution to the problem as has already been explained.
this error means
ORA-01795 maximum number of expressions in a list is
1000
Cause: More than 254 columns or expressions were
specified in a list.
Action: Remove some of the expressions from the list.
One way you can use a OR conditions like
shogen_no in (...)
or shogen_no in ().....
thanks
Good to see you read the threads you are answering. -
I stumbled upon this thread when I was searching for a solution to this issue. I was exploring if there are any alternatives to just replacing the string with a IN with a select. I hit on this solution, which was very interesting, it uses an array of table. Take a look:
http://www.orafaq.com/forum/t/41437/0/
PS: I know I am posting to an older thread, but only to keep the information in this thread in case it is useful to others.
This discussion has been closed.