If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

In order to optimize my queries i want to add some index hints on the fly. The parameters passed in IN CLAUSE are my concerns as whenever the number of parameters passed in the IN CLAUSE get changed then oracle treat the query as a different query such as;

select id, name from employees where id in (20, 21, 22); -- 3 parameters in IN CLAUSE
select id, name from employees where id in (20, 21, 22, 23); -- 4 parameters in IN CLAUSE
select id, name from employees where id in (20, 21, 22, 23, 24); -- 5 parameters in IN CLAUSE

all the above three queries are treated as different queries by ORACLE due to the above mentioned reason. So i need to write my own validation (my understanding) to incorporate above three queries as the same QUERY.

i want to check the query equivalence for 'source_stmt', 'destination_stmt' from the start of the query till the beginning of the WHERE CLAUSE. such as below;

Code:

select id, name from employees

and then add the remaining part of my 'source_stmt' query at the end of my Optimized query 'destination_stmt'. Such as below;

Code:

where id in (20, 21, 22)

OR

Code:

where id in (20, 21, 22, 23);

OR

Code:

where id in (20, 21, 22, 23, 24);

Is above thing possible? Also I want to know how "dbms_advanced_rewrite.validate_rewrite_equivalenc e" can help me out in this regard or any other way/suggestion to solve this problem?

>So i need to write my own validation
Why, when Oracle already "validates" SQL for you?

What problem are you really trying to solve?

You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.

Well Oracle CBO will always pick up the right index and execution plan if and only if the stats are not staled. The query which i mentioned as an example is just a test query and indeed the actual query would be far complex than that

Actually we have tested our queries by specifying the Index Hints but Client would not be agreed upon that if we ask him to deliver a new shipment/patch with all the Optimized queries specified in the Code.

So that is the reason why i am looking for such things like 'DBMS_ADVANCE_REWRITE'.

As i mentioned in the employees table example. just because of the different parameters in the 'IN CLAUSE' the same query is being treated as a new query and i can see the different Sql IDs for the same query in my AWR reports.

we can have any number of parametes in IN CLAUSE in production. So i want to know how can i handle this problem when writing queries on the fly using 'DBMS_ADVANCE_REWRITE'. So i dont want to write 1000 alternate optmized queries just because of the IN CLAUSE. such as below;

You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.