Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

using sys_guid() as primary key

Mustafa KALAYCINov 25 2016 — edited Nov 29 2016

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.

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 27 2016
Added on Nov 25 2016
27 comments
19,584 views