5

EDIT: As gavenkoa's answer points out, Oracle Database 12c (released a couple of years after this question was asked) has support for Identity Columns.

As far as I know, Oracle's RDBMS is one of the few (the only?) SQL database products that doesn't support identity/autonumeric columns.

The alternative offered by Oracle is database sequences, a feature in many ways much more powerful than auto-numeric columns, but not equivalent.

It is not that I don't like sequences. What I hate is having a different programming model for generating row identity values between Oracle and any other database. For example, I often try to setup HSQL or SQLite for java apps that will eventually run over an Oracle database when I'm not working specifically on the data layer (just as a stub or mocking database). I cannot do that easily because I need different set of SQL DDL scripts: one for Oracle, and one for everyone else; I also need two sets of Hibernate mapping files if I'm using Hibernate.

What I find intriguing is that Oracle Database, being one of the most complete and robust enterprise software packages of the last decade hasn't put that seemingly basic feature in their product, but almost any other RDBMS, even the smaller ones, has it.

Why?

Why doesn't oracle support a sequence-based identity column shortcut syntax that dumb and lazy people like me can use?

The only reason I can think of is that Oracle does that on purpose as a vendor lock-in strategy so your code is harder to migrate to other RDBMS where your database sequences cannot be used.

Or maybe I'm just wrong and confused? Please enlighten me.

Community
  • 1
Sergio Acosta
  • 9,568
  • 3
  • 25
  • 36
  • they heard you they have it now in 12c http://www.oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1.php#identity-columns – Kalpesh Soni Aug 27 '13 at 21:48
  • This is probably really low-priority for them because you can "fake" it yourself with sequences and triggers. http://www.lifeaftercoffee.com/2006/02/17/how-to-create-auto-increment-columns-in-oracle/ – Joeri Sebrechts Oct 16 '10 at 13:12
  • Thanks for the hint. I still don't understand why I have to fake it, but it is nice to see that it is not very difficult. – Sergio Acosta Oct 18 '10 at 02:39
  • And it's not even necessarily to fake it as you can just put sequence_name.nextval in the SQL - e.g. insert into table_name (id_col, col1, col2) values (sequence_name.nextval, 'thing', 'other thing'). I think this isn't something they even see about as a requirement as they've solved the problems which would need it. – Jon Hopkins Dec 20 '10 at 16:58

4 Answers4

5

http://www.oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1.php

The 12c database introduces the ability define an identity clause against a table column defined using a numeric type. The syntax is show below.

GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]
gavenkoa
  • 271
  • 2
  • 10
3

I'm only guessing here but its probably for legacy reasons.

Sequences & identity columns have annoying properties like not respecting transactions. Sequences actually provide you with more flexibility than a plain identity column as it allows you, the developer, to decide how and when to apply the sequence.

Sequences also give you the ability to know your assigned sequence number before having to insert the record.

On a side note, if you plan in the future to support either replication or any form of disconnected'ness (eg mobile devices or offline connections to your database) i would suggest using GUIDs as your key. As this removes issues around sequence partitioning etc.

Thomas James
  • 697
  • 5
  • 13
  • GUIDs as a key can take a boatload of index vs. a properly sized numeric field. As a numeric, the GUID at 128 bits and effectively distributed across its bounds can mean many gibibytes of scan on a mere billion records. – Jé Queue Oct 20 '10 at 19:22
  • They case yes, but as i've commented above to HLGEM they solve a specific complex problem in a simple way. I'm not suggesting that GUIDs be used at all times, but just consider they have an appropriate use. Also: "mere billion records" ha :) – Thomas James Oct 20 '10 at 20:54
2

The priorities of Oracle are just way different from the once of the everyday developer.

From my experience I'd categorize Oracles attitude as follows:

Top Priority

  • Reliablity: If one thing makes you look bad in the RDBMS world, it is destroyed data, broken backups and select statements returning wrong data.

  • Performance: The customers that pay really big bucks (and who cares about the rest?) has huge databases and a lot of work to do. So things that make large databases fast are features that sell themselves

Low or No Priority

  • Developer usability: If a developer wants to work on something nice and fancy, she left RDBMSes a long time ago. So the rest doesn't mind jumping through hoops for some basic task. They also aquired so much specialized Oracle know how, that switching systems is difficult.

There a lot of thing that fall into this category: tool integration with Version Control systems; management for database migrations; Boolean datatype for table columns; Identity columns.

For all these things and probably many more there are some workarounds / patches that somehow get the job done. As in the case of sequences even more powerful and flexible, but inconvenient. But since almost no paying customer chooses the RDBMS based on its niceness to the developer Oracle doesn't care.

To get an impression of the mindset of Oracle (and of many developers/admins working with or for Oracle) let me quote a piece from Tom Kyte:

You Asked

Here's a real short one for you Tom:

Why doesn't Oracle RDBMS have a boolean datatype?

and we said...

since

...,
flag char(1) check (flag in ( 'Y', 'N' )),
...,

serves the same purpose, requires the same amount of space and does the same thing - I guess we feel this is a feature we can let them have that we really don't need.

I mean - what do you get back from a column in "access" that is a boolean? TRUE / FALSE. We'll give you Y/N -- if you would like TRUE/FALSE, we can accomplish that easily with DECODE(flag,'Y','TRUE','N','FALSE')

Which I personally translate to: Who needs types? Assembler is just fine.

Jens Schauder
  • 1,503
  • 8
  • 13
  • This answer comes off more as Oracle-bashing (which I can sympathize with) but does not answer the question. –  Sep 10 '14 at 14:47
  • 2
    @Snowman, I don't think it's Oracle-bashing. The way Oracle deals with the Boolean issue hints at the same philosophy seen for the sequence/identity issue, and it's fair to see a pattern there. I do think that the value judgment expressed in the last line ("Who needs types?") is less-than-objective, but, overall, I think this answer gets closer to reality than any of the ones above it. – user1172763 Sep 10 '14 at 15:56
  • I know that my question does not have a correct answer at least not coming by anyone outside Oracle. I really appreciate Jens' answer and it is very close to how I feel about this issue. I'm used to Microsoft products where IMO a good developer experience has always been part of how their tools are promoted and positioned, but I also consider Oracle RDBMS a more complete (and complex) product than SQLServer and it seemed strange that something as basic as Identity Columns was not supported. So I feel this answer is quite accurate even though we cannot prove it right. – Sergio Acosta Sep 12 '14 at 14:53
1

I don't know about sequence in Oracle db but I try to avoid using identity columns. Usually identity columns are used as surrogate keys and seem working well at beginning but sooner or later when business requires to transfer/export/import data between systems and clients, identity columns are the one that is most troublesome to deal with.

Codism
  • 1,213
  • 14
  • 13
  • 8
    Strongly disagree. The idnetity column is the least troublsome in a complex system becasue it doesn't change. And very, very few "natural" keys really are unique and unchanging as they should be. As fara as import/export, etc. I do that daily and it's not hard at all, you just set up a cross match table and you are in business. – HLGEM Oct 20 '10 at 17:48
  • 2
    @HLGEM: Guid also don't change and you don't need "a cross match table". "A cross match table" is not that obviously hard to handle, but think about an importing process taking constant memory/storage vs. linear memory/storage proportional to the data being imported. I worked on a project which involves transferring about 10 tables between handhold devices. To maintain referential integrity, I need to keep about 10 "cross match tables" in memory while processing. You may argue that that was a bad design since beginning, – Codism Oct 20 '10 at 17:59
  • @HLGEM: (sorry for not being able to complete my last comment, every time I use "END" I realize the comment is submitted). To continue - You may argue that that was a bad design since beginning, but who sees the requirement in future? – Codism Oct 20 '10 at 18:09
  • 2
    GUIDs can significantly slow down performance compared to integre identities. And I haven't yet seen anyone who wants to write queries using them in the where clause. I can ask a user to give me the person_id of the record (Name not being a unique identifier) that they think is a problem, I can't even show them a GUID or the average user freaks out. GUIDs are far worse on a day to day basis than identities. So to solve a happens-every-once-in-a-while problem, you permanently cripple you system. Seems short-sighted to me. Now if you are doing replication yes you need them. – HLGEM Oct 20 '10 at 18:19
  • @HLGEM: performance is a quite open topic beyond this discussion. The example you mentioned is in a context of where you can share a key with an external user. There are also scenarios where you do want to hide keys from external users. The point is: can we stop talking about performance prematurely? – Codism Oct 20 '10 at 19:40
  • 2
    @HLGEM: I've got to agree with Codism, GUID's make a disconnected system easier to manage over identity columns when you have devices that are "sometimes-connected". – Thomas James Oct 20 '10 at 20:50
  • @HLGEM: For the "user-friendly"ness of it we just introduced a numeric auto-number (oracle seq + trigger) that is assigned when the remote data is consolidated. This allows each "offline" database to keep its referential integrity in-tack and we dont need to re-write it on consolidation or do stilly things like partition the numeric key space. – Thomas James Oct 20 '10 at 20:52