How do we generate an identifier that is globally unique across databases and machines? A globally unique identifier is very useful. For example, if we mark a credit card transaction uniquely, we trace it in different stages including authorization, settlement, dispute etc.
In the earlier post Five Ways of Creating Unique Record Identifier For Oracle Tables we mentioned unique identifiers in Oracle can be created by using: 1. Oracle pseudocolumn rowid; 2. Oracle rownum; 3. row_number() function; 4. Sequence; 5. sys_guid().
The simplest solution to globally unique identifier is to call sys_guid() whenever we need to. It does not depend on a separate structure like sequence. Other mechanisms include rowid, rownum and row_number() are only guaranteed to be unique for a single table. I have seen a self-made globally unique identifier generator does not work 100% of the time.
The following query uses sys_guid function to generate globally unique identifier for every row in EMP table. Sys_guid returns 16 byte "raw" type.
SQL> select sys_guid() as gid, EMPNO, ENAME from EMP; GID EMPNO ENAME -------------------------------- ---------- ---------- EB567FE077DC5427E040D00AD17F0769 7839 KING EB567FE077DD5427E040D00AD17F0769 7698 BLAKE EB567FE077DE5427E040D00AD17F0769 7782 CLARK EB567FE077DF5427E040D00AD17F0769 7566 JONES EB567FE077E05427E040D00AD17F0769 7788 SCOTT EB567FE077E15427E040D00AD17F0769 7902 FORD EB567FE077E25427E040D00AD17F0769 7369 SMITH EB567FE077E35427E040D00AD17F0769 7499 ALLEN EB567FE077E45427E040D00AD17F0769 7521 WARD EB567FE077E55427E040D00AD17F0769 7654 MARTIN EB567FE077E65427E040D00AD17F0769 7844 TURNER EB567FE077E75427E040D00AD17F0769 7876 ADAMS EB567FE077E85427E040D00AD17F0769 7900 JAMES EB567FE077E95427E040D00AD17F0769 7934 MILLER 14 rows selected.
Post a Comment