Re: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

Peter Teoh wrote:

I executed the following query:

select rowid, table_name from dba_tables where table_name = 'r'
                              *
ERROR at line 1:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
Check the internet but provide no reason, leads. Neither does "oerr" help - nothing mentioned for cause and action. 




Sorry for the noise...I am beginning to answer my own questions.......
According to http://igor.gold.ac.uk/oracle/9i/server.920/a96521/views.htm, only key-preserved table have the concept of rowid, correct?
And why only key-preserved table's view can have rowid? This is because only the kp-table can be uniquely identified by row, but not the one without key-preserved, in the view composing of kp and non-kp tables.
What is a key-preserved table then? Essentially, my interpretation is that kp-table (inside the definition of a view) allows queries on the view to be mapped back one-to-one to that specific table. So the example in the above link mentioned that emp is a key-preserved table, but not dept. So update to column in emp_dept that correspond to ANY columns from emp is allowed, but not dept. Correct? So the existence of this kp-table inside a view is called "updateable join view", correct?
dept is not a kp-table in the emp_dept view. but it is a kp-table in the following view: 

SQL> create view dept1v as select * from dept;

View created.

SQL> select rowid from dept1v;
AAADpsAAFAAAC+9AAA
So "key-preserved table" must always be followed by "with respect to a particular viewname". This is the key concept not mentioned clearly, and another is that there is a one-to-one mapping involved. Are my analysis correct? 

Please enlighten.....:-).
--
http://www.freelists.org/webpage/oracle-l

Other related posts: