Archives of the TeradataForum
Message Posted: Fri, 07 Mar 2003 @ 09:24:03 GMT
Anomy Anom wrote:
This i bad, because you need a table lock for every access and you modify the primary index with every update.
I usually use 1 table for *all* sequences:
CREATE SET TABLE Sequences(
If you access it with a WHERE tablename = 'foobar' it's a row hash lock.
Are you shure about the UPI? If you touch more than one row in a statement, you'll have to create unique values, although there's no order during the insert|update|delete. And it's much more complicated ;-)
And you need a column with info why the row was inserted:
Op char check(Op in ('I', 'U', 'D') replace trigger tmp1_ins_trig enabled after insert on Tmp1 order 1 referencing new_table as newtab for each statement ( update ids set nextvalue = nextvalue + 1 where tablename = 'tmp1'; /*** If you do the Select first, there may be deadlocks for single row INSERTs ***/ Insert into tmp2 ( SeqNbr ,Op ,Col1 ,Col2 ,Col3 ) Select dt.nextvalue ,'I' ,Col1 ,Col2 ,Col3 from newtab, (select nextvalue from ids where tablename = 'tmp1') dt; /*** The same Seq# for *all* rows ***/ );
This is rather fast for single row inserts, but it doesn't scale, because the write lock on IDs is released _after_ the Commit.
So why don't you just use a timestamp instead of a sequence number?
create table tmp2(
No sequence table, you just have to add the "Op" in the trigger.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|