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.

score 66 · Accepted Answer · edited Dec 26 '13 at 09:38

66

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

This blog studied the relative performance.

edited Dec 26 '13 at 09:38

Ben

51,770
36
127
149

answered Sep 30 '08 at 16:27

Turnkey

9,266
3
27
36

The blog only compares working with a guid that is generated from the db as apposed to working with a sequence.nextval, but i wonder how the performance is when working with guids generated from .net code ??? – gillyb Feb 21 '11 at 15:02
He added that clarification in the second paragraph after I answered. There would likely be some performance hit on insert due to the size of the indexes and the fact that the index would be split due to the random aspect of the .Net guid. If the native Oracle function is used then they are arranged sequentially to avoid that similar to the function in SQL Server. – Turnkey Feb 24 '11 at 01:45
+1 for concise answer with source, but what is the purpose of `default SYS_GUID()`? I skimmed the article, and it isn't apparent what that part is for. – zr00 Jul 27 '15 at 15:59
1

@zarose When a row is inserted and no value is specified for the `testguid` column, it will set a value by executing the `SYS_GUID()` function. – GalacticCowboy Dec 08 '15 at 14:18
@Turnkey But how to pass that the value of a GUID on to the database? What datatype is equivalent to RAW(16). In .NET a datatype of GUID doesn't work. – MrFox Jan 23 '17 at 10:09
2

@MrFox The RAW(16) is equivalent to a bytearray in .Net that is 16 bytes long. You don't have to pass anything with the default, but if you want to generate and pass a value you would pass something like this: var guid = Guid.NewGuid().ToByteArray() – Turnkey Jan 27 '17 at 03:40

Erik Anderson · Answer 2 · 2020-11-03T20:34:45.313

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.

score 4 · Answer 3 · answered Sep 30 '08 at 16:28

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');

score 3 · Answer 4 · answered Feb 27 '12 at 23:48

3

RAW(16) is apparently the preferred equivalent for the uniqueidentifier MS SQL type.

answered Feb 27 '12 at 23:48

stolsvik

5,253
7
43
52

score 2 · Answer 5 · answered Oct 06 '08 at 19:38

2

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).

answered Oct 06 '08 at 19:38

Osama Al-Maadeed

5,654
5
28
48

score 1 · Answer 6 · answered Sep 30 '08 at 16:24

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.

score 0 · Answer 7 · answered Sep 30 '08 at 16:24

0

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.

answered Sep 30 '08 at 16:24

dacracot

22,002
26
104
152

How should I store a GUID in Oracle?

7 Answers7

Linked