Re: Hello, a question comparing sql server to Oracle
- From: "hpuxrac" <johnbhurley@xxxxxxxxxxxxx>
- Date: 1 May 2006 10:10:02 -0700
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?
.
- Prev by Date: Re: one datafile and redo log files both r corrupted
- Next by Date: Re: one datafile and redo log files both r corrupted
- Previous by thread: Re: Hello, a question comparing sql server to Oracle
- Next by thread: Re: Hello, a question comparing sql server to Oracle
- Index(es):
Relevant Pages
|
