[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资源,如果主键全部都是消耗还是很可观的.我个人不建议使用它.