| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Default column value of MAX +1 of column - possible in Oracle 9i?
I wouldn't say triggers are evil. In fact, there are many situations
where they're necessary. However, using one to populate a table's
primary key field with a sequence's next value is an option I don't
tend to choose, because:
Below are two test cases: one using a trigger, and the other not. Each case inserts 100,000 rows.
SQL> drop table t;
Table dropped.
SQL> create table t(t_pk number(11) not null primary key,name varchar2(100));
Table created.
SQL> create sequence t_seq start with 1;
Sequence created.
SQL> create or replace trigger t_bir before insert on t
2 for each row
3 begin
4 select t_seq.nextval into :new.t_pk from dual;
5 end;
6 /
Trigger created.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> alter session set timed_statistics=true;
Session altered.
SQL> begin
2 for i in 1..100000 loop
3 insert into t(name) values ('Name '||i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Now, for some of the tkprof output:
begin
for i in 1..100000 loop
insert into t(name) values ('Name '||i);
end loop;
end;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0
0
Execute 1 9.76 15.70 0 0 0
1
Fetch 0 0.00 0.00 0 0 0
0
total 2 9.76 15.71 0 0 0
1
INSERT INTO T(NAME)
VALUES
('Name '||:B1 )
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 100000 44.95 77.75 73 1078 310094
100000
Fetch 0 0.00 0.00 0 0 0
0
SELECT T_SEQ.NEXTVAL
FROM
DUAL
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 100000 7.50 10.16 0 0 0
0
Fetch 100000 10.12 12.67 0 0 5000
100000
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 5241 0.73 1.14 0 38 2
0
Execute 205409 58.00 95.09 73 6352 320299
105066
Fetch 100666 10.17 13.26 23 1821 5000
100688
Now, I'm going to do the same thing, except without the trigger: SQL> truncate table t;
Table truncated.
SQL> alter trigger t_bir disable;
Trigger altered.
SQL> alter session set events '10046 trace name context forever, level 12'
Session altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> alter session set timed_statistics=true;
Session altered.
SQL> begin
2 for i in 1..100000 loop
3 insert into t(t_pk,name) values (t_seq.nextval,'Name '||i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Portions of the tkprof output:
begin
for i in 1..100000 loop
insert into t(t_pk,name) values (t_seq.nextval,'Name '||i);
end loop;
end;
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 12 1
0
Execute 1 8.65 11.72 0 0 0
1
Fetch 0 0.00 0.00 0 0 0
0
total 2 8.65 11.73 0 12 1
1
INSERT INTO T(T_PK,NAME)
VALUES
(T_SEQ.NEXTVAL,'Name '||:B1 )
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0
0
Execute 100000 28.53 35.52 30 1063 314685
100000
Fetch 0 0.00 0.00 0 0 0
0
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 5150 0.68 0.73 0 42 0
0
Execute 105238 33.14 41.01 30 6346 324886
105066
Fetch 286 0.01 0.10 15 595 0
616
The performance improved by not using the trigger. The total elapsed time went down from 109.50 seconds to 41.84 Of course, environments are different, and the response times can and will vary. But, using the trigger will still require additional executes (in this case 100,000) , while inserts without a trigger will not.
So, I don't agree with Kenneth's statement that triggers are evil, but I do agree with his quote, "But don't use triggers unless you really have to"..
Regards,
Arun
Regards,
Arun
Received on Thu Mar 24 2005 - 23:25:27 CST
 |
 |