0

I need to create a surrogate identity key for some intermediate tables used in a stored procedure in Oracle. I found that ROWID inserted into a UROWID column works well but this is not the correct way in older versions of Oracle (before 10g) -- using SEQUENCE.NEXTVAL is. SEQUENCE.NEXTVAL is a 2 step process and uses up memory/storage (full table scan) whereas with the ROWID way you just save the address and you're done. (like IDENTITY in SQL)
I want to use ROWID as the identity key. Is it OK for me to do this?

  • Why would correctly using a sequence ever cause a full table scan?!? – Erich Kitzmueller Aug 08 '11 at 22:06
  • I do mostly INSERT /*+ APPEND */ INTO table(...) SELECT ... FROM; I like to have the data ordered by the compound PK columns that the identity PK replaces (as a surrogate) so that later on I can order by the Id generated from the seq.nextVal. Makes debugging the intermediate table data left over after the stored procedure executes easy too. –  Aug 08 '11 at 22:29

1 Answers1

0

Just to be on the save side, this is how pros use sequences:

insert into master_table(id, x, y, z) values seq_master.nextval, :x, :y, :z;

insert into detail_table(master_id, a, b) values (seq_master.currval, :a, :b);
insert into detail_table(master_id, a, b) values (seq_master.currval, :c, :d);
...

I would prefer sequences any day over ROWIDs.

Erich Kitzmueller
  • 36,381
  • 5
  • 80
  • 102
  • I'm doing a direct load insert of the data. Direct Load Insert only works for INSERT INTO .. SELECT .... Inserts using the VALUES clause will use conventional insert. http://www.orafaq.com/tuningguide/direct%20path.html –  Oct 25 '11 at 23:09