RE: Adding rowids to fragmented table

Norma Jean Be careful doing an inplace alter, if this is a logged database you will run out of log space (unless you have 80 Gb plus !!) 'cos every record will be rewritten with 4 more bytes per record so you are to start to need more pages (depending on row size, page size). Do you have the opportunity to turn logging off on the db while you do this? If not you will have to alter type to raw or static first (but I'm not sure if you can then add row ids !!). A better method (and one which will give other gains) is to unload the data, drop, recreate the table with rowids and reload then reindex. Alternatively, if you have the dbspace available, rname the table, drop all except the PK index, rename this then unload from the old table to a pipe and use dbload to reload to a new table. use the PK to kick off several exclusive selects for the unload and you should be able to whistle through this. Good Luck Keith -----Original Message----- From: Sebastian, Norma J. [mailto:NormaJean.Sebastian@tellabs.com] Sent: Tuesday, November 02, 2004 17:23 To: informix-list@iiug.org Subject: Adding rowids to fragmented table Hi, When adding rowids to a frag'd table, should I drop all the indices first before altering the table? (the rebuild indices afterwards) 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. Table is 40 GB, (4 frags, round robin). 4 indices taking about 6 GB (total, not each). I'll test multiple ways if project gives me the time. 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... Thanks, Norma Jean ----------------------------------------- ============================================================ 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 ============================================================ sending to informix-list ********************************************************************************** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ********************************************************************************** sending to informix-list