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

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