I have a database into which I am inserting a huge amount broken up into a thousand chunks. Each insert the index is updated (which takes insanely long after around the 100th chunk or so), having the journal file grow to over 300 MB. Is there a way to postpone updating indices (possibly with a FREEZE INDEX command) whereby the index would stay defined in the schema, and after all tables have been populated it may be unfrozen and updated?
This can be accomplished by DROP INDEX and CREATE INDEX, but I was looking for a more elegant solution. I cannot change DBMS's.
Apart from it not being "elegant" is there any other reason why you do not want to use DROP INDEX/CREATE INDEX. I assume you are doing this bulk load during a scheduled maintenance? DROP INDEX/CREATE INDEX is pretty much standard procedure in bulk data loads. By recreating the non-clustered indexes at the end you ensure that the DB can create the most efficient index structure (thus making queries against the data run faster).
Some databases have additional features to hide these details from you (SQL server allows you to "disable/enable" an index) but behind the scenes they are still effectively executing DROP INDEX/CREATE INDEX.
Again, you are not saving anything even if there were a "FREEZE INDEX". If you are entering a lot of data it would be best to completely rebuild the index at the end anyway (which effectively acts like a "DROP INDEX/CREATE INDEX").
If your journal is getting too big perhaps you should split the insert into even smaller transactional chunks?