How should I store a GUID in Oracle?
DatabaseOracleGuidDatabase Problem Overview
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.
Database Solutions
Solution 1 - Database
CREATE table test (testguid RAW(16) default SYS_GUID() )
This blog studied the relative performance.
Solution 2 - Database
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.
Solution 3 - Database
If I understand the question properly, you want to generate a unique id when you insert a row in the db.
You could use a sequence to do this. link here
Once you have created your sequence you can use it like this:
INSERT INTO mytable (col1, col2) VALUES (myseq.NEXTVAL, 'some other data');
Solution 4 - Database
RAW(16) is apparently the preferred equivalent for the uniqueidentifier MS SQL type.
Solution 5 - Database
GUIDs are not as used in Oracle as in MSSQL, we tend to have a NUMBER field (not null & primary key) , a sequence, and a trigger on insert to populate it (for every table).
Solution 6 - Database
There is no uniqueidentifier in Oracle.
You can implement one yourself by using RAW (kind of a pain) or CHAR. Performance on queries that JOIN on a CHAR field will suffer (maybe as much as 40%) in comparison with using an integer.
If you're doing distributed/replicated databases, the performance hit is worth it. Otherwise, just use an integer.
Solution 7 - Database
The general practice using Oracle is to create an artificial key. This is a column defined as a number. It is populated via a sequence. It is indexed/constrained via a primary key definition.