RE: join after saving rowid Value into a Field - For Design , Dev. Gurus
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 [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: join after saving rowid Value into a Field - For Design , Dev. Gurus
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 [EMAIL PROTECTED] 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 = '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 ? = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Stephane=20Paquette?= INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author:INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
