SQL Performance (MOSC)

MOSC Banner

Can declare_rewrite_equivalence add hints?

edited Aug 5, 2023 7:08PM in SQL Performance (MOSC) 6 commentsAnswered

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 |   

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center