Exalytics: OBIEE Server Executes IN Subquery and Converts It into Values, Then Error: " [nQSError: 42029] Subquery contains too many values for the IN predicate"
(Doc ID 1996805.1)
Last updated on SEPTEMBER 30, 2020
Applies to:Oracle Exalytics Software - Version 22.214.171.124.0 and later
Business Intelligence Server Enterprise Edition - Version 126.96.36.199.0 to 188.8.131.52.150120 [Release 11g]
Information in this document applies to any platform.
When running OBIEE 184.108.40.206.1 queries on an Exalytics server, the BI server evaluates the sub-query first and then passes the values (via IN list) to main query. This happens for all types of Oracle Databases (Oracle 10gR1, Oracle 10gR2,Oracle 11g), for all Subject Areas.
For example, assume the following SQL is issued on an OBIEE server on Exalytics:
Whereas, on a standalone, non-Exalytics, OBIEE server, the same query generates a single SQL and does not create the large IN clause. With this behavior on Exalytics, and when cache is turned on, large sub-queries can lead to an error:
[nQSError: 42029] Subquery contains too many values for the IN predicate. (HY000)
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document