Is Predefined Constant....

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.

Comments:

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. :)

Posted by Robbert Michel on June 11, 2007 at 11:22 PM PDT #

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.

Posted by Alexander van Helm on June 27, 2007 at 07:36 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today