John O'Hara wrote:
> I was curious if anyone knows if it is possible to implement the
> following in Oracle:
>
> MySQL has a feature called Auto-Increment, which is similar to Oracle
> sequences, however it was one some additional features that (I don't
> believe) sequences can provide.
>
> In MySQL, I can define the following table
>
> create table REVISION
> (
> filename VARCHAR2(256) not null,
> revision INTEGER not null AUTO_INCREMENT,
> constraint REVISION_PK primary key (filename, revision)
> );
>
>
> After doing so, we can run the following inserts:
>
> insert into revision(filename) values ('foo.txt');
> insert into revision(filename) values ('bar.txt');
> insert into revision(filename) values ('foo.txt');
> insert into revision(filename) values ('foo.txt');
> insert into revision(filename) values ('bar.txt');
>
> we get the resulting table
>
> filename revision
> foo.txt 1
> bar.txt 1
> foo.txt 2
> foo.txt 3
> bar.txt 2
>
> That is, a separate sequence is maintained for each filename. I have
> not found a way to do this in Oracle, short of querying the table each
> time for max(revision) where filename=? and incrementing it, but this
> seems error prone. Is there native support for this, or would I be
> forced to write some sort of trigger?
>
> Regards,
> John
No, that "feature" isn't available natively in Oracle.
I believe I read somewhere that MySQL tables are implemented as ISAM
files (ah, the memories that term invokes). If that's true, then
AUTO_INCREMENT would be a piece of cake to implement in MySQL ... that
is, until you added hundreds of thousands of 'foo.txt' records ... then
the feature would get slower and slower ... just like max() would in
Oracle. I might be mistaken, however. Even though MySQL is Open Source,
I haven't actually looked at the source.
Received on Fri Nov 22 2002 - 16:28:17 CST
