Forum Stats

  • 3,824,946 Users
  • 2,260,442 Discussions
  • 7,896,356 Comments

Discussions

Using DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR to remove one hint from a SELECT

2878566
2878566 Member Posts: 1
edited Oct 24, 2019 5:33AM in Database Ideas - Ideas

Hello,

It tried it but it is impossible with the packages DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR to remove one hint fom a SELECT.

I showed my test case here : https://asktom.oracle.com/pls/apex/asktom.search?tag=remove-a-hint-with-dbms-advanced-rewrite-and-dbms-sql-translator-fa…

Chris said that I can use "alter session set optimizer_ignore_hints = true;" : it works but the problem is that it will erase ALL the hints from a SELECT.

OK, I know, hint is bad but sometimes developers do what they want.

So, do you think it is a good idea to ask Oracle to enhance these both packages?

Have a nice day,

David

Sven W.
1 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    I agree that there are cases where a DBA wants to say ignore that hint but keep the other.

    I'm a bit torn apart. On the one hand Chris is right, you are abusing a system ment for a specific scenario. But I do that too from time to time.

    On the other hand, I think dbms_sql_translater should be able to do any transformation including the one you suggested.

    I'm voting up just because of that, but it is only a halfhearted vote which might change after further considerations..

    Also think about this:

    If a vendor has a suboptimal hint inside his code, then that is a bug and the vendor has to fix it.

    Essentially you are looking for a temporary bug workaround. Now in that case there are probably several other workarounds possible, for example renaming that index.

  • Rainer Stenzel
    Rainer Stenzel Member Posts: 63 Bronze Badge

    Likewise I failed to achieve this using DBMS_SQLDIAG.CREATE_SQL_PATCH (resp. DBMS_SQLDIAG_INTERNAL before 12.2).

    Maybe another solution would be to add the option of replacing hints instead of adding them only in CREATE_SQL_PATCH.

    Sven W.
  • mtefft
    mtefft Member Posts: 844 Gold Badge

    You could rename the index....

    of course, any other query that has hints looking for that index would be impacted...