X

Welcome to All Things Warehouse Builder

  • ETL
    June 11, 2007

Is Predefined Constant....

Jean-Pierre Dijcks
Master Product Manager

When using constants, take a look at this 'Is Predefined Constant' checkbox. This is the effect on the usage of a constant in a simple insert statement:



Unchecked (see the red line below):



      INSERT
      /*+ APPEND PARALLEL("TGT_ORDERS") */
      INTO
        "TGT_ORDERS"
        ("ORDER_NBR",
        "CUST_NBR",
        "BILL_TO_ADR",
        "ORDER_DT",
        "PAYMENT_TERMS",
        "STATUS")
        (SELECT
  "ORDERS_STG"."ORDER_NBR" "ORDER_NBR",
  "ORDERS_STG"."CUST_NBR" "CUST_NBR",
  "ORDERS_STG"."BILL_TO_ADR" "BILL_TO_ADR",
  "CST_MAP"."CONSTANT_0_OUTPUT1" "OUTPUT1",
  "ORDERS_STG"."PAYMENT_TERMS" "PAYMENT_TERMS",
  "ORDERS_STG"."STATUS" "STATUS"
FROM
  "ORDERS_STG"  "ORDERS_STG"
        )
      ;



Checked (see the blue line below):



INSERT
      /*+ APPEND PARALLEL("TGT_ORDERS") */
      INTO
        "TGT_ORDERS"
        ("ORDER_NBR",
        "CUST_NBR",
        "BILL_TO_ADR",
        "ORDER_DT",
        "PAYMENT_TERMS",
        "STATUS")
        (SELECT
  "ORDERS_STG"."ORDER_NBR" "ORDER_NBR",
  "ORDERS_STG"."CUST_NBR" "CUST_NBR",
  "ORDERS_STG"."BILL_TO_ADR" "BILL_TO_ADR",
  sysdate "OUTPUT1",
  "ORDERS_STG"."PAYMENT_TERMS" "PAYMENT_TERMS",
  "ORDERS_STG"."STATUS" "STATUS"
FROM
  "ORDERS_STG"  "ORDERS_STG"
        )
      ;



The change comes down to calling the SYSDATE insert directly rather than using a function to do so... Should make a performance improvement in large row counts.

Join the discussion

Comments ( 2 )
  • Robbert Michel Tuesday, June 12, 2007
    This is really usefull with strings, numbers and fixed dates.
    Sysdate is the exception to the rule though.
    Here you are replacing a variable with a function. :)
  • Alexander van Helm Wednesday, June 27, 2007
    I agrre that this could make a performance improvement, but it has some nasty side effects. In your example when sysdate comes from a plsql constant it will have the same sysdate for all rows, whereas you use it directly in the insert statement it can differ, especially when inserting loads of rows. And this is something you do not want to happen.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.