Can declare_rewrite_equivalence add hints?
I am using the following command for test purpose. I am trying to use optimizer_features_enable = 11.2.0.4 in my tests. My current optimizer_features_enable is 12.2.0.1
BEGIN
SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (
name => 'test_rewrite',
source_stmt => q'[SELECT * FROM foo.teste where id = 10]',
destination_stmt => q'[SELECT /*+ opt_param('optimizer_features_enable','11.2.0.4') */ * FROM foo.teste where id+0 = 10]',
validate => FALSE,
rewrite_mode => 'TEXT_MATCH');
END;
/
When I run SELECT/*+ opt_param('optimizer_features_enable','11.2.0.4') */ * FROM foo.testewhere id+0 = 0 it works fine (check the Outline Data informations)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3rk4g55nv4w11, child number 0
-------------------------------------
SELECT /*+ opt_param('optimizer_features_enable','11.2.0.4') */ * FROM
foo.teste where id+0 = 10
Plan hash value: 1912010946
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| TESTE |