Archives of the TeradataForum

Message Posted: Fri, 07 Mar 2003 @ 09:24:03 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Sequence #

From:		Dieter N�th

Anomy Anom wrote:

table 1: create table seq (seqnbr integer); this is a one row one column table, to start with a value of 1 in seqnbr.

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(
tablename VARCHAR(30) NOT NULL,
nextValue INTEGER NOT NULL DEFAULT 0 )
UNIQUE PRIMARY INDEX ( tablename );

If you access it with a WHERE tablename = 'foobar' it's a row hash lock.

table 2: create table tmp1( col1 integer, col2 integer, col3 varchar(100)) unique primary index (col1); Transactional table where DMLs occur

table 3: create table tmp2(seqnbr integer, col1 integer, col2 integer, col3 varchar(100)) unique primary index (seqnbr); The log table in to which I am trying to serially insert the transactions with a sequence nbr

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(
ts timestamp(2) default current_timestamp(2),
Op char check(Op in ('I', 'U', 'D'),
...
);

No sequence table, you just have to add the "Op" in the trigger.

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference