Search code examples
sqloracle-databasejoinrowid

Oracle Join View - which rowid is used

CREATE VIEW EVENT_LOCATION ("EVENT_ID", "STREET", "TOWN") AS SELECT A.EVENT_ID, A.STREET, A.TOWN FROM TBLEVENTLOCATION A JOIN TBLEVENTS B ON A.EVENT_ID = B.EVENT_ID WHERE B.REGION = 'South';

if I run

SELECT ROWID, STREET, TOWN FROM EVENT_LOCATION

then which ROWID should I get back?

Reason I'm asking is: In the database there are many views with the above 'pattern'. It seems to differ which rowid is being returned from different views. ie. I am getting both A.ROWID or B.ROWID ...

UPDATE: I have resolved this using the following view. Which essentially guarantees the ROWID comes from the right table. Thanks for your replies!

CREATE VIEW EVENT_LOCATION ("EVENT_ID", "STREET", "TOWN") AS
  SELECT A.EVENT_ID, A.STREET, A.TOWN
  FROM TBLEVENTLOCATION A
  WHERE A.EVENT_ID IN (SELECT EVENT_ID FROM TBLEVENTS WHERE REGION = 'South');
Solution

  • Try looking at 

    select * from user_updatable_columns where table_name = 'EVENT_LOCATION'

    The columns that are updatable should indicate the table (and hence the rowid) which Oracle says is the child.

    Bear in mind that, if you use multi-table clusters (not common, but possible), then different tables in the same cluster can have records with the same ROWID.

    Personally, I'd recommend (a) don't use ROWID in your code anywhere and (b) if you do, then include an explicit evt.rowid evt_rowid column in the view.