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?
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.
You do not need a parachute to skydive. You only need a parachute to skydive twice.
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).
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.
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).
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.
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.