Sql IN clause 1000 item limit
We have some queries on our web application that required us to write sql statements which includes IN clause for thousands of items. We hit Oracle limit for 1000 items on those IN clauses. We resolved this by separating them with OR based on queries. Recently I learned that,
Any in statement like X IN (1,2,3) can be rewritten as (1,x) IN ((1,1), (1,2), (1,3)) and the 1000 element limit will no longer apply. I was told people have tested with an index on x and explain plan still reports that Oracle is using an access predicate and range scan.