By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,563 Members | 1,058 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,563 IT Pros & Developers. It's quick & easy.

nextval for sequence in an insert statement vs nextval in an insert trigger

P: n/a
I'm trying to find if there is any performance diference between
explicitly using a sequence in the insert statement to generate values
for a column and doing this in an insert trigger.

I noticed that th eaccess plan for the 2 situations is quite
different. For the case where the trigger is in place, the optimizer
applies 2 extra residual predicates.
Can anybody explain where the differences come from? Also, for real
life situations, should there be any performance differences between
the 2 approaches? How about oracle?
thanks

create table t(a int, b int);
create sequence s start with 1;

use db2expln to explain
insert into t(nextval for s, 1)

Statement:

insert into t(a, b)values (nextval
for s, 1)
Section Code Page = 1208

Estimated Cost = 7.573070
Estimated Cardinality = 1.000000

Insert: Table Name = DB2ADMIN.T ID = 4,524

End of section
Optimizer Plan:

INSERT
( 2)
|
Table:
DB2ADMIN
T

TRIGGER VERSION:
CREATE TRIGGER TR
NO CASCADE
BEFORE INSERT
ON T
REFERENCING NEW AS newrow
FOR EACH ROW MODE DB2SQL
begin atomic
set newrow.a=nextval for s;
END@

explain
Statement:

insert into t(b)values ( 1)
Section Code Page = 1208

Estimated Cost = 7.574730
Estimated Cardinality = 1.000000

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Residual Predicate(s)
| #Predicates = 1
Insert: Table Name = DB2ADMIN.T ID = 4,524

End of section
Optimizer Plan:

INSERT
( 2)
/ \
FILTER Table:
( 3) DB2ADMIN
| T
FILTER
( 4)
|
TBSCAN
( 5)
|
TFunc:
SYSIBM
GENROW

Apr 19 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
fi*******@gmail.com wrote:
I'm trying to find if there is any performance diference between
explicitly using a sequence in the insert statement to generate values
for a column and doing this in an insert trigger.

I noticed that th eaccess plan for the 2 situations is quite
different. For the case where the trigger is in place, the optimizer
applies 2 extra residual predicates.
Can anybody explain where the differences come from? Also, for real
life situations, should there be any performance differences between
the 2 approaches? How about oracle?
thanks

create table t(a int, b int);
create sequence s start with 1;

use db2expln to explain
insert into t(nextval for s, 1)

Statement:

insert into t(a, b)values (nextval
for s, 1)
Section Code Page = 1208

Estimated Cost = 7.573070
Estimated Cardinality = 1.000000

Insert: Table Name = DB2ADMIN.T ID = 4,524

End of section
Optimizer Plan:

INSERT
( 2)
|
Table:
DB2ADMIN
T

TRIGGER VERSION:
CREATE TRIGGER TR
NO CASCADE
BEFORE INSERT
ON T
REFERENCING NEW AS newrow
FOR EACH ROW MODE DB2SQL
begin atomic
set newrow.a=nextval for s;
END@

explain
Statement:

insert into t(b)values ( 1)
Section Code Page = 1208

Estimated Cost = 7.574730
Estimated Cardinality = 1.000000

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Residual Predicate(s)
| #Predicates = 1
Insert: Table Name = DB2ADMIN.T ID = 4,524

End of section
Optimizer Plan:

INSERT
( 2)
/ \
FILTER Table:
( 3) DB2ADMIN
| T
FILTER
( 4)
|
TBSCAN
( 5)
|
TFunc:
SYSIBM
GENROW
This will be a negligible difference. Just for fun try this trigger
(without BEGIN.. END):
CREATE TRIGGER TR
NO CASCADE
BEFORE INSERT
ON T
REFERENCING NEW AS newrow
FOR EACH ROW MODE DB2SQL
set newrow.a=nextval for s@

I'm actually surprised that the raw INSERT shows so dense. Must be some
OLTP single row optimization.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 19 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.