On Sat, Mar 19, 2005 at 22:37:55 +0200,
Andrus Moor <eetasoft@online.ee> wrote:
> >> I have table containing different types of documents (type A, B and C).
> >>
> >> Each document type must have separate sequential ID starting at 1
> >>
> >> ID of first inserted record of type A must be set to 1
> >> ID of first inserted record of type B must be also set to 1
> >> ID of second record of type A must be set to 2
> >> etc.
> >
> > Sequences aren't designed for doing this. If you aren't doing lots of
> > updates, just lock the table and assign the next id as the current max id
> > of that type + 1.
>
> Bruno,
>
> thank you for reply.
>
> Document IDs are almost never updated. There are about 25 document updates
> per minute in peak hours (they create a copy from document and this creation
> also writes reference to original document). The database can became quite
> large (500000 documents).
>
> Which indexes should I create for getting max ID's fast (total 25 different
> document types) ?
You need to create an index on (category, sequence) so that order by
category, sequence will be fast.
> I have 120 concurrent users inserting documents. Mostly they are using 10
> different document types. Each type should have separate numbering. They
> insert 30 documents per minute in peak hours.
You really should think about this. What are you really using these
sequence numbers for.
> Locking the whole table causes delay for users wanting to insert other type
> of document.
> Is this reasonable? Is this delay noticeable in this case?
At 25 inserts per minute locking the table shouldn't be a problem.
You could also speed this up by using another table to store the highest
value for each category. If you do it that way you can use UPDATE to
do the update without locking the whole table. (In effect you only lock
by category type.)
> Is it possible to get a number concecutive IDs from sequence ?
Not if transactions sometimes rollback. You also have to worry about
clients requesting groups of sequence numbers at once and then not using
them. Deleting records will leave holes. You also need a sequence per
category type which will be a pain to maintain.
