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.

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
Received on Mon Oct 31 2011 - 03:06:01 CDT

Original text of this message