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

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 - 13:05:37 CST