SQL substitution feature in Oracle 10g

Question

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?

• Philᵀᴹ: Stored outlines
• Philᵀᴹ: Or SQL Profiles. See antognini.ch/papers/SQLProfiles_20060622.pdf and oracle-base.com/articles/misc/outlines.php
• FrustratedWithFormsDesigner: @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?
• Philᵀᴹ: 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
• user1822: Could also be a materialized view with query rewrite enabled.

Answer 1

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