Re: problems accessing an index when using a variable inlist

I had already tried the cardinality hint, but forgot to mention it.

I actually hinted to use an index and oracle chose a different index (with an index full scan) than it does when i hard code the values.

I got it to work by using the string parse and insert the rows to a GTT, then querying the GTT.

• Original message ---------------------- From: "jaromir nemec" <jaromir_at_db-nemec.com>
  > Hi Ryan,
  >
  > one possible reason is that the cardinality of the IN-LIST table is wrongly
  > estimated.
  > You may verify it using the cardinality hint in the "select * from table",
  > setting the cardinality to the length of your IN-LIST.
  >
  > Regards,
  >
  > Jaromir
  > ----- Original Message -----
  > From: <ryan_gaffuri_at_comcast.net>
  > To: <oracle-l_at_freelists.org>
  > Sent: Thursday, November 29, 2007 5:27 PM
  > Subject: problems accessing an index when using a variable inlist
  >
  >
  > >I am using the standard variable inlist code from asktom. I find that when
  > >I use it, I cannot access an index.
  >
  > > WHERE a.col1 IN (select * from table( select cast(
  > > k_utility.func_varchar_in_list(col1) as
  > > t_varchar_tab ) from dual a))
  > >
  > >
  > >
  >
  >
  

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