How should I store a GUID in Oracle?

Question

I am coming from the SQL server world where we had uniqueidentifier. Is there an equivalent in oracle? This column will be frequently queried so performance is the key.

I am generating the GUID in .Net and will be passing it to Oracle. For a couple reasons it cannot be generated by oracle so I cannot use sequence.

Answer 1

CREATE table test (testguid RAW(16) default SYS_GUID() )  

This blog studied the relative performance.

Answer 2

As others have stated, there is a performance hit using GUIDs compared to numeric sequences. That said, there is a function named "SYS_GUID()" available since Oracle 8i that provides the raw equivalent:

SQL> SELECT SYS_GUID() FROM DUAL;  SYS_GUID() -------------------------------- 248AACE7F7DE424E8B9E1F31A9F101D5 

A function could be created to return a formatted GUID:

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 ; / 

Thus returning an interchangeable string:

SQL> SELECT GET_FORMATTED_GUID() FROM DUAL ;  GET_FORMATTED_GUID() -------------------------------------- {15417950-9197-4ADD-BD49-BA043F262180} 

A note of caution should be made that some Oracle platforms return similar but still unique values of GUIDs as noted by Steven Feuerstein.

Update 11/3/2020: With 10g, Oracle added support for regular expression functions which means the concatenation can be simplified using the REGEXP_REPLACE() function.

REGEXP_REPLACE(     SYS_GUID(),     '([0-9A-F]{8})([0-9A-F]{4})([0-9A-F]{4})([0-9A-F]{4})([0-9A-F]{12})',     '{\1-\2-\3-\4-\5}' ) 

The expression breaks out the string value returned by SYS_GUID() into 5 groups of hexadecimal values and rebuilds it, inserting a "-" between each group.