RE: Large IN LIST in an OBIEE query

  • From: "Martin Busik" <martin.busik@xxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 31 Oct 2011 09:06:01 +0100

Hi,
I'd suggest to use a cardinality hint in that case, e.g.

key in (select /*+ cardinality(4) key_value from table(sys.odcinumberlist
(1,2,3,4)))

As you know your ID list, you also know its size, so generating a
cardinality hint is not a problem.

If you have more than 1000 values an alternative to an "or clause" is "union
all", e.g.

key in (select /* cardinality(1250) key_value
         from table(sys.odcinumberlist (...)
          union all
         select key_value
         from table(sys.odcinumberlist (...)     
         )

HTH,
Martin
        

--
http://www.freelists.org/webpage/oracle-l


Other related posts: