Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: Problems with Long IN () clauses?

    I recently ran across something that said that Oracle had difficulties with long IN (x,y,z...) types of clauses (or maybe it was long OR clauses). Does anyone have any insight into this? What is considered long?

    Thank you.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    A long datatype is unsupported for most types of manipulation or testing and should never be used for new development. Your example of a long variable using an inclause would never work. Also comands like substr, instr and most other things don't work.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    May 2004
    Posts
    184
    Bill,

    Sorry. I wasn't necessarily referring to variables, but select statements. I.e., select * from table_name where employeeid in (1,2,3,4).

    I had heard that Oracle did not do well with these if they were too long, but there was no information about how long 'too long' was.

    Thanks.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Ahhh. the in clause will only support about 256 hard coded variables in the select (if I remember correctly), but an unlimited number using a sub select. For example,

    where junk in (select test_string from mytable where flag=1)

    is legal even if the sub-select returns a million rows.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    May 2004
    Posts
    184
    Bill,

    Any known peformance issues using the IN (1,2,3) types of clauses? Obviously 256 is more than I'd like, but since the application can dynamically build my IN clause, I need to know it's limitations.

    Thank you.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If I'm not wrong, IN clause limit is 1000 elements (not 256).

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    If your application is building the IN clause dynamically, I would be best concerned about how to bind this in clause dynamically everytime I run a query that involves it. If you are just using values (not querys or whatsoever) IN should be just fine.. but first, make sure you BIND this value (i.e. don't hard code it on the application and bind the sql by altogether).

  8. #8
    Join Date
    May 2004
    Posts
    184
    I assume by bind you are referring to bind variables in PL/SQL. If that is the case, then I'm not sure that it would apply, but I'm not an Oracle expert hence my question, unless I misunderstood your suggestion.

    The application is a web based app that builds the complete sql SELECT string via server side javascript, then passes the sql statement through SQL Net and the IN clause would contain only values by the time it reached Oracle.

    Thank you.

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Problem is, you're building the SQL dynamically, and you will end up (depending on how much this query varies, for example, the predicates you put on it) with probable 256 different plans given the in parameters you passed in (assumed by your answer that will be lesser than 256).

  10. #10
    Join Date
    May 2004
    Posts
    184
    JMartinez,

    Okay. Thank you. What can I do to improve this? I cannot change the way it is done in the JavaScript, is there something I can do on the Oracle side to improve this?

    Thanks.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think I would change it to insert the data to a temp table and then change the IN to a join to that temp table
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    May 2004
    Posts
    184
    Brett,

    I'm just trying to clarify and understand so please don't take this wrong.

    What I think you said was that I should INSERT 256 rows (if it really had that many items) into a temporary table and then use a subselect on the temp table like
    select * from table_name where exists (select id from temp_table where table_name.id=temp_table.id).
    I would think the overhead of 256 inserts would be more intensive than just doing a
    select * from table_name where id in (1,2,...256)
    .

    Is this what you are saying or have I misunderstood you?

    Thank you.

  13. #13
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Why do you think the overhead of the inserts would be greater, it all depends on your data. If you have a 100 million rows for which you have to execute the IN clause then the overhead of the inserts would be miniscule. And the advantage of doing a join could well lead to significant speed up.

    Try it in your TEST & PROD environment and see what effect it has, you may be pleasantly surprised. In one of our apps the technique Brett mentioned worked very well and lead to considerable speed ups of more than 10 fold.

    The other question is how does the user select 256+ entities? or is their a much smaller set of possible IN Clauses in which case you could keep the possible permutations in a permanent table.

    Alan

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rtnetworks
    Brett,

    I'm just trying to clarify and understand so please don't take this wrong.
    What's to take wrong? Hey you can lead a horse to water.....

    http://weblogs.sqlteam.com/brettk/ar...5/05/1312.aspx

    And if your worried about the overhead for inserts, then have the app create a flatfile, and load it instead on inserts
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    May 2004
    Posts
    184
    Alan,

    Thank you. Wow, that is a big increase in performance.

    Alan, you are right, it all depends upon the scale and number of rows. We are only talking about 200,000 rows in the search, so it's not that big.

    The user has a HTML page with a mult-select list of approximately 400 items in it. This list is populated from a table in Oracle. The user can choose any or all of the items in the list and then via Server Side JavaScript, we write a dynamic SQL statement that is passed to Oracle.

    Thanks,

    Robert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •