Definition:
In Oracle PL/SQL, SYS_GUID is a built in function which returns the Global Unique Identifier (GUID) for a row in a table. It accepts no arguments and returns a RAW value of 16 bytes. Note that it is different from ROWID.
A GUID is a sequence of characters that are supposed to be globally unique. In other words, they should never appear more than once regardless of the circumstances. You would normally use GUIDs when you need absolutely unique values that involve multiple database instances or multiple networks. The algorithm that Oracle uses is somewhat faulty in that the values are unique but they are not random (or even pseudo-random).
Example Syntax:
SYS_GUID()
Example Usage:
The SQL example below demonstrates that ROWID and SYS_GUID generate different unique identifiers for a row in a table:
SQL> select rowid, '---------' SEPARATOR ,sys_guid() from employee
ROWID SEPARATOR SYS_GUID()
------------------ --------- --------------------------------
AAATN8AAGAABUsjAAA --------- 3B578C494C7C47CA8E2DA59C7E631B2C
AAATN8AAGAABUsjAAB --------- 34C14293F4D549B38E5E363240772F89
AAATN8AAGAABUsjAAC --------- 8419065E52624162991124CA81616508
AAATN8AAGAABUsjAAD --------- 9EF2BB66AA5445A084FED980DF074A59
AAATN8AAGAABUsjAAE --------- 414B7AF0A3694FA4AF79ACEF988C8161
AAATN8AAGAABUsjAAF --------- 5BBB727CC66240EEB43D43C7F98364F8
AAATN8AAGAABUsjAAG --------- 179768E73B404ADFA95C009C4B38431E
AAATN8AAGAABUsjAAH --------- 556EF536C3294162BC43D84AC8883B22
AAATN8AAGAABUsjAAI --------- 9721FF2DB26F41CDB5FBF6ADAAA83B6D
9 rows selected.
Developer Note: SYS_GUID can be used as a default value for a primary key column, which is often more convenient than using a sequence, but note that the values will be more or less random and not sequential. On the plus side, that may reduce contention for hot blocks, but on the minus side your index inserts will be all over the place as well. We generally recommend against this practice.
Related Links:
