How can I speed up the 'drop index' command?
I'm copying tables from one disk to another (faster disks, extent consolidation). I rename the old tables to keep them around for a few days until I'm sure the copies are okay. I have to create indexes on the new copy using the same names as the indexes on the old table, so I have to drop the indexes on the old table first. Some of the tables are > 2Gb in size and have 5+ indexes - is there any way to speed up dropping the old indexes? Anything I can change in my onconfig file? TIA Duane Sent via Deja.com http://www.deja.com/ Before you buy.
Hi Its the first time for me posting a question to the group, so here goes. Running IDS7.31 with Enterprise Replication active, how can I export my databases without having to shutdown replication. Cheers Folks h.straker@videonetworks.com
In article <88h4b1$7m7$1@nnrp1.deja.com>,
duane_hakala@my-deja.com wrote:
> I'm copying tables from one disk to another (faster disks, extent
> consolidation). I rename the old tables to keep them around for a few
> days until I'm sure the copies are okay.
>
> I have to create indexes on the new copy using the same names as the
> indexes on the old table, so I have to drop the indexes on the old
> table first. Some of the tables are >2Gb in size and have 5+ indexes.
> is there any way to speed up dropping the old indexes? Anything I can
> change in my onconfig file?
Duane,
I have never seen anything in the ONCONFIG that would speed up an index
drop. Sorry I don't have a concrete answer for you.
Speculation: I wonder if it is any faster to drop a disabled index than
an enabled index.
set indexes y_ix disabled;
drop index y_ix;
My tentative plan to try is to disable it before dropping it, in the
hope that this would free up those index pages and perhaps not lock the
table while dropping it.
Some years ago, Art entered a feature request with Informix regarding
dropping in index this way, so that a drop index command could be
effective immediately while a b-tree cleaner (or similar thread) starts
mulching along - asynchronously WRT any other threads - to turn the
pages of the dropped index into unused pages. The user would not have
to be locked out of the table meantime.
I am guessing that the idea died of non-benign neglect. (IMO, Art's
ideas should NEVER be simply ignored!)
--
+----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+
|------------------- Bulletin Board Announcement ----------------------|
| Congregants will please note that the bowl at the back of the church |
| bearing the sign "For the Sick" is for monetary contributions only. |
+----------------------------------------------------------------------+
Sent via Deja.com http://www.deja.com/
Before you buy.
Henry You need to post your message as a new thread, not as a reply to someone else's query. regards Neil Henry Straker wrote in message <88h6ie$glv$1@starburst.uk.insnet.net>... >Hi > >Its the first time for me posting a question to the group, so here goes. > >Running IDS7.31 with Enterprise Replication active, how can I export my >databases without having to shutdown replication. > >Cheers Folks > >h.straker@videonetworks.com > >
duane_hakala@my-deja.com wrote: > > I'm copying tables from one disk to another (faster disks, extent > consolidation). I rename the old tables to keep them around for a few > days until I'm sure the copies are okay. > > I have to create indexes on the new copy using the same names as the > indexes on the old table, so I have to drop the indexes on the old > table first. Some of the tables are > 2Gb in size and have 5+ indexes - > is there any way to speed up dropping the old indexes? Anything I can > change in my onconfig file? The problem is the time needed to mark all the index pages free when they are interleaved with the data pages. If you detach the indexes their pages will be contiguous and dropping them becomes a DROP TBLSPACE operation which is much faster. -- Art S. Kagel & Family kagel@erols.com
Jacob Salomon wrote:
>
> In article <88h4b1$7m7$1@nnrp1.deja.com>,
> duane_hakala@my-deja.com wrote:
> > I'm copying tables from one disk to another (faster disks, extent
> > consolidation). I rename the old tables to keep them around for a few
> > days until I'm sure the copies are okay.
> >
> > I have to create indexes on the new copy using the same names as the
> > indexes on the old table, so I have to drop the indexes on the old
> > table first. Some of the tables are >2Gb in size and have 5+ indexes.
> > is there any way to speed up dropping the old indexes? Anything I can
> > change in my onconfig file?
>
> Duane,
>
> I have never seen anything in the ONCONFIG that would speed up an index
> drop. Sorry I don't have a concrete answer for you.
>
> Speculation: I wonder if it is any faster to drop a disabled index than
> an enabled index.
> set indexes y_ix disabled;
> drop index y_ix;>
> My tentative plan to try is to disable it before dropping it, in the
> hope that this would free up those index pages and perhaps not lock the
> table while dropping it.
>
> Some years ago, Art entered a feature request with Informix regarding
> dropping in index this way, so that a drop index command could be
> effective immediately while a b-tree cleaner (or similar thread) starts
> mulching along - asynchronously WRT any other threads - to turn the
> pages of the dropped index into unused pages. The user would not have
> to be locked out of the table meantime.
>
> I am guessing that the idea died of non-benign neglect. (IMO, Art's
> ideas should NEVER be simply ignored!)
I tend to agree. ;-)
--
Art S. Kagel & Family
kagel@erols.com