Using DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR to remove one hint from a SELECT

2878566

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.

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

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.

mtefft

You could rename the index....

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