I keep running into that issue when I design a database, this time around it’s an Oracle schema, thus I need fresh input on GUIDs and Oracle. It’s basically the old story, if distribution and replication is a serious feature, a GUID would be very welcome. We could also create a hybrid monster, take a numeric primary key and supplement a GUID field for distribution purposes, sure it takes up additional space …

Arguments for the use of GUIDs

- Makes distribution and replication a lot easier.
- ID’s can be created in the application, thus no ID would need to fetched from a DB insert.
- Creating ID’s on the “client” side enables working “offline”.
- Enabled (dirty?) cross-table references, e.g. a table which contains website url’s could contain a generic “ID” column which contains id’s from the “books” table or “articles” table. This also applies for class models ofcourse.
- Security (by obscurity) in urls.
- don’t have to create those (annoying) sequences with triggers in Oracle :)

Arguments against the use of GUIDs

- Performance (will depend on which database, e.g. Oracle also has sequential GUIDs and MS-Sql has a native guid type).
- Universally small chance that it’s not unique.
- Looks ugly (heard that a LOT)
- It has a Microsoft feel to it :)

Well, Google also supplies some nice information about the performance, reasons to use (or not) and options:

[1] How should I store a Guid in Oracle
[2] Unique IDs for multi-master replication – Sequence or SYS_GUID?
[3] Watch out for sequential Oracle GUIDs!
[4] UUIDs as primary keys

- Probable performance loss of 40% when using a CHAR / RAW(16) instead of an integer. [1] (Searching is faster in CHAR as all the strings are stored at a specified position from the each other, the system doesnot have to search for the end of string, thus CHAR instead of VARCHAR2, plus the length of a GUID is fixed anyway)
Whereas in VARCHAR the system has to first find the end of string and then go for searching.
- SYS_GUID is sequential because random GUIDs play hell with indexes. SQL Server has a sequential GUID generator as well for the same reason.
- On SQL Server, the base datatype of a Guid is actually a binary(16) (equivalent to Raw(16) on Oracle). But when the Guid value is marshalled into the binary, the bytes are re-ordered!
- You can uses sys_guid() as a default column so that it is automatically populated. CREATE TABLE guid_table (pky RAW(16) default sys_guid() PRIMARY KEY, NAME VARCHAR2(100));
- Using UUIDs in URLs is more secure. [4]
- A nice function to format the SYS_GUID() [3] :

CREATE OR REPLACE FUNCTION GET_FORMATTED_GUID RETURN VARCHAR2 IS guid VARCHAR2(38) ;
BEGIN
SELECT SYS_GUID() INTO guid FROM DUAL ;

guid :=
'{' || SUBSTR(guid,  1, 8) ||
'-' || SUBSTR(guid,  9, 4) ||
'-' || SUBSTR(guid, 13, 4) ||
'-' || SUBSTR(guid, 17, 4) ||
'-' || SUBSTR(guid, 21) || '}' ;

RETURN guid ;
END GET_FORMATTED_GUID ;
/

/*
CREATE TABLE "TEST_GUID_FORMATTED"
(  "GUID_FORMATTED" CHAR(36),
"NAME" NVARCHAR2(50),
CONSTRAINT "PK_GUID_FORMATTED" PRIMARY KEY ("GUID_FORMATTED"
)</code>

truncate table test_guid_formatted;
commit;
*/

declare
stopwatch date;
begin
stopwatch := sysdate;
for counter in 1..1000000 loop
insert into test_guid_formatted (guid_formatted, name)
values (get_formatted_guid, 'test: ' || to_char(counter));
end loop;
dbms_output.put_line(to_char(sysdate - stopwatch));
commit;
end;