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@xxxxxxxxxxxx>
> 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@xxxxxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> 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


Other related posts: