Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Add user-specified hints to the SELECT clause of 'copy to staging' and 'copy to destination' insert statements in Data Archive for Oracle
Solution

The feature of adding user-specified hints to SELECT clause of copy to staging and copy to destination insert statements (only for archive, archive&purge) is given in Data Archive for Oracle 5.3.7 (as part of enhancement request CR 283819).

An informatica hint "INFA_SELECT_HINTS"  needs to be entered as a leading comment in the "Insert Statement" field of the entity table in EDM.

Example

Without Informatica hint
  • For OE_ORDERS table the value in "Insert Statement" is:
    a.order_date in (select /*+ FULL(X) PARALLEL(X, #) */ x.order_date from <INTERIM_TABLE> x where x.purgeable_flag = 'Y')
  • This will be converted into insert statement as:
    INSERT /*+ APPEND PARALLEL (Z,2) */ INTO <HISTORY_SCHEMA>.ORDERS" Z (
    <COLUMNS>
    )
    SELECT <COLUMNS> FROM <SOURCE_SCHEMA>."ORDERS"@<DBLINK> A
    WHERE A.ORDER_DATE IN (SELECT /*+ FULL(X) PARALLEL(X , 2) */ X.ORDER_DATE FROM <STAGING_SCHEMA>.<INTERIM_TABLE>@<DBLINK> X WHERE X.PURGEABLE_FLAG = 'Y')
With Informatica hint
  • For OE_ORDERS table the value in "Insert Statement" is:
    /* INFA_SELECT_HINTS = ORDERED USE_HASH (X A) PARALLEL(A, #) */ a.order_date in (select /*+ FULL(X) PARALLEL(X, #) */ x.order_date from <INTERIM_TABLE> x where x.purgeable_flag = 'Y')
  • This will be converted into insert statement as:
    INSERT /*+ APPEND PARALLEL (Z,2) */ INTO <HISTORY_SCHEMA>.ORDERS" Z (
    <COLUMNS>
    )
    SELECT /*+ ORDERED USE_HASH (X A) PARALLEL(A, 2) */ <COLUMNS> FROM <SOURCE_SCHEMA>."ORDERS"@<DBLINK> A
    WHERE A.ORDER_DATE IN (SELECT /*+ FULL(X) PARALLEL(X , 2) */ X.ORDER_DATE FROM <STAGING_SCHEMA>.<INTERIM_TABLE>@<DBLINK> X WHERE X.PURGEABLE_FLAG = 'Y')
More Information
Reference
Applies To
Product: Data Archive
Problem Type:
User Type:
Project Phase:
Product Version: Data Archive for Oracle 5.3.6
Database: Oracle
Operating System:
Other Software:
Attachments
Last Modified Date:9/25/2012 7:30 AMID:140889
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)