Re: Why Oracle don't have AUTO_INCREMENT as in MySQL

On 27 avr, 13:42, sybrandb <sybra..._at_gmail.com> wrote:
> On Apr 27, 8:20 am, hasta..._at_hotmail.com wrote:
>
>
>
> > On 26 avr, 06:03, DA Morgan <damor..._at_psoug.org> wrote:
>
> > > Galen Boyer wrote:
> > > > On Mon, 23 Apr 2007, damor..._at_psoug.org wrote:
>
> > > >> I thinkOracleis remarkably easy to use given its power.
>
> > > > And so do I. We are talking about a particular feature of theOracle
> > > > engine. Not the entire engine. The sequence is not as easy to
> > > > implement than an autoincremeting datatype, plain and simple.
>
> > > What I am saying that simple does not trump functional.
>
> > Daniel, I agree that faster *often* means more complex.
> > It is not always so, however.
>
> > For a counter-example, consider a many-rows insert
> > SQL statement, which we all know is *damn* faster
> > than an equivalent PL/SQL loop, yet is simpler
> > in many respects.
>
> > In the case of IDENTITY columns, it is hard to see
> > why they would be slower than sequences.
>
> > If anything, I would expect them to be marginally
> > faster than assigning a sequence with a trigger,
> > and on par with specification in an insert.
>
> > > Array processing with BULK COLLECT and FORALL is more complicated
> > > than cursor loops. But it will be a cold day in heck before you see
> > > me implementing cursor loops again.
>
> > Again, I sympathize with this view.
>
> > But dont forget that performance is not the only constraint.
> > Development effort and maintainability are other that come
> > to mind. I wouldn't mind a cursor loop in a rarely executed
> > procedure if it is fast enough.
>
> > In fine, the good engineer must find a solution that
> > fulfills the requirements at the minimum cost
> > (over the whole lifetime of the product)
>
> > --- Raoul
>
> No one ever calculates that minimum cost.
> Did your ever find a program which has been developed for
> *maintainability*.
> The basic rules for that were already laid out in
> the 60's and 70's.
> Yet programmers writing at the minimum cost hardcode *everything*
> simply because maintenance costs are *never* calculated.
> In the eyes of most developers the most efficient solution is the
> solution with the least effort on their side, and the highest payment
> from customer.

Hmmm... I dont see that the picture is so black, but then we may not work in the same fields. And I certainly agree that it is very easy to fall in the huge "quick and dirty" trap.

I specifically wrote that one wants to minimize cost over *the whole lifetime* of the product. For most programs, this indeed implies many (many !!!) efforts invested in maintainability (and many fights with management, which couldn't care less of maintainability :-)

It takes what it takes to achieve the required performance or scalability or maintainability. Nothing less.

But nothing more. Over-optimization or over-maintainability are *also* traps I have witnessed :-)

Going back to the main subject note that sequences often (but not always) go against many good programming principles.

When used in inserts :

• They require code duplication.
• They introduce unneeded coupling.
• They break abstraction (a client should not, *must not* have to know how a surrogate key is generated)

The situation is much better with triggers, but still the implementation of a single logical concept is scattered in three places, and that implies naming conventions, which in turn open opportunities for inconsistencies, etc....

Now dont get wrong. The issue is certainly pretty minor :-)

• Raoul