What is OPAQUE_TRANSFORM Hint and how to Control it (Doc ID 780503.1)

Last updated on FEBRUARY 06, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 and later
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.2.0.3



Goal

What is OPAQUE_TRANSFORM usage :

The OPAQUE_TRANSFORM hint is to help with the transformation of datatype when certain type of operations are done within the database.  For example object types .
It is also used for a insert-as-remote-select operation on a remote database 

 

Example : insert into emp (select * from emp@rep102b)   ;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.50          0          0          0           0
Execute      1      0.00       0.51          0          1         44          12
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       1.01          0          1         44          12

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 57  (SCOTT)

Rows     Row Source Operation
-------  ---------------------------------------------------
     12  REMOTE  EMP (cr=0 pr=0 pw=0 time=508808 us)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
     12   REMOTE OF 'EMP' (REMOTE) [REP102B]
             SELECT /*+ OPAQUE_TRANSFORM */ "EMPNO","ENAME","JOB","MGR",
               "HIREDATE","SAL","COMM","DEPTNO" FROM "EMP" "EMP"


Note :  This hint should not interfere with the query optimizer plan.

Solution

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms