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

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 23 Apr 2007 14:48:41 -0700
Message-ID: <1177364920.961225@bubbleator.drizzle.com>

hasta_l3_at_hotmail.com wrote:
> On 23 avr, 17:10, DA Morgan <damor..._at_psoug.org> wrote:

>> hasta..._at_hotmail.com wrote:
>>> On 22 avr, 20:07, DA Morgan <damor..._at_psoug.org> wrote:
>>>> hasta..._at_hotmail.com wrote:
>>>>> What people would like is to *supplement* sequences
>>>>> with a declarative way to telloracle: Please provide
>>>>> a unique value for this primary key if none specified.
>>>>> Something like DB2, which - as I get it - has both
>>>>> SEQUENCEs and IDENTITY columns...
>>>> I can understand the allure of such but laziness being
>>>> what it is I would expect most developers to be lazy and
>>>> the quality of applications to suffer.
>>>> --
>>> Quite to the contrary, Daniel.  The less code you write,
>>> the most solid an application is...
>> Based on that statement then I would assume that
>>
>> BEGIN
>>    FOR r IN (SELECT * FROM parent)
>>    LOOP
>>      -- modify record values
>>      r.part_num := r.part_num * 10;
>>      -- store results
>>      INSERT INTO child
>>      VALUES
>>      (r.part_num, r.part_name);
>>    END LOOP;
>>    COMMIT;
>> END slow_way;
>> /
>>
>> is more efficient than:
>>
>> DECLARE
>>
>> TYPE myarray IS TABLE OF parent%ROWTYPE;
>> l_data myarray;
>>
>> CURSOR r IS
>> SELECT part_num, part_name
>> FROM parent;
>>
>> BEGIN
>>    OPEN r;
>>    LOOP
>>      FETCH r BULK COLLECT INTO l_data LIMIT 1000;
>>
>>      FOR j IN 1 .. l_data.COUNT
>>      LOOP
>>        l_data(1).part_num := l_data(1).part_num * 1;
>>      END LOOP;
>>
>>      FORALL i IN 1..l_data.COUNT
>>      INSERT INTO child VALUES l_data(i);
>>
>>      EXIT WHEN r%NOTFOUND;
>>    END LOOP;
>>    COMMIT;
>>    CLOSE r;
>> END fast_way;
>> /
>>
>> I can get you a couple of dozen other similar examples if you'd like.
>>
>> The truth is that the less code you write the less is written. It has
>> nothing whatsoever to do with the quality, performance, or scalability.
>>

>
> We were talking of quality (or error rate or bug count)
> The (unwritten) assumption is that sequences and
> identity columns have the same performance and scalability
> (presumably the later is implemented with the former ?)
>

>> You might wish to reconsider.

>
> Mmmm... I think your underestimate how quickly small
> issues add up when programming in the large.
>
> You, and I, and pretty much everybody else on this
> planet, has an error rate of a few percents.
>
> A standard review process will make that error
> rate drop to an error every few thousand lines,
> at best one error every ten thousand lines
>
> Now, I estimate that a medium size system
> has a couple hundred tables. A declarative way
> to specify identity involves the same couple
> of lines, well wthin reach of a zero bug garantee
> of a standard process.
>
> I also estimate that a well-refactored system
> will have a few thousand inserts. That figure is
> pretty close to the error rate above, and we may predict
> that the system will have one sequence related
> bug, before testing.
>
> Now, good testing usually improves the error rate
> one more order of magnitude. However, it is pretty
> likely that tesing wont uncover a sequence related
> bug.
>
> Therefore, the small issue we are talking about
> makes the difference between none and one bug.
>
> The same analysis also explains why less code
> is preferable, performance and scalability being
> held constant
>
> --- Raoul

I don't underestimate anyone's ability to make mistakes. Especially me as I know from personal experience some of the uglier ones.

That said ... writing fewer lines of code does not guarantee fewer errors and neither do identity columns.

What guarantees fewer errors is code reviews and testing.

I can make a horrible error in one line of code. rm -rf *
and I know I can. <g>

-- 
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 Mon Apr 23 2007 - 16:48:41 CDT