Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2008
    Posts
    4

    Unanswered: Optimizing queries on the Fly using DBMS_ADVANCED_REWRITE

    Hi,

    Code:
    DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
       name                 VARCHAR2,
       source_stmt          VARCHAR2,
       destination_stmt     VARCHAR2,
       validate             BOOLEAN    := TRUE,
       mode                 VARCHAR2   := 'TEXT_MATCH');
    Code:
    begin
    	sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
    	'test_emp_query',
    	'select id, name from employees where id in (20, 21, 22)',
    	'select /*+ index(employees my_ix_01) */ id, name from employees where id in (20, 21, 22)',
    	false);
    end;
    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?


    Kind Regards,
    NI

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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.

  3. #3
    Join Date
    Apr 2008
    Posts
    4
    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_equivale nce (
    '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_equivale nce (
    '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_equivale nce (
    '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

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

  5. #5
    Join Date
    Apr 2008
    Posts
    4
    The same problem has been posted at the below link:

    Oracle.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •