RE: problems accessing an index when using a variable inlist

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 29 Nov 2007 13:16:30 -0500
Message-ID: <03c401c832b3$f8043820$1100a8c0@rsiz.com>

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:

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.
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.
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ryan_gaffuri_at_comcast.net
Sent: Thursday, November 29, 2007 11:28 AM To: oracle-l_at_freelists.org
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 Received on Thu Nov 29 2007 - 12:16:30 CST