Saturday, June 02, 2012

Five Ways of Creating Unique Record Identifier For Oracle Tables

I have used the following five ways to create a unique record identifier for an Oracle table using SQL.
1. Simply use Oracle pseudocolumn rowid
Oracle pseudocolumn rowid comes with every table. It looks like the following.
SQL> select rowid from table_test where rownum <3;
ROWID
------------------
AAAYLPAAVAAEvLTAAA
AAAYLPAAVAAEvLTAAB
2. Create a  unique record id using rownum
create table table_test_new as select rownum as uniq_id, a.* from table_test a;
3. Create a unique record id using row_number()
create table table_test_new2 as select row_number() over(order by LOG_DATE)  uniq_id, a.* from  table_test a;
4. Create a unique record id using sequence.
create sequence rec_id_seq;
create table table_test_new3 as select rec_id_seq.nextval uniq_id, a.* from  table_test a;
5. Create a unique record id using sys_guid()
According to Oracle document, SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes.
create table table_test_new4 as select sys_guid() uniq_id, a.* from  table_test a;
What are other ways to create a unique record id that I am missing?

No comments: