Thread: ORA-01795, limit on in clause

I would like to share that Oracle 10g has a limit on in clause, therefore you can only have maximum of 1000 expressions inside a in clause.
Example.
You might come accross such situtaion sometime when passing employee Ids as string inside the in clause.

Select name from temployee where Id in ('1','2','3',.....'10000')

Cause: The performance of this query will be very slow and if the number of employee ids limit reached, the oracle server will throw an error message.

Suggestions:

1) Avoid using in clause, use exists clause which is better in performance.

2) Use sub query instead of using in clause and passing id's as string

Example;

Select name from temployee where Id in (select Id from tUsers)

This will work perfectly and performance of the query will be better than the IN and EXISTS clause.

Any suggestions, please comment.