Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: obtain primary keys

Re: obtain primary keys

From: Sybrand Bakker <>
Date: Thu, 10 Feb 2005 23:19:39 +0100
Message-ID: <>

On 10 Feb 2005 14:00:27 -0800, wrote:

>Hello all!
>First, I'm not a dba- I am a sysadmin. company is "between" dba's so I
>have to take care of an oracle database used for developping a webapp.
>The application uses stored procedures where I see a lot of:
> lock table t in exclusive mode nowait;
> select max(ix) + 1 into pix from t;
> insert into t values (pix, etc...);
> commit;
>To me this locking seems a bit drastic.
>One way of doing it would be using sequences from triggers, like:
> create or replace trigger tinsert
> before insert on t for each row
> begin
> if :new.ix is not null then
> raise_application_error(-20000, 'ix must be null');
> else
> select seq_t_ix.nextval into :new.ix from dual;
> end if;
> end;
>What I like with this is that there is no way the programmers can fail
>:-) but there are a slight drawback when it comes to concatenated
>primary keys.
>There are parts of concat keys that needs to be changed from number(6)
>to number(13) if to use sequences. This might be a stupid question, but
>it is somehow possible to estimate, calculate or test how such change
>will effect performance and disk usage?
>Any thoughts on this? Is there an "Oracle-way" or "best practice" on
>how to get primery key values?
>Them programmers are used to sql-server and claims that there is a
>special datatype for this there but this does not seem to be the case
>for Oracle- or am I wrong?
>It's on if that matters.
>Thanks for your time,

1 Concatenated primary keys are best avoided at all 2 For non-concatenated primary keys usually the number(10) definition is more than sufficient.
3 There is no special datatype and there is no need for it as there are sequences. The advantage of a sequence is, it is not bound to a table.
I don't think you will even notice changing a single key column from number(6) to number(13). However, you'll probably will notice the use of a composite primary key.

Sybrand Bakker, Senior Oracle DBA
Received on Thu Feb 10 2005 - 16:19:39 CST

Original text of this message