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 |