How to use ROWID with an Oracle Join View

Question

I am trying to create a unique identifier for every row of a view. The view I have joins a lot of tables and therefore no one table's primary key will help me identify the row.

Doing a google search it looks like I may be able to achieve this by using rowid? But I'm not sure how to reference the view's rowid. Below is an example of how I envisioned rowid would work, but it obviously fails with an 'ambiguous column' error because I am not specifying a specific table for rowid.

Ex:


    with v_someTable (select...),
      v_anotherTable as (select blah, id from v_someTable where...),
      v_yetAnotherTable as (select foo, id from v_someTable where...)
    select distinct rowid, rt.key, v1.blah, v2.foo
    from realTable rt 
      left join v_anotherTable v1 on v1.id=rt.id 
      left join v_yetAnotherTable v2 on v2.id=rt.id

I am trying to do this in a query and not a stored procedure. Any help would be appreciated!

Thanks!

score 6 · Accepted Answer · answered Dec 07 '11 at 14:27

6

My understanding is that a rowid refers to a row in a physical table, rather than a row in a result set (which is effectively what a view is).

To get a unique identifier for each row, you'd need to combine the primary keys of the tables that you're joining in some way.

answered Dec 07 '11 at 14:27

paulbailey

5,328
22
35

Gotcha, I concatenated a bunch of primary keys to achieve what I wanted. Thanks! – user973479 Dec 07 '11 at 15:05

Kevin Burton · Answer 2 · 2011-12-07T14:59:51.290

4

If you do not have primary primary keys on all your tables you could select the rowids from the individual tables and concatenate them:

SELECT rt.rowid||v1.rowid||v2.rowid as uniqueid
FROM ......

edited Dec 07 '11 at 14:59

answered Dec 07 '11 at 14:51

Kevin Burton

11,676
2
24
37

I happen to have primary keys for the other tables but this is good to know. Thanks! – user973479 Dec 07 '11 at 15:05

score 1 · Answer 3 · answered Dec 10 '13 at 02:06

Don't use the ROWID pseudo column, it is storage dependend (and might change when the usefull ENABLE ROW MOVEMENT is used). You can also not use it to look up (joined) records on the view in a efficient way.

It is better to use the real PKs on your records in this case (for index lookups to work). And I would not join them, but just use multiple columns - only this way you can re-select them (with index support).

Henning · Answer 4 · 2014-10-15T13:51:28.370

This question is answered for a time now but please be careful when concatinating primary keys. For example when you have key1 = 23 and key2 = 45 and concatinate it to 2345 it is not clear if the keys were 23 and 45 or if they were 2 and 345.

Use a delimiter (23,45 -> 23_45) that can not occur in any of the keys (not all keys are numeric). Or fill up keys to max possible length (23,45 -> 00230045 (for key1 and key2 NUMBER(4,0))).

Also be aware of Oracles feature (not all DBs can handle this) to define primary and foreign keys over multiple columns which might be faster and extend your possibilities for clean joins without having to split your concatinated key.

score 0 · Answer 5 · answered Jun 23 '20 at 16:51

It's not recommended to use ROWID, but if you're forced to use it in JOIN or on where conditions, you have to use ROWIDTOCHAR function Ex: ROWIDTOCHAR(ROWID) = key

Note: the ROWIDTOCHAR function is used to convert a ROWID to its equivalent VARCHAR2 value ROWIDTOCHAR is available starting from Oracle 12c

How to use ROWID with an Oracle Join View

5 Answers5