Home » RDBMS Server » Performance Tuning » Queries on the fly using DBMS_ADVANCED_REWRITE
| Queries on the fly using DBMS_ADVANCED_REWRITE [message #382911] |
Mon, 26 January 2009 04:36  |
nawazijaz
Messages: 3
Registered: August 2008
Location: Lahore
|
Junior Member |
|
|
Hi,
can some body explain it to me what is 'VALIDATE_REWRITE_EQUIVALENCE' and how to use it to validate your queries?
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 in the IN CLAUSE get changed then oracle treats 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. So i need to write my own validation (my understanding) to incorporate above three queries as the same QUERY.
I want to know how "dbms_advanced_rewrite.validate_rewrite_equivalence" can help me out in this regard?
"dbms_advanced_rewrite.validate_rewrite_equivalence"
Kind Regards,
NI
|
|
|
|
| Re: Queries on the fly using DBMS_ADVANCED_REWRITE [message #382986 is a reply to message #382911] |
Mon, 26 January 2009 22:49  |
nawazijaz
Messages: 3
Registered: August 2008
Location: Lahore
|
Junior Member |
|
|
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.
--- Block 01
select id, ename from Employees where id in (1, 2, 3, 4); -- 4 parameters in IN CLAUSE
select id, ename from Employees where id in (5, 6, 7, 8); -- 4 parameters in IN CLAUSE
select id, ename from Employees where id in (9, 10, 11, 12); -- 4 parameters in IN CLAUSE
--- Block 02
select id, ename from Employees where id in (1, 2, 3, 4, 5, 6); // 6 parameters in IN CLAUSE
select id, ename from Employees where id in (7, 8, 9, 10, 11, 12); // 6 parameters in IN CLAUSE
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;
--------------------------------------------------------------------------------------------------
begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
'test_emp_query_02',
'select id, name from employees where id in (1, 2, 3, 4)',
'select /*+ index(employees my_ix_01) */ id, name from employees where id in (?, ?, ?, ?)', -- FOUR PARAMETES IN CLAUSE
false);
end;
--------------------------------------------------------------------------------------------------
begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
'test_emp_query_02',
'select id, name from employees where id in (?, ?, ?, ?, ?)',
'select /*+ index(employees my_ix_01) */ id, name from employees where id in (?, ?, ?, ?, ?)', -- FIVE PARAMETES IN CLAUSE
false);
end;
--------------------------------------------------------------------------------------------------
begin
sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
'test_emp_query_03',
'select id, name from employees where id in (?, ?, ?, ?, ?, ?)',
'select /*+ index(employees my_ix_01) */ id, name from employees where id in (?, ?, ?, ?, ?, ?)', -- SIX PARAMETES IN CLAUSE
false);
end;
--------------------------------------------------------------------------------------------------
There must be a structured way to achieve above problem by writing one block only??
Kind Regards,
Nawaz Ijaz
|
|
|
|
|
|
|
|
| Re: Queries on the fly using DBMS_ADVANCED_REWRITE [message #403753 is a reply to message #403679] |
Mon, 18 May 2009 08:56 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The slight downside to that approach is that the performance of your queries takes a slight turn for the worse.
Here's a little test case:create table test_201 (col_1 number, col_2 number);
insert into test_201 select level, mod(level,200) from dual connect by level <= 100000;
create index test_201_idx on test_201(col_2);
create or replace type test_201_num_tab as table of number;
/
create or replace function f_test_201 (p_num in number) return test_201_num_tab as
t_return test_201_num_tab := test_201_num_tab();
begin
for i in 1..p_num loop
t_return .extend();
t_return(t_return.last) := i+10;
end loop;
return t_return;
end;
/
declare
v_tim pls_integer;
v_iter pls_integer :=1000;
v_Val pls_integer;
begin
v_tim := dbms_utility.get_time;
for i in 1..v_iter loop
select /*+ test2 */ count(col_1)
into v_val
from test_201
where col_2 in (10,20,30,40);
end loop;
dbms_output.put_line('Test 1 '||to_char(dbms_utility.get_time - v_tim));
v_tim := dbms_utility.get_time;
for i in 1..v_iter loop
select /*+ test2 */ count(col_1)
into v_val
from test_201
where col_2 in (select column_value from table(f_test_201(4)));
end loop;
dbms_output.put_line('Test 2 '||to_char(dbms_utility.get_time - v_tim));
end;
/
which you can run.
When I run it, the user defined type approach performs roughly 4 times worse than the straight SQL version.
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 00:07:26 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
