SQL substitution feature in Oracle 10g
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&gt; select * from foo;

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

SQL&gt; 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&gt; alter session set query_rewrite_integrity=trusted;

Session altered.

SQL&gt; 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&gt; 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