SQL & PL/SQL

Hi people,

Would someone be kind enough to help me with the following issue we are facing?

We have an application that consists of two components that both run an identical SQL query. The query is of the form:

SELECT column_a, column_b, column_c FROM table_x WHERE column_a = <bound_variable_1> and column_b = <bound_variable_2>

table_x is a large table holding approx 1,000,000 rows.

column_b can be either a 1 or 0 while column_a is pretty much unique although it does not have to be.

The spread of data in column_b is approx 95% values set to 0 while 5% are set to 1.

We have indexes on both column_a and column_b and ideally we'd like index on column_a to be used to run the query.

The issue we have is that component_1 runs the query with bound_variable_2 set to 1 while component_2 runs the query with bound_variable_2 set to 0. If component_1 runs the query first after stats have been regathered, the Oracle optimizer computes an execution plan making use of the index on column_b. However, the majority of queries are actually executed by component_2 which has bound_variable_2 set to 0 and so using index on column _b results in very poor execution times. Unfortunately we have no control over which component runs the SQL first.

What we would like to do is ensure the optimizer always chooses a 'good' index to use for the SQL provided to it. I understand we could use hints or stored outlines to try and force Oracle to always a specific execution plan. But I'd prefer not to do this as I don't want to force Oracle into anything it does not want to do. Instead I wondered if we could change the SQL queries slightly to try and confuse the optimizer so that it sees the queries run by the two components as different and so computes different execution plans. For example, if component_1 ran:


SELECT column_a, column_b, column_c FROM table_x WHERE column_a = <bound_variable_1> and column_b = <bound_variable_2>

and component_2 ran:

SELECT column_c, column_b, column_a FROM table_x WHERE column_a = <bound_variable_1> and column_b = <bound_variable_2>

would this be enough to make Oracle think the queries are different and so compute different execution plans? We are using Oracle 10.2.0.2.

We cannot remove the index on column_b as we use it for other queries in the application.

Any help / advice would be appreciated.

Thanks.