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.
