Re: Adding rowids to fragmented table
This is a multi-part message in MIME format. ------=_NextPart_000_000F_01C4C0E5.312B94C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I believe that adding "rowids" to a fragmented table merely maps=20 the real rowids to a column that can be referenced as "rowid" -=20 and is, for most intents and purposes, a rowid column. Because of this, I don't think that you will have to drop and recreate the indices....how is that for confidence? Here is what the 9.4 Reference Guide Says about rowids: In nonfragmented tables, rowid refers to an integer that defines the = physical location of a row. Rowids must be explicitly created to be used in = fragmented tables and they do not define a physical location for a row. Rowids in fragmented tables are accessed by an index that is created when the = rowid is created; this access method is slow. It is recommended that users = creating new applications move toward using primary keys as a method of row identification instead of using rowids. Regards, Ben ----- Original Message -----=20 From: "Sebastian, Norma J." <NormaJean.Sebastian@tellabs.com> To: <informix-list@iiug.org> Sent: Tuesday, November 02, 2004 12:22 PM Subject: Adding rowids to fragmented table > Hi, >=20 > When adding rowids to a frag'd table, should I drop all the indices > first before altering the table? (the rebuild indices afterwards) >=20 > If I drop the indices before alter table, am I just making work for > myself?... Does adding rowid change the indices in any way (I could = just > re-stat the table)? It will take 1-2 hours to recreate the indices. >=20 > Table is 40 GB, (4 frags, round robin). 4 indices taking about 6 GB > (total, not each). >=20 > I'll test multiple ways if project gives me the time. >=20 > Would appreciate input on best practice for adding rowids... (best > would be not to add them, but SAP needs them for the upgrade). I > checked sys admin and perf guides... Either I didn't check the right > places, or there's not that much there about this... >=20 > Thanks, > Norma Jean >=20 > ----------------------------------------- > = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D The > information contained in this message may be privileged and = confidential > and protected from disclosure. If the reader of this message is not = the > intended recipient, or an employee or agent responsible for = delivering > this message to the intended recipient, you are hereby notified that = any > reproduction, dissemination or distribution of this communication is > strictly prohibited. If you have received this communication in = error, > please notify us immediately by replying to the message and deleting = it > from your computer. Thank you. Tellabs > = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D >=20 >=20 > sending to informix-list ------=_NextPart_000_000F_01C4C0E5.312B94C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2800.1476" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT face=3D"Courier New" size=3D2>I believe that adding "rowids" = to a=20 fragmented table merely maps </FONT></DIV> <DIV><FONT face=3D"Courier New" size=3D2>the <EM>real</EM> rowids = </FONT><FONT=20 face=3D"Courier New" size=3D2>to a column that can be referenced as = "rowid" -=20 </FONT></DIV> <DIV><FONT face=3D"Courier New" size=3D2>and is, for <EM>most</EM> = intents and=20 purposes, a rowid column. Because</FONT></DIV> <DIV><FONT face=3D"Courier New" size=3D2>of this, I don't think that you = will have=20 to drop and recreate</FONT></DIV> <DIV><FONT face=3D"Courier New" size=3D2>the indices....how is that = for=20 confidence?</FONT></DIV> <DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Courier New" size=3D2>Here is what the 9.4 Reference = Guide Says=20 about rowids:</FONT></DIV> <DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>In nonfragmented tables, rowid refers = to an integer=20 that defines the physical<BR>location of a row. Rowids must be = explicitly=20 created to be used in fragmented<BR>tables and they do not define a = physical=20 location for a row. Rowids in<BR>fragmented tables are accessed by an = index that=20 is created when the rowid is<BR>created; this access method is slow. It = is=20 recommended that users creating<BR>new applications move toward using = primary=20 keys as a method of row<BR>identification instead of using = rowids.</FONT></DIV> <DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Courier New" size=3D2>Regards,</FONT></DIV> <DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Courier New" size=3D2>Ben</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>----- Original Message ----- </FONT> <DIV><FONT face=3DArial size=3D2>From: "Sebastian, Norma J." = <</FONT><A=20 href=3D"mailto:NormaJean.Sebastian@tellabs.com"><FONT face=3DArial=20 size=3D2>NormaJean.Sebastian@tellabs.com</FONT></A><FONT face=3DArial=20 size=3D2>></FONT></DIV> <DIV><FONT face=3DArial size=3D2>To: <</FONT><A=20 href=3D"mailto:informix-list@iiug.org"><FONT face=3DArial=20 size=3D2>informix-list@iiug.org</FONT></A><FONT face=3DArial=20 size=3D2>></FONT></DIV> <DIV><FONT face=3DArial size=3D2>Sent: Tuesday, November 02, 2004 12:22=20 PM</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Subject: Adding rowids to fragmented=20 table</FONT></DIV></DIV> <DIV><FONT face=3DArial><BR><FONT size=3D2></FONT></FONT></DIV><FONT = face=3DArial=20 size=3D2>> Hi,<BR>> <BR>> When adding rowids to a frag'd table, = should I=20 drop all the indices<BR>> first before altering the table? (the = rebuild=20 indices afterwards)<BR>> <BR>> If I drop the indices before alter = table,=20 am I just making work for<BR>> myself?... Does adding rowid change = the=20 indices in any way (I could just<BR>> re-stat the table)? = It will=20 take 1-2 hours to recreate the indices.<BR>> <BR>> Table is 40 GB, = (4=20 frags, round robin).