On 02-09-2010 23:28, shorti wrote:


Informix automatically manages your indexes. You can configure how it
does it, or even prevent it from doing it, but that part is managed by
the database (as it should).

Regarding the data itself, it can reuse space left free by DELETEs.

Update stats are needed if/when your data profile changes. This doesn't
mean you shouldn't run them periodically...
Update stats can run simultaneously with the normal database activity.
LOCK MODE WAIT <N> as Art wrote is a good practice....

One thing I'm not sure if it was referenced in this thread is the number
of extents in the tables. An extent is a sequential set of pages that
belong to a table, index or partition. The maximum number of extents in
Informix is very low (specially in 2K pages). This is NOT a problem if
you plan well and monitor it regularly. Again, Informix does a pretty
nice job to avoid that the tables reach an high number of extents. An
online REORG is a request from customer that ever met a situation where
a table reaches the maximum number of tables. That is a nasty situation
for a 24x7 system, but we have to say it will only happen if you neglect
your duties as a DBA. Nevertheless let's hope for good news in the
upcoming releases related to these points.

Currently you have REPACK and SHRINK. These can work online, but it may
affect queries that do full scans (usually we don't like those in OLTP
systems), so anyone using them online must be aware of this.

We can create indexes online, but the problem with that related to your
concerns, is that the index may not exist. So if you think about this to
"reorg" and index, you'd have to drop it first. And this would of course
impact the query plans... Again, you don't need to REORG them...

Well, in fact, we saw a situation a few months ago here, where the
current "state" of the index made the running of update stats very slow.
Note that it did not affect the queries... Only a full index scan like
the one done by the LOW option... And there was a workaround for it...


So, in short, don't worry too much about not being able to do something
you're used to do, because that may not be necessary in Informix.

Again, years of experience and I never do "reorgs". I recall having a
performance issue some years ago and I did test it in a "lab"... I
recreated all the indexes did several measures and it did not help...
What would have help appeared years later in V11

Regards.

You obviously never looked at anything I designed or implemented!! :-D