Dynamic query vs static query index selection?

Posted by pedrorodriguez on 05-Apr-2016 12:39

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.


All Replies

Posted by George Potemkin on 05-Apr-2016 15:35

MATCHES function in WHERE clause should affect the selection of index - neither for dynamic query nor for static one.

Posted by gus on 05-Apr-2016 16:14

Also, since we are talking about a dataserver here, the index selections are done by Oracle.

Posted by pedrorodriguez on 05-Apr-2016 16:18

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.

This thread is closed