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?

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