Forum Stats

  • 3,824,982 Users
  • 2,260,448 Discussions
  • 7,896,369 Comments

Discussions

Categories

ORA-01795, limit on in clause

405508
405508 Member Posts: 17
edited Mar 13, 2012 3:56AM in SQL & PL/SQL
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
user12266275Sam Jose
· Share on TwitterShare on Facebook
«1

Comments

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    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,
    
       shogen_desc,
    
       disp_turn
    
  FROM shogen
    
 WHERE shogen_no IN ( SELECT shogen_no 
    
                        FROM <<some other table>> )
    Justin
    Distributed Database Consulting, Inc.
    http://www.ddbcinc.com/askDDBC
    · Share on TwitterShare on Facebook
  • 137581
    137581 Member Posts: 17
    You might try using 'between' clause replacing 'in'... check documentation for correct syntax on using between.
    · Share on TwitterShare on Facebook
  • 415189
    415189 Member Posts: 85
    Just add some ORs . . .

    where
    shogen_no in ('one', 'two', 'three') or
    shogen_no in ('four', 'five', 'six') or ...
    · Share on TwitterShare on Facebook
  • 405508
    405508 Member Posts: 17
    adding 'OR' makes the query extremely slow..... :(
    · Share on TwitterShare on Facebook
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    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
    · Share on TwitterShare on Facebook
  • 116618
    116618 Member Posts: 90
    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.
    · Share on TwitterShare on Facebook
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    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
    · Share on TwitterShare on Facebook
  • Himanshu Kandpal
    Himanshu Kandpal Member Posts: 1,971 Silver Badge
    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
    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    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
    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.

    Good to see you read the threads you are answering.
    Sam Jose
    · Share on TwitterShare on Facebook
  • 416257
    416257 Member Posts: 43
    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. :)
    user12266275
    · Share on TwitterShare on Facebook
This discussion has been closed.