Oracle primary key settings

I recently summarized how to set the oracle primary key. paste it here and save it. There are two ways to set a primary key, one is auto-incrementing primary key, and the other is to generate a unique sequence.
I. Auto-increment primary key

-- First create a table TEST
1 create table TEST
2 (
3 NID int primary key,
4 test1 varchar2 (20 ),
5 test2 varchar2 (20 ),
6 test3 varchar2 (20 ),
7 test4 varchar2 (20 ),
8 test5 varchar2 (20)
9)

-- Create another SEQ_TEST Sequence
 
1 create sequence SEQ_TEST
2 minvalue 1 -- Minimum value
3 nomaxvalue -- do not set the maximum value
4 start with 1 -- count from 1
5 increment by 1 -- add one at a time
6 nocycle -- always accumulate without repeating
7 nocache; -- do not create a buffer

The above code completes the establishment of a sequence, named SEQ_TEST. The range is from 1 to infinite (the infinite degree is determined by your machine ), nocycle decides not to loop. If you set the maximum value, you can use cycle to make seq reach the maximum and then loop. for nocache, by the way, if you provide the cache value, the system will automatically read the seq of your cache value, which will speed up the operation during repeated operations, however, if an unexpected situation occurs, such as when the machine or oracle is dead, the seq value retrieved next time will be inconsistent with the previous one. (If the connection is not coherent, we recommend using cache because time is money !)
It is not enough for you to only have tables and sequences. You 'd better create another trigger to execute it! The Code is as follows:
1 create or replace trigger tg_test
2 before insert on test for each row when (new. nid is null)
3 begin
4 select seq_test.nextval into: new. nid from dual;
5 end;
Below is the test
1 select * from test
2 insert into test (nid, test1) values (6, 'aaa ')
3 insert into test (test1) values ('bbb ')

Ii. unique sequence

SYS_GUID () generates a 32-bit unique encoding.
The number generated by the sequence generator can only be unique in a single instance, which is not suitable for using it as the primary keyword in the parallel or remote environment, the sequence in the environment may generate the same number, resulting in a conflict. SYS_GUID makes sure that the identifier it creates is unique in each database.
In addition, the sequence must be part of the DML statement, so it requires a round-trip process to the database (otherwise it cannot guarantee that its value is unique ). SYS_GUID is derived from the timestamp and machine identifier that do not need to access the database, which saves the query consumption.
Many applications rely on the sequence generator to create the primary Keywords of data rows. These data rows do not have an obvious primary value. That is to say, the creation of a record in such a dataset changes the data column. Therefore, the Administrator may be interested in using SYS_GUID as the primary keyword in the table without using sequence numbers. This is useful when objects are generated in different databases of different machines and need to be merged later.
Using SYS_GUID or sequence may cause performance consumption in some places in the database usage cycle; the problem is where. For SYS_GUID, the performance impact is on the query time and creation time (more blocks and indexes should be created in the table to accommodate data ). For the sequence, the performance impact during the query, at this time, the SGA sequence buffer is used up. By default, a sequence will buffer 20 values at a time. If the database is disabled without these values, they will be lost.
Another notable disadvantage of SYS_GUID generated values is that managing these values is much more difficult. You must (manually) enter them, fill them with scripts, or pass them as Web parameters. For these reasons, it is not a good idea to use SYS_GUID as a primary keyword unless it is in a parallel environment or if you want to avoid using a management sequence generator.


Author Peter Luo