join after saving rowid Value into a Field - For Design , Dev. Gurus

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Fri, 27 Dec 2002 02:28:42 -0800
Message-ID: <F001.00522EA2.20021227022842@fatcity.com>

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 ?  

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: VIVEK_SHARMA
  INET: VIVEK_SHARMA_at_infosys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Dec 27 2002 - 04:28:42 CST