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

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 27 Apr 2007 07:56:28 -0700
Message-ID: <1177685787.533768@bubbleator.drizzle.com>

Galen Boyer wrote:
> On Wed, 25 Apr 2007, damorgan_at_psoug.org wrote:

>> Galen Boyer wrote:
>>> On Mon, 23 Apr 2007, damorgan_at_psoug.org 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_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Apr 27 2007 - 09:56:28 CDT