RE: Large IN LIST in an OBIEE query
From: Martin Busik <martin.busik_at_busik.de>
Date: Mon, 31 Oct 2011 09:06:01 +0100
Message-ID: <000001cc97a3$ee90eb40$cbb2c1c0$_at_busik.de>
Hi,
I'd suggest to use a cardinality hint in that case, e.g.
Date: Mon, 31 Oct 2011 09:06:01 +0100
Message-ID: <000001cc97a3$ee90eb40$cbb2c1c0$_at_busik.de>
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-lReceived on Mon Oct 31 2011 - 03:06:01 CDT