guid主键 oracle_[20180826]GUID做主键.txt

[20180826]GUID做主键.txt

--//我个人是反对使用GUID做主键的,我们有一个应用使用guid做主键,而且保存的类型不是raw类型,而是转换为varchar2(36),

--//中间还使用"-"分隔.重复链接的测试:https://connor-mcdonald.com/2018/08/21/gooey-guids/

--//当然我个人也很矛盾,如果一个应用有许多sequence做主键(甚至上千),维护也是一个大问题.

1.环境:

SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID

------------------------------ -------------- -------------------------------------------------------------------------------- ----------

IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t ( sz int, dur interval day to second );

Table created.

2.测试脚本:

declare

ts_start timestamp;

ts_end   timestamp;

iter int;

dummy raw(32);

begin

for i in 1 .. 7 loop

--// for i in 1 .. 8 loop

iter := power(10,i);

ts_start := systimestamp;

if iter <= 10000 then

select max(x) into dummy from

(

select sys_guid() x from

( select 1 from dual connect by level <= iter )

);

else

select max(x) into dummy from

(

select sys_guid() x from

( select 1 from dual connect by level <= iter/10000 ),

( select 1 from dual connect by level <= 10000 )

);

end if;

ts_end := systimestamp;

insert into t values (iter, ts_end - ts_start );

commit;

end loop;

end;

/

--//我仅仅测试执行7次(8次时间太长了).

SCOTT@test01p> select * from t;

SZ DUR

---------- --------------------

10 +00 00:00:00.010000

100 +00 00:00:00.003000

1000 +00 00:00:00.030000

10000 +00 00:00:00.250000

100000 +00 00:00:02.264000

1000000 +00 00:00:24.194000

10000000 +00 00:03:54.217000

7 rows selected.

--//当然集中看消耗CPU资源,如果主键全部都是消耗还是很可观的.我个人不建议使用它.