hello everyone,
This is not a question just wonder about your ideas and experiences. I just want to learn your opinions about the subject. I already search forum and google it, read blogs etc. is there anyone using this? many blog says sys_guid can be used but it is a little bit "slower" against sequences. so I run my tests and it is much more faster than sequences. here is my test:
oracle 11.2.0.3 DB on an Exadata X3 (1/8)
drop table t_Seq;
drop table t_raw;
drop sequence seq1 ;
create sequence seq1; -- default 20 cache
create table t_seq
( id number(9) primary key
, filler varchar2(1000)
);
insert into t_seq
select seq1.nextval ,'sdfsf' from dual connect by level < 1000000;
-- this takes 25 seconds
drop table t_seq;
drop sequence seq1;
create sequence seq1 cache 10000;
create table t_seq
( id number(9) primary key
, filler varchar2(1000)
);
insert into t_seq
select seq1.nextval ,'sdfsf' from dual connect by level < 1000000;
-- this takes 13 seconds
create table t_raw
( id raw(16) primary key
, filler varchar2(1000)
);
insert into t_raw
select sys_guid(),'sdfsf' from dual connect by level < 1000000;
-- this takes 3.8 seconds
select segment_name, bytes from user_Segments where segment_name in (select index_name from dba_indexes where table_name in ('T_SEQ', 'T_RAW'));
SYS_C0069970 28311552 --sys_guid
SYS_C0069969 15728640 --sequence
select segment_name, bytes from user_Segments where segment_name in ('T_SEQ', 'T_RAW');
T_RAW 32505856
T_SEQ 18874368
so first create a sequence with default 20 cache, and then test it with 10.000 cache. best sequence performance is 13 seconds (with 10.000 cahce) bu sys_guid is already much more faster, 3.8 seconds. I also check the sizes of course both index and table. SYS_GUID seems bigger twice than the sequnce (but of course sequnce number is starting from 1 and table column is number(9), if this was bigger, space would be bigger).
so I just want to test sequence and sys_guid:
drop sequence seq1;
create sequence seq1 ;
declare
x number(38);
function sf return number is
begin
return seq1.nextval;
end;
begin
for i in 1..100000 loop
x := sf;
end loop;
end;
/
-- this takes 6 seconds
drop sequence seq1;
create sequence seq1 cache 10000;
declare
x number(38);
function sf return number is
begin
return seq1.nextval;
end;
begin
for i in 1..100000 loop
x := sf;
end loop;
end;
/
-- this takes 4.8 seconds
declare
x raw(16);
function sf return varchar2 is
begin
return sys_guid();
end;
begin
for i in 1..100000 loop
x := sf;
end loop;
end;
/
-- this takes 2.9 seconds.
times become more closer but still sys_guid is faster. also I realized, if I run sequence codes twice without dropping sequences, time is getting longer. also SYS_GUID can be a default value for a table column.
so what do you think? thanks.