Trun Off Indexes!
Sherwin asked whether all indexes on an Informix table could be turned off during a bulk load and turned on again later without dropping and recreating them. Yuri Dovgart thought it was probably impossible. Kevin Hayward said SET INDEXES FOR table DISABLED / ENABLED works, with indexes updated on enable. Art S. Kagel argued enabling must rebuild the index anyway, so no true shortcut exists. Not fully settled.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Can all indexes on a table in an Informix Database be turned off and then turned on again later. I need to speed up loading of data into my tables. But I don't wana drop and recreate my indexes, neither do I want them slowing down my data insertion. sherwin
Probably, there is no way to do so. In article <374E9293.4C1D5EFA@blr.sni.de>, sherwin@blr.sni.de wrote: > Can all indexes on a table in an Informix Database be turned off and > then turned on again later. I need to speed up loading of data into my > tables. But I don't wana drop and recreate my indexes, neither do I want > them slowing down my data insertion. > > sherwin > > -- With best regards, Yuri Dovgart. Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.
Bulk Loading data is much faster with out constraints, triggers and Indexes. You can disable these with a set command. When you then enable after your load the constraints are checked and the indexes are updated. I have done the disable and enable indexes but have never had enough faith in the data I was loading to disable constraints and triggers. Set indexes for [table name] disabled; (Load your data) Set indexes for [table name] enabled; Good Luck Kevin Hayward -----Original Message----- From: Sherwin [mailto:sherwin@blr.sni.de] Posted At: Friday, May 28, 1999 5:57 AM Posted To: informix Conversation: Trun Off Indexes! Subject: Trun Off Indexes! Can all indexes on a table in an Informix Database be turned off and then turned on again later. I need to speed up loading of data into my tables. But I don't wana drop and recreate my indexes, neither do I want them slowing down my data insertion. sherwin
Sherwin wrote: > > Can all indexes on a table in an Informix Database be turned off and > then turned on again later. I need to speed up loading of data into my > tables. But I don't wana drop and recreate my indexes, neither do I want > them slowing down my data insertion. Think about what you just said! If you turn off index maintenance during a load and then turn it on, and turning it on does not involve rebuilding the index (ala dropping and recreating it) then how are you going to reach all those new rows using that outdated index that does not have those rows listed? So the answer is no, and it cannot be done. You can make a disable indexes command that PRETENDS to do this but the ENABLE INDEX command has to do the rebuild anyway! Art S. Kagel