Re: MORE ABOUT: There is NOT value in using BOUND variables!!! ??? (WRONG -- this is ALOT of value)

As a generic approach, you could code in the USE_CONCAT hint. this would encourage
Oracle to take the path for a single equality 255 times, rather than switching to a scanning strategy.

As a more 'persuasive' hint, you could check the optimum access path for a single " = {literal}" query, and code hints, including the relevant index name, describing that path.

Personally I am not enthusiastic about including index names in hints unless there is a VERY solid change control system in place that ensures a) The index names will never change or
b) If an index name changes, every piece of code

        that references it can be found quickly and easily

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Deja User wrote in message <80hodt$58l$1_at_nnrp1.deja.com>...
>In article <EtsqOJOd3O=bBEnRTOoilbhnPD5l_at_4ax.com>,
> tkyte_at_us.oracle.com wrote:
>[snip]
>> 3) you didn't take into account that select .... where field
>>(:1,:2,:3,....)
>> would generate a different plan then where field in
>> (1,2,3,4,5,6,0,0,0,0,0,0,0....). The optimizer sees the dup zeros in
>>the second
>> on and turns it into the more simple (1,2,3,4,5,6,0). The
>>differences in run
>> times were due to differences in optimizer plans. A little tuning
>>and the bind
>> variable query will run faster.
>[snip]
>
>Thanks to Thomas Kyte for, AS ALWAYS, an excellent and very informative
>reply. Following are a couple of questions that come to mind after
>reading his post.
>
>As indicated in the quoted portion above, he is saying that "...a
>little tuning and the bind variable query will run faster...". What
>sort of tuning are we talking about? Any help would be greatly
>appreciated.
>
>In our case, we have a large number of queries which are run by upto 40
>concurrent users. All of these queries are EXACTLY the same except for
>the criteria specified in the "IN" list. So if we were to move to the
>BOUND variables to take full advantage of the savings, we have to use a
>long "IN" list (e.g. :1, :2, :3, .... :255) and a major portion of
>these values could be all zeros. As Thomas' own results confirm that
>such a query with a lot of dummy zeros runs slower than the query
>without zeros (0.68 vs 11.90 secs). How can I solve this problem then?
>
>Regards,
>Mike.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Fri Nov 12 1999 - 14:59:31 CST