469,611 Members | 1,959 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,611 developers. It's quick & easy.

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

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
1 20080
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.

Similar topics

9 posts views Thread by Martin | last post: by
10 posts views Thread by Anthony Best | last post: by
7 posts views Thread by peter.morin | last post: by
7 posts views Thread by Rahul B | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.