Re: Hello, a question comparing sql server to Oracle

Mark wrote:
> Hi, I want to insert into a table and have a primary key being a
> number. It is not important that this number is incremental so is
> allowed to have "holes". I don't want to assign the number in my Java
> code.

Yes in oracle you create a sequence which is "outside" of the table definition. A sequence can be used to populate keys for more than one table but often a sequence is created for each table.

> In Postgres and SQL Server I can set a column to be "autonumber" and
> the database automatically assigns it.

Well actually in SQL Server it is an identity column but close enough.

> Looking in Google it appears
> that this can't be done directly in Oracle. I have also checked and
> cannot find anything in the 10.2 manuals. I understand that I must
> "select sequencename.nextval into 'variablename' from dual" in a
> database trigger, then update :new.primarykeycolumnname with
> 'variablename'. If this is the only way of doing it then this is ok.
> But there is so much written in the newsgroup about pl/sql being slow
> so I don't want to code the trigger in pl/sql if there is a more
> efficient way of doing it.

Triggers do have some overhead.

If you don't want to use a trigger your insert statement can supply the name of the sequence

Insert into mytable_a (column_1, column2) values (sequence_for_tablea.nextval, 'some data');

• You don't have to retrieve the value first from oracle just give it the syntax like above.

But if you do it like this every insert in the application will need to be coded in a consistent manner.

If you do it in a trigger then none of the inserts have to code that column.

>
> Could someone please tell me if everything I have written above is
> correct. And what are my alternatives to improve speed if it is slow. I
> will be using Oracle 10.2 on RH.

Most shops ( well many at least ) are using trigger's. It is overhead but often fairly small.

Do you want to do some benchmarking in your environment? Received on Mon May 01 2006 - 12:10:02 CDT