how to change SQL execution plan 11g

DBMS_ADVANCED_REWRITE can be used in cases where we cannot change the code, but can still influence and change the way the optimizer executes the same SQL statement.

begin

sys.dbms_advanced_rewrite.declare_rewrite_equivalence(

name => 'Use_Myobjects_Index',

source_stmt =>'select dummy from dual where dummy=''AAA''',

destination_stmt => 'select /*+ INDEX (dual the_index) */ dummy

from dual where dummy=''AAA''' ,

validate => false, rewrite_mode => 'text_match');

end;

/

Note: ORA-30394: source statement identical to the destination statement – fix with adding i.e. „and sysdate=sysdate“ to sql statement text.