[20180826]GUID做主鍵.txt

[20180826]GUID做主鍵.txt

--//我個人是反對使用GUID做主鍵的,我們有一個應用使用guid做主鍵,而且儲存的型別不是raw型別,而是轉換為varchar2(36),
--//中間還使用"-"分隔.重複連結的測試:
--//當然我個人也很矛盾,如果一個應用有許多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資源,如果主鍵全部都是消耗還是很可觀的.我個人不建議使用它.