Clever Geek Handbook

ORA-01795 - why is the maximum number of expressions limited to 1000

SO is full of working workarounds, but I'm curious about the historical reasons for the 1000 limit for the "maximum number of expressions" in the IN clause?

+3


source to share


2 answers


Perhaps this is due to the potential for the abuse of tons of values. And each value in it will be converted to an equivalent OR condition.

For example NAME IN ('JOHN', 'CHARLES'..

) will be converted toNAME = 'JOHN' OR NAME = 'CHARLES'

Thus, it can affect performance.



But please note that Oracle still supports

SELECT ID FROM EMP WHERE NAME IN (SELECT NAME FROM ATTENDEES)

In this case, the optimizer does not convert to multiple OR conditions, but does instead JOIN

.

+2


source


This restriction is not only for the IN list, but for any list of expressions . The documentation says:

A comma-separated list of expressions can contain up to 1000 expressions.

Your question is WHY the limit is 1000 . Why not 100 or 10,000 or a million? I guess this is due to the limit for the number of columns in the table, which is 1000. Perhaps this relationship is true in Oracle internally to make the expression list and columns match the DML expression.

But, for a good design, the 1000 limit itself is large. You will practically not reach the limit.

And, a quote from the famous AskTom site on a similar topic,



We'll spend more time parsing requests and then executing them!

Refresh My own thoughts

I think Oracle is quite old in DB technology that these constraints were made once and they never had to think about it again. The list of all expressions has a 1000 limit. And the robust design never allows users to go to Oracle for clarification. And Tom replies that parsing always makes me think that this whole limitation at that time in the 70s or 80s was more a matter of computation. C based algorithms may need some limitation, and Oracle had uo with 1000.

Update 2: from the app and its framework point of view

As a DBA, I've seen many developers run into performance issues, which are actually problems application framework

generating queries to fetch data from the database. The app provides users with the ability to add filters

which will eventually form the logic AND, OR

in the IN

query list . Internal Oracle extends it as query rewrite

a stage optimization

as logic OR

. And the request becomes huge, thus increasing the time to PARSE

. In most cases, it suppresses index usage

. So this is one of the cases where a request is generated with a huge IN list through the application framework.

+1


source






More articles:


All Articles