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.

In Postgres and SQL Server I can set a column to be "autonumber" and
the database automatically assigns it. 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.

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.

Mark G.

On Mon, 01 May 2006 09:56:25 -0700, Mark wrote:

You do not have to select from dual and then transfer the value. You
could use the sequencename.nextval trick during the record insert.

Think of the nextval as a function that returns a numeric value. This can
be used in place of any number. Thus the following example in
Oracle's command line tool, SQLPlus:

SQL> connect test
Enter password:
Connected.
SQL> create table testit ( x number );

Table created.

SQL> create sequence seq1;

Sequence created.

SQL> insert into testit values (seq1.nextval);

1 row created.

SQL> / < means rerun previous command in SQLPlus

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from testit;

X
----------
1
2
3

SQL>



HTH
--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** Top posting [replies] guarantees I won't respond. ***

Mark wrote:
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.

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

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.

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?

On Mon, 01 May 2006 09:56:25 -0700, Mark wrote:

When doing things the wrong way - yes, PL/SQL could be slow.

Using PL/SQL when a simple set operation would have done the trick is
invariably doing to be slower than the set operation.

That's like saying "I have a dump truck, but I need to put the load into
the cab because all I know is how to handle a car. So each trip leaves the
dumpster empty, and I need a lot of trips. A lot slower than doing it
right."

But ... PL/SQL is slow - that's yet another myth.

(In fact, PL/SQL can be compiled to native C ... which is reasonably fast.
But still slower than doing it right.)

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** Top posting [replies] guarantees I won't respond. ***

In Oracle, you'll want to create a sequence and then create a trigger on
your table to generate the next value from a sequence and insert that
value in the table's column.

In SQL Server, the autoincrementing column value is tied directly to the
table. In Oracle, the sequence is not directly tied to anything...

HTH,
Brian


--
================================================== =================

Brian Peasland
oracle_dba (AT) nospam (DOT) peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown