RE: problems accessing an index when using a variable inlist

Presumably the index you are trying to use has a.col1 as a leading edge.
Ignore this suggestion if that is wrong. As a side note, it would sure read
cleaner if you used some other alias than "a" for dual. Also, I think there
is something wrong with a.tab1 = c.tab3 but you're probably meaning some
column references in typing up the example.

A hardwired in list, while offensive from a coding standpoint, does allow
the optimizer to exactly know the cardinality of t_varchar_tab at parse
time.

Probably the optimizer also knows or can quickly find out the number of rows
in a gtt with respect to your session. (Whether Oracle actually tracks the
+/- totals for gtts in a cached register for your session as would be an
optimal implementation is not the point.)

So I'm not surprised it gets it right in those cases when it is in fact
smart to use the index.

Three things to try:

1) rownum stopkey trick on the selection that populates t_varchar_tab with a
limit designed to never exclude any rows but to inform the optimizer the row
source can never exceed a certain size. Of course if you don't have a safe
limit in mind that documents this row source small enough to favor the
index, then you're out of luck, and of course you don't want to return a
wrong answer. If you're generating code and switching from previously
building a literal in list, then you can always make it safe since you know
the limit when you are parsing. But then remember to just use a few values
for the in list limit, so you don't defeat your likely purpose of reducing
the number of parsed versions of the sql. Some base value times the power of
ten that makes it safe in each actual case is as good an algorithm as any I
know. Meaning if you expect mostly it will always be under 20, then use 20,
200, 2000, etc. and it becomes moot when the cardinality is large enough to
cost out using the index anyway.
2) cardinality hint (if you're to a release that supports it and actually
uses it) on the select that populates t_varchar_tab. If you're sure from
knowing your data this is likely to always be a win to consider the in list
length as 1, the optimizer *should* get it right. At least it is easy to
test.
3) add a between using the min and max of the inlist result set. If your
function is expensive to process this is not a good choice, and the
optimizer will still have a tough time getting the cardinality right, but
this should be a tie breaker in some cases. You might also combine this
notion with projection of tab1 as a virtual table excluded from the joins.
That would work well if the result set from tab1 is small and then you can
pick up the tab2 and tab3 joins from indexes.

Good luck. Too bad there is not a bit in the optimizer to guess whether it
is a useful idea to sample or estimate the cardinality of row sources
returned from functions. Without being told the cardinality with a hint I
don't see how the optimizer could know whether to guess the return list is
long or short. I would probably be biased to guess the cardinality of things
like t_varhar_tab is small. I believe last time I checked they actually
guess based on the default block size of the database, which didn't make any
sense to me.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of ryan_gaffuri@xxxxxxxxxxx
Sent: Thursday, November 29, 2007 11:28 AM
To: oracle-l@xxxxxxxxxxxxx
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. 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




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

Other related posts: