Recent Pos

Overcome Oracle SQL IN() clause limitation


oracle 1000 limit in clause solution
There is many ways we can overcome the limitation on IN() clause imposed by Oracle on its SQL queries.

1. We can break the entire list into smaller part and then append the smaller lists using OR clause into the original query.

2. We can create a GLOBAL TEMPORARY table and insert the original list into the DB at the time of execution and then use SELECT statement inside the IN() clause at the same session.

3. We can write a function which internally maintain table type object and holds the list temporarily, that will be used as a temporary table inside the IN() clause, for example:

⇒ We can’t use more than 1000 entity inside IN() clause but can give a SELECT query instead of the list.

4. Similar to GLOBAL TEMPORARY table, create 1 permanent table and maintain all the entities permanently and fetch required ID by projection.

All the above solutions can solve the limitation problem, but these solutions will hit performance of the system mostly in the solution 1. The remaining solutions also hit system performance slightly.
Instead of making a situation to face this kind problem, we should design our system such way that these kind of problem never come in software life-cycle.

No Comments

Post a Comment