Oracle-L: RE: join after saving rowid Value into a Field

From: Richard Ji <richard.ji_at_mobilespring.com>
Date: Fri, 27 Dec 2002 03:03:43 -0800
Message-ID: <F001.00522EC0.20021227030343@fatcity.com>

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-----
Sent: Friday, December 27, 2002 5:29 AM
To: Multiple recipients of list ORACLE-L 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 :-

to Store the ROWID of Table P in M in a separate column (say "P_rowid")
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: Richard Ji
INET: richard.ji_at_mobilespring.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 - 05:03:43 CST

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