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

Developers can also use the approach that Oracle uses with UROWID values, which are stored in secondary indexes on IOTs (i.e. replacing ROWIDs used in "normal" indexes).

Store the ROWID as well as the PK/UK column values. Use the following algorithm to retrieve in future:

1. Retrieve the PK/UK values by ROWID
2. Compare retrieved PK/UK values to those stored
3. If PK/UK values do not match, then retrieve again by PK/UK

The upsides and downsides should be pretty obvious, but it is certainly "safe"...

>
> You can use the rowid but do not keep it.
> As a dev DBA I would not allow to store the rowid in a
> table because its value is meaningless once you
> export/import, ...
>
>
> --- VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com> a écrit :
> > 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 > > and <like M.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
> > > > Cursor & passing it to the Query as follows :-
> >
> > select * from P where rowid > > earlier)
> >
> > Are there Some Standard Practices that Should be
> > Followed during
> > Designing Tables , Fields, SQL Writing ?
> >
> > Any Dos , Don'ts ?
> >
> >
>
> > Stéphane Paquette
> DBA Oracle et DB2, consultant entrepôt de données
> Oracle and DB2 DBA, datawarehouse consultant
> stephane_paquette_at_yahoo.com
>
> __________________________________________________________
> Lèche-vitrine ou lèche-écran ?
> magasinage.yahoo.ca
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net --
> Author: > INET: stephane_paquette_at_yahoo.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: tim_at_sagelogix.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).