Solved

Oracle - PK field auto increment

Posted on 2004-04-22
11
1,273 Views
Last Modified: 2012-06-22
Hello everyone,

I am new to Oracle and I read that to implement auto increment on a field such needs to be done.

CREATE SEQUENCE emp_seq
start with 1
increament by 1
nomaxvalue
nocycle
nocache
;

then use:

insert into emp (empno, ename, sal, deptno)
values (seq_emp.nextval, 'Oracle Guru', 2300, 10)

OR create a trigger to do this.

now the question is do I have to create several emp_seq's?? because I have several tables that needs the auto increment. I understand how the sequence and trigger is used as a work around here. but are there any other options available? I am running Oracle 9i (9.2)

0
Comment
Question by:anusha
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 1

Expert Comment

by:Xavi23
ID: 10892951
the value is important?
if not, you can only create a one sequence and then use this sequence for every table (the value is unique)
0
 
LVL 1

Expert Comment

by:legendcain
ID: 10893023
Well, that's pretty much up to you... you could just have one sequence that multiple tables refer to... but, depending on your needs you might want to have a sequence per table, that's my approach anyway...

no matter what you end up doing... caching w/ sequences is a GOOD idea though depending on how often you will be popping values off of it... if it's quite frequently you certainly do NOT want nocache... because then the database has to do some work in the background for you (updating the sequence information in the data dictionary etc..) EVERY TIME you hit the sequence.. caching helps you scale a little bit more ya know?

-jim



 
0
 

Author Comment

by:anusha
ID: 10893106
what are my consequences of using 1 sequence for all tables ?
I have about 10 tables that will need auto increment and the values of the ID are important. They are my key fields.

I would like to take your advice on the caching issue

How should I write the sequence then?

CREATE SEQUENCE emp_seq
start with 1
increament by 1
nomaxvalue
nocycle
;
0
 
LVL 1

Accepted Solution

by:
legendcain earned 250 total points
ID: 10893236
SQL> create sequence my_seq cache 100;

Sequence created.

... So now the db caches 100 values for me... and when it comes time for the next 100, then the database does its stuff in the background again w/ updating the data dictionary and what not... assuming you're going to be popping values off pretty frequently, you want the cache to reflect your needs...

One thing I can say about having one sequence is assuming that all ten of those tables are firing at the sequence... it becomes a higher point of contention, so you have to set the cache on that one sequence to reflect the needs of your ten tables... i guess i rather like having one sequence per table because that way i set the cache for my needs of that table... say every day on emp you pump 1000 values off your sequence , ... maybe you set emp_seq to cache 1000 ... now some other table you do 100000 values, ... factor in the needs of your other 8 tables and it's tough to say what the perfect cache would be for one sequence ...

-jim
0
 
LVL 1

Expert Comment

by:legendcain
ID: 10893292
... sorry, forgot to address the "important" question...

if the values of the ID are important meaning that they're your keys and you're only concerned about uniqueness.. that's fine... sequences will only return unique values of course...

the only thing you'll end up w/ in that case would be table EMP might have ID 1, 2 ... another table might have ID 3, 4 ... and so on and so forth...

as long as that's fine w/ your application then it's not a problem...

even with a sequence per table.. you'll more than likely run into that scenario w/ caching... say you set your cache on EMP_SEQ to 100 and in one day you only create 50 EMP's... then that nite you restart your DB... 51-100 will be missing because those values were cached and "lost" when you restarted... no biggee tho... you'll have a hard time trying to exhaust a sequence :)

-jim
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Expert Comment

by:legendcain
ID: 10893346
... in a toned down environment you'll probably not notice having only one sequence... but, it just makes sense if you think about it.. you have a shared resource... the more tables that share that resource... the higher the point of contention... with one sequence you're lessening the stress on that one sequence by putting more stress on other sequences... i guess i just don't like putting all my eggs in one basket as they say.. ;)

-jim
0
 

Expert Comment

by:odedmaimon
ID: 10894256
i think that in your case you should use a sequence for each table, it makes more sence for the application.
0
 
LVL 8

Expert Comment

by:annamalai77
ID: 10895825
hi

i assume that each table has different functional purposes ie for eg. one table is for storing Purchase order, one table for receipt documents, one for issue documents like wise

if above is the case then u can have a document key table with different document types and their start value, last value, end value. have a pl/sql block which will fetch the last value from the document key table and increment it by one and inserting the incremented value to the table for every document table.

if above mentioned is not the scenario, and ur requirement is only to just have a id then i think having one sequence no for each table will be a better option with caching for each sequence based on the no of transactions for each table.

regards
annamalai

0
 

Expert Comment

by:odedmaimon
ID: 10897682
annamalai77 => <"if above is the case then u can have a document key table with different document types and their start value, last value, end value. have a pl/sql block which will fetch the last value from the document key table and increment it by one and inserting the incremented value to the table for every document table.">

why doing this?? why implementing this thing when he can use sequences???
0
 
LVL 8

Expert Comment

by:annamalai77
ID: 10897745
hi

well my friend odedmaimon i gave him 2 options one as u said, and the other one by sequences. its a better way to do it, by having a document key value table. then having a sequence. having sequence will put extra load on the server thats why.

regards
annamalai
0
 
LVL 4

Expert Comment

by:kssaran
ID: 10934938
Hi

The best practise would be to introduce a new table called Document_numbers and a function to get the Document Number for different table based on the Document Type. This way you can generate two types of Primary key Numbers one is for the transactions based on the Financial Month & Year like 2004040001,2004040002...  and other one is for just the number count 1,2,3... so on

Create table document_numbers
(
DNU_DOCTYPE                   VARCHAR2(10) NOT NULL,
DNU_DOCNAME                  VARCHAR2(30) NOT NULL,
DNU_FINYEAR                    NUMBER(8),
DNU_LASTDOCNO              NUMBER(10) NOT NULL
);


and the function goes like this

FUNCTION FN_GET_DOCNO(P_DOCTYPE IN VARCHAR2,P_DOCNAME IN VARCHAR2,P_TRANTYPE IN VARCHAR2 )
RETURN VARCHAR2 IS
CURSOR C_MAS IS SELECT NVL(DON_LASTNO,0)+1 LASTNO FROM DOCUMENT_NUMBERS
                WHERE DON_TYPE=P_DOCTYPE FOR UPDATE OF DON_LASTNO NOWAIT;
CURSOR C_TRAN IS SELECT NVL(DON_LASTNO,0)+1 LASTNO FROM DOCUMENT_NUMBERS
                WHERE DON_TYPE=P_DOCTYPE AND DON_DOCYYYYMM = TO_CHAR(SYSDATE,'YYYYMM')
                FOR UPDATE OF DON_LASTNO NOWAIT;
TMP_VAL NUMBER:=0;
BEGIN
IF P_TRANTYPE='M' THEN
    OPEN C_MAS;
    FETCH C_MAS INTO TMP_VAL;
    IF C_MAS%FOUND THEN
      UPDATE DOCUMENT_NUMBERS
      SET DON_LASTNO=TMP_VAL WHERE CURRENT OF C_MAS;
      RETURN TMP_VAL ;
    ELSE
       INSERT INTO DOCUMENT_NUMBERS
       VALUES  (P_DOCTYPE,  P_DOCNAME,NULL,1);
        RETURN 1;
    END IF;  
    CLOSE C_MAS;
ELSIF P_TRANTYPE='T' THEN
    OPEN C_TRAN;
    FETCH C_TRAN INTO TMP_VAL;
    IF C_TRAN%FOUND THEN
      UPDATE DOCUMENT_NUMBERS
      SET DON_LASTNO=TMP_VAL WHERE CURRENT OF C_TRAN;
      RETURN TO_CHAR(SYSDATE,'YYYYMM') || lpad(TMP_VAL,5,'00000') ;
    ELSE      
       INSERT INTO DOCUMENT_NUMBERS
        VALUES  (P_DOCTYPE, P_DOCNAME,TO_CHAR(SYSDATE,'YYYYMM'),1);
        RETURN TO_CHAR(SYSDATE,'YYYYMM') || lpad(1,5,'00000');
    END IF;  
    CLOSE C_TRAN;
END IF;
EXCEPTION
WHEN OTHERS THEN
 RAISE_APPLICATION_ERROR('-20001',SQLERRM);
END;

now for any kind of operation that you need to generate number based on the Financial Year & month You can do so by calling the function

Fn_GET_DOCNO with the required parameter. The Function will return the Unique Number for any type of Insert.

example:
insert into invoice_header (invno,invdt) values (FN_GET_DOCNO('INV','INVOICE HEADER','T'),sysdate)


Any more queries welcome

Rgds
Sara
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now