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 27, 2016

Applies to:

Business Intelligence Server Enterprise Edition - Version 11.1.1.7.0 to 11.1.1.7.150120 [Release 11g]
Oracle Exalytics Software - Version 1.0.0.3.0 and later
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:

  select D1.c1 as c1, D1.c2 as c2 from ( select distinct 0 as c1,
    D1.c1 as c2
 from
    (select distinct T373951.Office_Dsc as c1
         from
              BISAMPLE.SAMP_OFFICES_D T373951 /* D30 Offices */
         where  ( T373951.Office_Dsc in
           (select distinct T373951.Office_Dsc as c1                            ==== Note INNER sub-query ====
            from
              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 ) ) )
    ) D1
 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:

First Query:

select distinct T260451.Office_Dsc as c1
 from
    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 )



Second Query:

 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
 from
    (select distinct T260451.Office_Dsc as c1,
              T260451.Department as c2
         from
              BISAMPLE.SAMP_OFFICES_D T260451 /* D30 Offices */
         where  ( T260451.Office_Dsc in ('Blue Bell Office',                 ==== Start of the long IN Clause ====
'Casino Office',
'College Office',
'Copper Office',
'Eden Office',
'Eiffel Office',
'Figueroa Office',
'Foster Office',
'Glenn Office',
'Guadalupe Office',
'Madison Office',
'Merrimon Office',
'Mills Office',
'Montgomery Office',
'Morange Office',
'Perry Office',
'River Office',
'Sherman Office',
'Spring Office',
'Tellaro Office') )
    ) D1
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)

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms