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

Galen Boyer wrote:
On Wed, 25 Apr 2007, damorgan@xxxxxxxxx wrote:
Galen Boyer wrote:
On Mon, 23 Apr 2007, damorgan@xxxxxxxxx wrote:

I think Oracle is remarkably easy to use given its power.
And so do I. We are talking about a particular feature of the
Oracle
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.

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.

Yet another disingenuous statement. In many situations BULK COLLECT and
FORALL allow for far superior performance gains over cursor loops, so it
behooves one to learn whatever they should learn to get the better
performance benefits.

The scenario we are discussing in this thread, on the other hand, have
no measurable diffence in performance. The only measurable parameter is
maintainability of code.

I don't see any evidence that your statement "have no measurable
diffence in performance" is valid. The history of autoincrementing
columns would indicate otherwise. IIRC one of the first things IBM
did with Informix was add sequences.

http://www.iiug.org/news/insider/insider_jul05.html

Also look at this:
http://www.dbmsmag.com/9809d14.html

Specifically the section titled: "Scalability Bottlenecks" from which
I will quote:

"Each of the major DBMS vendors has a nonportable solution to this problem. One example is Oracle, which uses a special data object called a SEQUENCE to get around the bottleneck. The Oracle SEQUENCE object allows transactions to get unique ID numbers without holding a lock on a table for the duration of the transaction. Many more transactions can run concurrently using this mechanism."

From my experience the distinction you are trying to make between cursor
loops and array processing on one hand and sequences and autoincrementing on the other does not hold up in the lab.

That isn't to say Oracle couldn't come up with a better implementation
of autoincrementing. But if they did my original objection would still
stand. I think they improve the chance of laziness replacing good practices.
--
Daniel A. Morgan
University of Washington
damorgan@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • RE: Access Append Query to update Sequence Generator in Oracle
    ... Jerry Whittle, Microsoft Access MVP ... to be a Trigger for TASSIGNINTERVAL ... Also committing should not make any difference to the sequence or trigger. ... was that the Sequence Generator in Oracle remained at the last number PRIOR ...
    (microsoft.public.access.queries)
  • RE: Access Append Query to update Sequence Generator in Oracle
    ... every insertion. ... Also committing should not make any difference to the sequence or trigger. ... was that the Sequence Generator in Oracle remained at the last number PRIOR ...
    (microsoft.public.access.queries)
  • RE: Access Append Query to update Sequence Generator in Oracle
    ... Oracle does not expect a commit after every record change. ... Also committing should not make any difference to the sequence or trigger. ... to the insert query, so, even though the new larger numbers went in, Oracle ...
    (microsoft.public.access.queries)
  • Re: Please help with no-gap autoincrement field
    ... Since MySQL has this feature, some of the code depends on it. ... I am new to ORACLE and based on my limited knowledge, ... autoincrement field with no gaps? ... as the source of the key rather than a sequence. ...
    (comp.databases.oracle.misc)
  • Re: Why Oracle dont have AUTO_INCREMENT as in MySQL
    ... Not the entire engine. ... The sequence is not as easy to ... me implementing cursor loops again. ... In the eyes of most developers the most efficient solution is the ...
    (comp.databases.oracle.misc)