RE: Large IN LIST in an OBIEE query
Date: Mon, 31 Oct 2011 09:06:01 +0100
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 (...) )