RE: rebuilding indexes

> -----Original Message----- > From: William Rice [SMTP:ricew@operamail.com] > Sent: Monday, April 17, 2000 3:16 PM > To: David Palmer; informix-list > Subject: RE: rebuilding indexes > > set <database object> disabled; > set <database object> enabled; > might achieve what you are trying to do. > [Arshad, Shehla] Might achieve or will achieve? We were doing disable/enable indexes/constraints and were told that it doesn't re-build any indexes or constraints. I would like to go back to this strategy if someone can confirm that it actually re-builds the indexes. Thx. > Will > > >===== Original Message From "David Palmer" <palmerd@billings.k12.mt.us> > ===== > >In 7.3, are there other ways to rebuild indexes other than dropping and > >re-creating them? > > > >- David > > ------------------------------------------------------------ > This e-mail has been sent to you courtesy of OperaMail, as a free > service from > Opera Software, makers of the award-winning Web Browser, Opera. Visit > us at > http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free > e-mail > account is waiting at: http://www.operamail.com/ > ------------------------------------------------------------

> > [Arshad, Shehla] Might achieve or will achieve? We were doing > disable/enable indexes/constraints and were told that it doesn't re-build > any indexes or constraints. I would like to go back to this strategy if > someone can confirm that it actually re-builds the indexes. Thx. > This is extremely simple to test. 1. Create a table with sufficient data (say, 100,000 rows). Or you could use an existing table in a test instance. 2. Create a detached index on this table. 3. Examine sysmaster.sysextents to determine the space and extents used by the index. Also note onstat -d's output. 4. Disable the index. 5. Note that the index no longer occupies any space - no rows in sysextents; onstat -d report more space available. However, its definition continues toexist in the "sys" tables. 6. Enable the index. Monitor the process. Note the similarity in the various stages of the process to that of an Index build. 7. Note that the index now occupies space. Finally, you could determine that the actual location (extents taken) of the index after the disable/enable could be different. To attempt this, create an entirely new index in the same dbspace after disabling the index but before enabling the index. Rudy