RE: join after saving rowid Value into a Field - For Design , Dev

[Richard Ji]

Title: Message

Not a good idea to store rowid in table M.  If you ever move table P to a different tablespace

or within the same tablespace, all it's rowid would change.

 

Richard Ji

-----Original Message-----
From: VIVEK_SHARMA [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 27, 2002 5:29 AM
To: Multiple recipients of list ORACLE-L
Subject: join after saving rowid Value into a Field - For Design , Dev. Gurus

let us suppose there are two tables M and P.

both Contain the field emp_id. other columns may be different. 

 

 

All records of M also Exist in P .Table M will have records in the range 1-5 lakhs.

P table will contain Additional Records such that the Total Number of Records in P is 15-20 times the number of records in M.

 

one way to join the two tables is to say M.emp_id = P.emp_id. but because P has high number of records the select is slower.

 

we found that select of a row from table "P" using "rowid" column was very QUICK . 

 

Is it a Correct practice :-

 

1) to Store the ROWID of Table P in M in a separate column (say "P_rowid")

 

2) Is it possible to do a Join like the follows :- 

 

select field1, field2,... from M,P

where M.emd_id = '6223'

and <like M.P_rowid = P.rowid>

 

this way we hoped to select from M table (which has less number of records) and do a rowid based select on P table, which we found out is not allowed by ORACLE.

 

we want a join because we want to create a view over table M and P.

 

We do NOT want to use the following way :-

 

select field1, field2 ..,P_rowid from M where emp_id = '6223' from a Cursor & passing it to the Query as follows :-     

 

select * from P where rowid = P_rowid (selected earlier)

 

Are there Some Standard Practices that Should be Followed during Designing Tables , Fields, SQL Writing ?

 

Any Dos , Don'ts ?