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