My Oracle Support Banner

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 1.0.0.3.0 and later
Business Intelligence Server Enterprise Edition - Version 11.1.1.7.0 to 11.1.1.7.150120 [Release 11g]
Information in this document applies to any platform.

Symptoms

When running OBIEE 11.1.1.7.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)

Cause

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
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.