Home Page for the TeradataForum
 

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016