>> 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) ?
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.
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?
Is it possible to get a number concecutive IDs from sequence ?
Andrus.