Re: to add table with primary key

On Feb 28, 9:36 am, "shakespeare" <what..._at_xs4all.nl> wrote:
> "codadilupo" <yossaria..._at_operamail.com> schreef in berichtnews:47c6b8a3$0$12838$5fc30a8_at_news.tiscali.it...
>
> > shakespeare wrote:
>
> >> I often wonder why people use sequences and want to keep control of the
> >> values.
>
> > Me too. For example, some of my clients don't want sequence gaps or want
> > sequences to start again with 1 at the beginning of each year.
>
> > How do you manage these situations?
>
> > C.
>
> > (followup set to CDOS)
>
> If no gaps are allowed, you have to create your own 'numbering system', and
> create values at commit time. Gaps come with sequences (or vice versa), like
> it or not. In my programming days, we used Oracle Designer, which knew
> functionality (autogen columns) for 'sequence within parent' functionality,
> by selecting the max. value of the sequence number in the table and adding 1
> to it. This had some multi user issues though.
>
> Shakespeare

Is there a question for the comp.databases.oracle.server forum here?

Generally speaking because writers do not block readers the select max() approach does not work well in Oracle for any table with concurrent update access, and in many cases the performance cost of the select max is unacceptable.

Setting an Oracle sequence to be nocache defeats the primary reason to use a sequence generator to create a value to begin with. You might as well use a user function to select and update a row value in this case.

• Mark D Powell --