Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> problems accessing an index when using a variable inlist

problems accessing an index when using a variable inlist

From: <ryan_gaffuri_at_comcast.net>
Date: Thu, 29 Nov 2007 16:27:36 +0000
Message-Id: <112920071627.24019.474EE878000CEB5D00005DD32207020653079D9A00000E09A1020E979D@comcast.net>


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.

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 29 2007 - 10:27:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US