How to configure mapping to avoid OPAQUE_TRANSFORM hint on remote DB?
572253Feb 24 2011 — edited Feb 24 2011Hello,
we have a mapping on OWB11gR2 loading data from a Oracle DB version 9.2 which makes trouble under some special circumstances.
Mapping is designed as follows:
- one source table: on remote (dblink) database version 9.2, containing a timestamp
- two constants p_from and p_to with datattype DATE
- one filter with condition: source.timestamp > p_from AND source.timestamp <= p_to
- one target table on database 11gR2
Mapping statement is generated as:
INSERT /*+ APPEND */
INTO target.targettable
(...)
SELECT *
FROM source.sourcetable s
WHERE s.timestamp > p_from AND timestamp <= p_to
When p_from != p_to, then everything is fine. Now we have the special case where p_from = p_to. The statement then is running very long on the remote database. The execution plan there delivers following statement:
SELECT /*+ OPAQUE_TRANSFORM */
*
FROM source.sourcetable
The where-clause is missing completely, so the remote database is doing a full table scan. The source table has 90Mio rows, so it takes very long – absolutely unnecessary!
When executing the statement manually, the SELECT is executed on remote DB with the where-clause and without the opaque-hint.
How can the mapping be configured to avoid this OPAQUE_TRANSFORM hint or avoid loosing the where clause in such a case?
Thank you!
Kind regards,
Marco
Edited by: Marco76 on Feb 24, 2011 12:55 PM