Menu
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.
221
-- If you want to generate a new Guid (uniqueidentifier) in SQL server the you can simply use the NEWID() function. -- This will return a new random uniqueidentifier e.g. You can directly use this with INSERT statement to insert new row in table.
The GUID data type is a 16 byte binary data type. This data type is used for the global identification of objects, programs, records, and so on. The important property of a GUID is that each value is globally unique. The value is generated by an algorithm, developed by Microsoft, which assures this uniqueness.
GUIDs may seem to be a natural choice for your primary key - and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I'd strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless you specifically tell it not to.
SYS_GUID () function in oracle database can be defined as a built-in function in PL/SQL which is used to generate and return a global unique identifier (GUID) (RAW value) of size 16 bytes for each row of the table and it does not accept any argument in the function, it generates GUID which are supposed to be unique ...
CREATE table test (testguid RAW(16) default SYS_GUID() ) This blog studied the relative performance.
73
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.
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
