Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: table constraint
In this scenario you definitely don't want to use database triggers.
It would be best if all inserts were done by the same session, so you could bulk insert them. However, I suspect that it must be doing all inserts separately from several user sessions. In this case your performance requirement does not seem to be a problem.
See:
SQL> create table mytable
2 as
3 select 1 colour, 1 direction,
to_date('25062007120000','ddmmyyyyhh24miss') mydate from dual
4 /
Tabel is aangemaakt.
SQL> alter table mytable add constraint mytable_pk primary key
(colour,direction,mydate)
2 /
Tabel is gewijzigd.
SQL> create package mytable_api
2 as
3 procedure ins
4 ( p_colour in mytable.colour%type 5 , p_direction in mytable.direction%type 6 , p_mydate in mytable.mydate%type7 );
Package is aangemaakt.
SQL> create package body mytable_api
2 as
3 procedure lck
4 ( p_colour in mytable.colour%type
5 , p_direction in mytable.direction%type
6 )
7 is
8 cursor c_lock 9 is 10 select 'dummy' 11 from mytable 12 where colour = p_colour 13 and direction = p_direction 14 for update of mydate 15 ; 16 begin 17 open c_lock; 18 close c_lock;
22 ( p_colour in mytable.colour%type 23 , p_direction in mytable.direction%type 24 , p_mydate in mytable.mydate%type25 )
29 lck(p_colour,p_direction) 30 ; 31 select 'dummy' 32 into l_dummy 33 from mytable 34 where colour = p_colour 35 and direction = p_direction 36 and mydate between p_mydate - 3/24/60 and p_mydate + 3/24/60 37 ;
40 insert into mytable 41 ( colour 42 , direction 43 , mydate 44 ) 45 values 46 ( p_colour 47 , p_direction 48 , p_mydate 49 ); 50 when too_many_rows then 51 null;
Package-body is aangemaakt.
SQL> declare
2 l_start_time number := dbms_utility.get_time;
3 begin
4 for i in 1..200
5 loop
6 mytable_api.ins 7 ( trunc(dbms_random.value(0,2)) 8 , trunc(dbms_random.value(0,2)) 9 , sysdate - dbms_random.value(-.2,.2) 10 );
PL/SQL-procedure is geslaagd.
SQL> select count(*) from mytable
2 /
COUNT(*) -------------------------------------- 159
1 rij is geselecteerd.
Regards,
Rob.
Received on Tue Jun 26 2007 - 07:57:34 CDT