Quote:
> > > Hi...
> > > Technical informix questions:
> > > 1. When you have a CLUSTERED index and do an UPDATE,
> > > does informix perform a 'update in place' of the data
> > > space or does it perform a DELETE/INSERT pair?
> > Informix updates rows in place unless the row contains a VARCHAR column that
> > grows with the update such that it no longer fits on its home page. In the
> > latter case the row is moved to another page and a forwarding pointer is
> > placed in its slot on the original location, but the indexes still point
> > to the original slot.
> Therefore it would be a good idea to define a MINIMUM value
> for the VARCHAR columns as well. And an additional question
Yes it is, Informix will reserve the MINIMUM size so that there will be
fewer rows per page and fewer rows will have to be relocated.
Quote:
> to Joe, will you update the indexed columns or will you update
> other columns ?
> > > 2. If you have a table which will have a very high
> > > percentage of INSERTS but MAYBE a worst case of 2%
> > > DELETEs/UPDATEs, is there a penalty associated with having
> > > a CLUSTERED INDEX IFF the data is being INSERTed into
> > > the table in ascending key order all the time?
> > No penalty but little advantage from the ordering of the data either. If
> > the data is inserted in ascending order, according to the index, it does
> > not guarantee that the table will maintain its clusteredness. Because
> > Informix selects pages onto which to insert rows using the LRU queues, it
> > is likely that two threads inserting rows, or even sequential rows inserted
> > by a single thread, will select different pages onto which to append the
> > new row.
> But it's only possible to enter new rows into a page if the page
> itself has enough free space inside. If there's only one page inside
Or if the page is clean, ie no rows on it yet at all.
Quote:
> the table with enough free space for the new row, this page will be
> used. Normally you will have several pages with room inside if you'd use
> a flexible row-size ( varchar's ). Otherwise it's only possible to have
> pages with room if you remove some rows ( DELETE ).
No, since additional extents will be added as needed as long as the dbspace
has room and the fragment/table has fewer than 16million rows. So data can
always be added to unused pages in an existing extent or a new extent can
be allocated.
Quote:
> > The position of data inserted is not really random but will
> > appear so and will not perform for sequential scans as well as the table
> > will if the clustered index is reclustered. Inserting data in a strictly
> > ascending manner it will take longer (ie more new rows) before any
> > performance degradation is noticeable or severe than if data with keys all
> > over the map were being inserted but there will be some degradation
> > eventually. A larger BUFFER cache and good RA parameter settings can
> > minimize the effects further as can table RESIDENCY for smaller tables.
> ---> in other words, use CLUSTER INDEXES just for large tables, where
> "large" means more than 50% of the total Buffer Pool size.
WHAT? I don't get that at all?!!?!? Clustering is best used for tables
that are fairly static or where most sequential scans will involve only
older data. You can expand the number of tables for which it is appropriate
if you can afford to recluster the index frequently enough that you are
still gaining from its use.
Art S. Kagel
Clustering and indexes