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 Go to next message
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 Go to previous messageGo to next message
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 Smile

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 #382991 is a reply to message #382911] Mon, 26 January 2009 23:04 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.


>can some body explain it to me what is 'VALIDATE_REWRITE_EQUIVALENCE' and how to use it to validate your queries?

Why do you think you need to "validate your queries"?

If you do not understand above or how to (ab)use it,
why do you think it is your solution?

What business problem are you trying to solve?
How would an independent observer conclude when the problem has been solved?

Re: Queries on the fly using DBMS_ADVANCED_REWRITE [message #403679 is a reply to message #382991] Mon, 18 May 2009 00:58 Go to previous messageGo to next message
delisyoso
Messages: 2
Registered: May 2009
Junior Member
Create an overloaded procedure that returns a table type from the parameters received if you don't want to insert first into a temp/working table.

select id, ename from Employees
where id in (select column_value from table(pack.ret_table(:1, :2, :3,...)));

Re: Queries on the fly using DBMS_ADVANCED_REWRITE [message #403753 is a reply to message #403679] Mon, 18 May 2009 08:56 Go to previous message
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.
Previous Topic: Index is not working properly in my query ?
Next Topic: Increasing number of Processes
Goto Forum:
  


Current Time: Thu Dec 08 06:36:03 CST 2016

Total time taken to generate the page: 0.17177 seconds