problems accessing an index when using a variable inlist
- From: ryan_gaffuri@xxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 29 Nov 2007 16:27:36 +0000
I am using the standard variable inlist code from asktom. I find that when I
use it, I cannot access an index. The query performs fine when the inlist is
hard coded or when I first insert to a global temp table and select from the
global temp table.
I checked to make sure the data types were a match. My my type is a
varchar2(4000) and the underlying column is a varchar2. Here is my where
clause.
FROM tab1 a LEFT OUTER JOIN tab2 b ON (a.col1 =b.col2)
LEFT OUTER JOIN tab3 c ON (a.tab1 = c.tab3)
WHERE a.col1 IN (select * from table( select cast(
k_utility.func_varchar_in_list(col1) as
t_varchar_tab ) from dual a))
AND a.datefield> (SYSDATE - 10);
as I said Oracle uses the correct index when I hard code the values or if i use
the k_utility package and insert the records to a GTT and then query the GTT.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
