Re: [SQL] abusing an aggregate funct

On Sat, 14 Nov 1998, Marc Howard Zuckman wrote:

> On Sat, 14 Nov 1998, Thomas Good wrote:
>
> > Due to the sheer girth of the table the following query, called from
> > a data entry script,  takes a bit of time to finish:
> >
> > SELECT max(rec_num) FROM crtrd1;
> >
> > I use this to setup `rec_num + 1' for my query number (p_key)...any
> > way I can speed this up a bit?  I've run vacuum analyze but the
> > table size is doing me in...
> >
> Use a sequence to generate rec_num.  See man create_sequence.
>
> You can then use curval('sequence_name') to get the last
> sequence number generated, or if you are performing an insert:
>   insert into crtrd1 (p_key,otherdata) values (nextval('sequence_name',moredata);
>
> You could also create the table using nextval as the default
> value of p_key.  You may wish to create a unique_index on
> p_key, but nextval is guaranteed not to generate duplicates
> unless you allow it to rollover.
>
>
Unfortunately, solution using sequences is not so good unless you don't
suppose to use access to that table from some simultaneously running
sessions. The problem is that every sessions accessing this additional
sequence  with nextval()  will get its own pool of values for the
sequence, so early or later you'll get some gaps in records numbering
because of at least on of two reasons:
    a) usually the length of such a pool is set to limit that is
bigger then 1 and  obviously, it's impossible to check the number of
inserts (and thus the number of nestval`s) during every sessions.
    b) and in case of pool with 1-length some of your inserts may
fail, but nextavl would be called and next time you try insert you'll call
nextval again and will miss one or more values of sequence.


Also, you can't issue curval until you haven't call nextval at least once
during currunt session -- this is also source of gaps in values from
sequence assigned to records in the table. So, I think this method can
produce only approximation of real ammount of records:-(


Aleksey.