Hi all,
I am finding a rather curious behavior (maybe expected) in my application when selecting the index in a query for a dynamic vs static query. This is an application running with an Oracle dataserver.
The queries are very simple, with just one criteria specified using matches in an indexed field. Something like:
open query quer for each customer where name matches "*name*"
When I execute this code as a static query, performance seems reasonable and the correct index for the field "name" is used, but if a build a dynamic query with exactly same criteria, then primary index is selected and performance is terrible. Just by adding a by option in the query for name field makes the dynamic query perform correctly.
Is this an expected behavior? I would expect static and dynamic query acting same way in this example.
Cheers,
MATCHES function in WHERE clause should affect the selection of index - neither for dynamic query nor for static one.
Also, since we are talking about a dataserver here, the index selections are done by Oracle.
But what could be the reason behind such a massive difference in performance, especially being a dataserver I would expect the SQL generated would be the same.