A colleague of mine sent me this link, and wanted me to have a look at point 5:
Choose sys_guid() instead of sequences for populating meaningless PK-columns.
Traditional sequences are used for populating an ID-column.
Sequences have several disadvantages:
• Sequences have to be created, maintained and deployed.
• Sequences need PL/SQL-code to be fetched (the ‘select into’ construction is obsolete in 11g, but defaulting a column with sequence.nextval still does not work).
• When you introduce triggers to fetch the .nextval value, you introduce significant undesired performance penalties
• When moving your data from data from one environment to another (e.g. copy PRODUCTION data to ACCEPTANCE), you have to synchronize the .LASTVAL of all sequences.
• Fetching sequences can be a performance-critical in big environment (bulk fetch in Data warehouse for instance) which has to be fixed by increasing the CACHE property
Use guid's to overcome the disadvantages of sequences:
create table employees
( guid raw(32) default sys_guid() not null primary key);
I knew about the
SYS_GUID() function, but didn't realize you can use it as a default value for a primary key column. That sure is convenient. Where other RDBMS all have some sort of autonumber/identity column to automatically fill an ID column, Oracle uses sequences, which is a little more cumbersome to implement. However, I found the story not really balanced. Before addressing each point and mentioning some of the disadvantages of SYS_GUID, let's see how it works. Using sequences, a simple setup can be like this:
rwijk@ORA11GR1> create sequence seq cache 1000
2 /
Sequence created.
Here I create the sequence with "cache 1000", because I want a session to fetch 1000 numbers at once, instead of one each time. This reduces the overhead for Oracle to maintain the SYS.SEQ$ table. Gaps in the numbers might be large this way, but this shouldn't bother you. If it does bother you, you likely shouldn't be using a sequence, as they are never gap-free. A table is created without any reference to the sequence:
rwijk@ORA11GR1> create table t_seq
2 ( id number(9) primary key
3 , filler varchar2(1000)
4 )
5 /
Table created.
To fill the id column with the sequence value, I prefer to just use the nextval function directly in my api. Another option is to use database triggers, but I'm on a mission to
avoid those whenever possible. The next procedure represents such an api:
rwijk@ORA11GR1> create procedure p_seq
2 is
3 begin
4 insert into t_seq (id,filler)
5 select seq.nextval
6 , lpad('*',1000,'*')
7 from dual
8 connect by level <= 20000
9 ;
10 end;
11 /
Procedure created.
Using the SYS_GUID() function, a similar setup would look like this:
rwijk@ORA11GR1> create table t_sysguid
2 ( id raw(16) default sys_guid() primary key
3 , filler varchar2(1000)
4 )
5 /
Table created.
rwijk@ORA11GR1> create procedure p_sysguid
2 is
3 begin
4 insert into t_sysguid (filler)
5 select lpad('*',1000,'*')
6 from dual
7 connect by level <= 20000
8 ;
9 end;
10 /
Procedure created.
One object less (the sequence), and you don't need to bother about the ID column in the insert statement. The mentioned disadvantages of sequences per point:
Sequences have to be created, maintained and deployedWhile obviously true, I don't think it is a major point. A sequence never comes alone, so we are already creating, maintaining and deploying database objects. But it adds up a little indeed.
Sequences need PL/SQL-code to be fetched (the ‘select into’ construction is obsolete in 11g, but defaulting a column with sequence.nextval still does not work)You don't necessarily need PL/SQL to use a sequence, as shown above. So this is untrue. Although it is true that you cannot default a column with sequence.nextval.
When you introduce triggers to fetch the .nextval value, you introduce significant undesired performance penaltiesSo don't introduce database triggers to fetch the .nextval value :-)
When moving your data from data from one environment to another (e.g. copy PRODUCTION data to ACCEPTANCE), you have to synchronize the .LASTVAL of all sequencesThis is true. Very minor disadvantage though, in my opinion. If we refresh the acceptance database, we don't only refresh the data, but also the objects itself.
Fetching sequences can be a performance-critical in big environment (bulk fetch in Data warehouse for instance) which has to be fixed by increasing the CACHE property.The solution to the possible problem is already presented here.
No advantages of sequences over SYS_GUID are mentioned, so here are some of mine:
A RAW(16) column takes up more space than a NUMBER column.The SYS_GUID is a RAW(16) taking up 16 bytes, whereas as an ID populated by a sequences typically is a NUMBER(6), NUMBER(9) or NUMBER(12). A reasonable average is NUMBER(9) which takes up only 4 bytes. When you have foreign key columns in your table, this effect is multiplied.
Ad-hoc querying has become more cumbersome.Which one would you rather type. This:
select id from t_sysguid where id = '48000DA3C76E41A789C7E8925A91E28A'
Or this:
select id from t_seq where id = 1234
You can no longer see the order of inserts by the ID column.When using the cache property and multiple sessions, this is neither necessarily true for sequence populated ID columns, but there is a strong correlation between the number and the time it was populated. This "information" is not there with SYS_GUID() populated columns.
Performance seems to be slightly less when using SYS_GUID()The results of my test varied a lot. But generally it favoured the sequence approach. Here is the output of one of the more representative tests:
rwijk@ORA11GR1> begin
2 runstats_pkg.rs_start;
3 p_seq;
4 runstats_pkg.rs_middle;
5 p_sysguid;
6 runstats_pkg.rs_stop(1000);
7 end;
8 /
Run1 draaide in 1670 hsecs
Run2 draaide in 2220 hsecs
Run1 draaide in 75.23% van de tijd
Naam Run1 Run2 Verschil
STAT.recursive calls 1,162 49 -1,113
LATCH.enqueue hash chains 1,626 508 -1,118
STAT.hot buffers moved to head of LRU 1,213 75 -1,138
STAT.dirty buffers inspected 5,613 3,266 -2,347
STAT.free buffer requested 6,215 3,482 -2,733
STAT.free buffer inspected 6,678 3,577 -3,101
STAT.redo subscn max counts 4,147 753 -3,394
STAT.heap block compress 3,678 7,119 3,441
LATCH.cache buffer handles 4,922 0 -4,922
STAT.Heap Segment Array Inserts 12,512 7,156 -5,356
STAT.HSC Heap Segment Block Changes 12,582 7,156 -5,426
STAT.calls to get snapshot scn: kcmgss 6,752 152 -6,600
LATCH.checkpoint queue latch 17,879 9,818 -8,061
LATCH.cache buffers lru chain 27,793 13,845 -13,948
LATCH.object queue header operation 40,308 25,913 -14,395
STAT.consistent gets 26,810 7,302 -19,508
STAT.consistent gets from cache 26,810 7,302 -19,508
STAT.consistent gets from cache (fastpath) 19,687 145 -19,542
STAT.redo entries 42,552 71,492 28,940
STAT.db block gets from cache (fastpath) 21,080 57,202 36,122
STAT.session logical reads 98,920 144,900 45,980
LATCH.simulator hash latch 5,752 56,028 50,276
STAT.db block gets from cache 72,110 137,598 65,488
STAT.db block gets 72,110 137,598 65,488
STAT.db block changes 63,379 129,357 65,978
LATCH.sequence cache 150,100 0 -150,100
LATCH.cache buffers chains 396,737 572,383 175,646
STAT.undo change vector size 2,564,000 4,921,872 2,357,872
STAT.physical read total bytes 17,776,640 23,355,392 5,578,752
STAT.physical read bytes 17,776,640 23,355,392 5,578,752
STAT.cell physical IO interconnect bytes 17,776,640 23,355,392 5,578,752
STAT.physical IO disk bytes 17,776,640 23,355,392 5,578,752
STAT.redo size 59,361,376 66,097,504 6,736,128
Run1 latches totaal versus run2 -- verschil en percentage
Run1 Run2 Verschil Pct
679,069 707,672 28,603 95.96%
PL/SQL procedure successfully completed.
So while I am certainly not saying no to SYS_GUID(), I thought there was a little more to the story than the all-positive points in the link.
If you know some more advantages or disadvantages from one over the other, please say so in a comment.
