Re: obtain primary keys

On 10 Feb 2005 14:00:27 -0800, phph109_at_yahoo.es 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 10.1.0.3 if that matters.
>
>Thanks for your time,
>ph

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