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 FEBRUARY 22, 2022
Applies to:Oracle Exalytics Software - Version 126.96.36.199.0 and later
Business Intelligence Server Enterprise Edition - Version 188.8.131.52.0 to 184.108.40.206.150120 [Release 11g]
Information in this document applies to any platform.
When running OBIEE 220.127.116.11.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:
D1.c1 as c2
(select distinct T373951.Office_Dsc as c1
BISAMPLE.SAMP_OFFICES_D T373951 /* D30 Offices */
where ( T373951.Office_Dsc in
(select distinct T373951.Office_Dsc as c1 ==== Note INNER sub-query ====
BISAMPLE.SAMP_OFFICES_D T373951 /* D30 Offices */ ,
BISAMPLE.SAMP_TIME_DAY_D T373869 /* D01 Time Day Grain */ ,
BISAMPLE.SAMP_HEADCOUNT_F T374041 /* F50 Facts Headcount */
where ( T373869.Day_Key = T374041.Day_Key and T373951.Office_Key = T374041.Office_Key and TO_DATE('2001-01-01' , 'YYYY-MM-DD') < T373869.Calendar_Date ) ) )
order by c2 ) D1 where rownum <= 1000001
On Exalytics, the physical SQL will be split into two different queries: the first query evaluates the inner sub-query, and the second query uses the values retrieved from the inner sub-query as an IN clause:
select distinct T260451.Office_Dsc as c1
BISAMPLE.SAMP_OFFICES_D T260451 /* D30 Offices */ ,
BISAMPLE.SAMP_TIME_DAY_D T260369 /* D01 Time Day Grain */ ,
BISAMPLE.SAMP_HEADCOUNT_F T260541 /* F50 Facts Headcount */
where ( T260369.Day_Key = T260541.Day_Key and T260451.Office_Key = T260541.Office_Key and TO_DATE('2001-01-01' , 'YYYY-MM-DD') < T260369.Calendar_Date )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select distinct 0 as c1,
D1.c1 as c2,
D1.c2 as c3
(select distinct T260451.Office_Dsc as c1,
T260451.Department as c2
BISAMPLE.SAMP_OFFICES_D T260451 /* D30 Offices */
where ( T260451.Office_Dsc in ('Blue Bell Office', ==== Start of the long IN Clause ====
'Tellaro Office') )
order by c2, c3 ) D1 where rownum <= 1000001
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