Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Three or four years ago I read in an Oracle blog somewhere that a DBA had used for an emergency incident resolution an Oracle 10g feature of real time SQL substitution. Basically, he configured Oracle in way that every time that it received a certain query A it executed another query B instead. No application code change, no schema change, just a simple "execute query B instead of A" type of configuration.

Not that I am planning to use that feature (I can think of some undesirable consequences), but out of curiosity does it really exist? If yes, what is that feature called?

share|improve this question
    
Stored outlines –  Phil May 22 '12 at 20:02
    
1  
@Phil: I thought stored outlines were only for execution plans. Is it possible to use them to substitute actual queries the way the OP describes? –  FrustratedWithFormsDesigner May 22 '12 at 20:13
1  
Yes, you can change the SQL text using Outlines. I've done this before in 9i to modify a query to add a couple of hints. This shows how it is done: practicalappsdba.wordpress.com/2007/05/18/… - I don't see why you can't change the query as long as the input & output remains the same - outlines are evaluated & substituted at parse time –  Phil May 22 '12 at 21:04
1  
Could also be a materialized view with query rewrite enabled. –  a_horse_with_no_name May 22 '12 at 21:58
show 1 more comment

1 Answer 1

up vote 4 down vote accepted

That sounds like the DBMS_ADVANCED_REWRITE package. Tim Hall has an excellent walk-through of using that package to point an application's queries against a different table or view.

If you merely want to change the query plan but not point the query at a different table, you can use stored outlines or SQL profiles.

For example, I have tables FOO with 1 row and BAR with 2 rows

SQL> select * from foo;

      COL1
----------
         1

SQL> select * from bar;

      COL1
----------
        66
        77

I can declare a rewrite equivalence saying that queries against FOO should instead hit BAR

begin
  sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE(
    'Rewrite_Foo',
    'select col1 from foo',
    'select col1 from bar',
    false,
    'TEXT_MATCH' );
end;

Now, if I set query_rewrite_integrity to trusted, queries against FOO end up hitting a completely different table.

SQL> alter session set query_rewrite_integrity=trusted;

Session altered.

SQL> select * from foo;

      COL1
----------
        66
        77

That can create some rather interesting query plans where the object you're querying is nowhere to be found in the plan

SQL> select * from foo;

      COL1
----------
        66
        77


Execution Plan
----------------------------------------------------------
Plan hash value: 4224476444

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    26 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| BAR  |     2 |    26 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        584  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.