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 (2)
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 | June 12, 2007 6:22 AM
Posted on June 12, 2007 06:22
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 | June 27, 2007 2:36 PM
Posted on June 27, 2007 14:36