Oracle OPAQUE_TRANSFORM undocumented hint

OPAQUE_TRANSFOR comes as undocumented hint you can usually see in a data warehouse environment. Oracle rewrite query INSERT INTO..SELECT or CREATE TABLE..AS every time you refer to a remote instance using DBLink. OPAQUE_TRANSFORM appears every time query is distributed and involves the SELECT part from remote database.

SQL> insert into t1 select * from t1@lab11r2 ;



1099999 rows created.



Execution Plan

----------------------------------------------------------

Plan hash value: 1788691278



-----------------------------------------------------------------------------------------

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|

-----------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT |      |  1074K|   409M|  2291   (1)| 00:00:28 |        |      |

|   1 |  REMOTE          | T1   |  1074K|   409M|  2291   (1)| 00:00:28 | LAB11~ | R->S |

-----------------------------------------------------------------------------------------



Remote SQL Information (identified by operation id):

----------------------------------------------------



1 - SELECT /*+ OPAQUE_TRANSFORM */ "ID","DATA1","DATA2","DATA5" FROM "T1"

"T1" (accessing 'LAB11R2' )

Metalink reports for the search string "OPAQUE_TRANSFORM" only one result as note id 780503.1 and of course two bugs: 5565178 and 7417255. Metalink says exactly:

The OPAQUE_TRANSFORM hint is to help with the transformation of datatype when certain type of operations are done within the database.

Don Burleson refers here to two theories: first suppose that OPAQUE_TRANSFORM is used by Oracle to transform query in an 'earlier dialect' to bypass the possibily to involve different Oracle version in a distributed query. The other theory refers to an error ORA-22997 that may outcome during conversion, in different Oracle version in a distributed query, converting abstract data types to LOB columns...

Metalink describes a method to disable OPAQUE_TRANSFORM hint. This way is to set the trace for event 22825 at level 1. Doing this will disable the automatic use of OPAQUE_TRANSFORM. Event 22825 is related to import/export functions and its actions is to enable logical transformation of opaque types.

Maybe this is a smoking undocumented hint... but why?