6 Replies Latest reply on Apr 28, 2014 10:35 AM by Steven Hawkins

    Combining multiple 'in' criteria can prevent pushing criteria to Oracle

    Mike Higgins Newbie

      I am sorry of this is already covered somewhere, but my searching did not find anything on this subject.

       

      I am evaluating Teiid (version 8.3 currently, I expect to change this soon).  We are issuing queries (in this case) against a single Oracle database.

       

      I have a SQL query that needs to filter a table against a list of 2000 constant values.  Since Oracle only allows 1000 items in an 'in' criteria, oring multiple 'in' criteria together is a workaround for this limitation.  So, if I have a query that has 2000 items that I need to filter, the SQL will contain two 'in' operations on the same field, each containing 1000 of the items, connected with 'or'.

       

      Teeid combines these two operations into a single criteria, then cannot push this criteria to Oracle because it exceeds the 1000 item limit, so it must perform the filtering itself.  Unfortunately in my case, this makes a usually sub-second query now take 15-20 seconds.

       

      A similar situation is handled for dependent tables by Teiid, by issuing multiple in operations combined with 'or'.

       

      My question is:  is there anything I could do to get my large list to be split using ors and pushed to Oracle for execution?