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

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Fri, 27 Dec 2002 03:13:59 -0800
Message-ID: <F001.00522EDB.20021227031359@fatcity.com>

('binary' encoding is not supported, stored as-is)
Vivek,

  Bad, bad, bad idea. You can play with rowids in your programs - as long as you consider them to be transient values (get it/use it). Don't forget that they are physical addresses (BTW, DBMS were invented in the first place to hide the physical implementation from programs). Any export/import, ALTER TABLE MOVE, partition split etc., basically any kind of reorg which may shuffle your data on your disks will quietly make everything implode. Think about something as trivial as refreshing the development database. If your join doesn't run as fast as you hope, check that your stats are up-to-date, check the execution plan, and if then you still are left unsatisfied, try some hints (FIRST_ROWS, ALL_ROWS and ORDERED are the safest and my favorite ones). But never ever consider rowids as reliable technical data.

HTH, SF

>----- Original Message -----
>From: "VIVEK_SHARMA" <VIVEK_SHARMA_at_infosys.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Fri, 27 Dec 2002 02:28:41
>
>let us suppose there are two tables M and P. =0D
>both Contain the field emp_id. other columns may be
>different. =0D
> =0D
> =0D
>All records of M also Exist in P .Table M will have
>records in the range=0D
>1-5 lakhs.=0D
>P table will contain Additional Records such that
>the Total Number of=0D
>Records in P is 15-20 times the number of records
>in M.=0D
> =0D
>one way to join the two tables is to say M.emp_id
>=3D P.emp_id. but=0D
>because P has high number of records the select is
>slower.=0D
> =0D
>we found that select of a row from table "P" using
>"rowid" column was=0D
>very QUICK . =0D
> =0D
>Is it a Correct practice :-=0D
> =0D
>1) to Store the ROWID of Table P in M in a separate
>column (say=0D
>"P_rowid")=0D
> =0D
>2) Is it possible to do a Join like the follows :-
>=0D
> =0D
>select field1, field2,... from M,P =0D
>where M.emd_id =3D '6223' =0D
>and <like M.P_rowid =3D P.rowid>=0D
> =0D
>this way we hoped to select from M table (which has
>less number of=0D
>records) and do a rowid based select on P table,
>which we found out is=0D
>not allowed by ORACLE. =0D
> =0D
>we want a join because we want to create a view
>over table M and P.=0D
> =0D
>We do NOT want to use the following way :-=0D
> =0D
>select field1, field2 ..,P_rowid from M where
>emp_id =3D '6223' from a=0D
>Cursor & passing it to the Query as follows :-
>=0D
> =0D
>select * from P where rowid =3D P_rowid (selected
>earlier)=0D
> =0D
>Are there Some Standard Practices that Should be
>Followed during=0D
>Designing Tables , Fields, SQL Writing ?=0D
> =0D
>Any Dos , Don'ts ?=0D
> =0D

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroul
  INET: sfaroult_at_oriolecorp.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:13:59 CST