[log in to unmask] wrote in article ...

>Item Subject: cc:Mail Text
>If this is considered a waste of bandwidth, please let me know.
>In keeping with the discussions of Mars, here is something else
>from another world... ;)
>
>
>Informix permits what they call "clustered indexes." While indices
>are comparable to Image paths, a "clustered index" is somewhat related
>to a primary key under Image. (It took me the longest time to realize
>that the primary key is NOT the key which is used most often, but the key
>for which it makes the most sense to group identical values.) As with
>primary keys, there can be only one clustered index per table (set).
>However, there can be indices with NONE being clustered.
>

Informix clustered indexes caluse the physical placement of data on disk to
be in an indexed order.  These indexes are not updated when rows are added
or updated.  It's been a while since I have done anything with TurboImage
but isn't this closer to TurboImage's sorted keys (except that new records
are
also created in sorted order and Turbo does all of it's ordering with
pointers and
not by reordering the detail dataset).

>Just as Image only groups these keys upon dbunload/dbload (or with the
>application of a third-party tool like Adager, in which case you can
>group by a NON-primary key), Informix enforces the proximity only when
>the index is "re-clustered."
>
>
>One developer was not convinced that one of the indices on a particular
>table was populated correctly. (I think he was mistaken and the index was
>fine, but I don't have enough experience with Informix to say this with
>confidence.) So, I told Informix NOT to cluster this index, then to cluster
>it again.
>
>"Not clustering" was handled fine, and quickly. (I'm sure it just reset
>a flag.) However, in trying to re-cluster the index (copying the entire
>table in the process), it ran out of disk space. This, after a half hour
>of processing. (This makes me appreciate that much more the intelligent
>tools we have on the 3000.) Now the flag is still set to "no cluster"
>for that index, even though transactions will be handled no differently
>from how they were when I started.
>

When the ALTER INDEX TO CLUSTER statement is executed, Informix will
make a copy of the entire table on disk in the order of the index before
dropping
the old index.  You must have sufficient space available in the dbspace to
hold a
copy of the entire table.

>I went back and re-read the documentation today. Rather than issuing both
>ALTER ... TO NOT CLUSTER and ALTER ... TO CLUSTER commands, it looks like
>I needed to enter only the latter. Seems strange, though, to enable what
>is already enabled.
>
>
>Incidentally, it appears there is no "delete chain." When a record is
>deleted, it is just flagged as such. When a record is added, it goes to
>the end of the table. To get rid of the "holes" caused by deletions,
>you need to cluster an index, to force the copying of non-deleted records
>to a new table, which then replaces the current table. Go figure.
>

When an item is deleted, there is a delete flag that is set.  When the
transaction is
committed, a request to the delete the item is placed in a pool in shared
memory
called the btree cleaner pool.  The "btcleaner" thread is responsible for
the updating
the information in the table space structures, which include a bit map that
reflects
the free pages in the extents used by the table.

From some testing that I have done, Informix will try and use free pages in
the
extents before allocating new extents to the table.  If you have a test
environment,
or you can uses the stores7 database, try the following:

dbaccess stores7 <<EOD
select tabname, partnum
    from systables
  where tabname not like 'sys%'
     and nrows > 0;
EOD

Choose a table to test, I used the customer table from the stores7 database,
using the partnum field from the first query build a command file that looks
similar
to this:

echo "Table Page Headers:"
dbaccess sysmaster <<EOD
select pg_pagenum, pg_nslots, pg_flags, pg_frcnt
  from systabpaghdrs
  where pg_partnum = <the partnum from the first query>
EOD

echo "Table Extents"
dbaccess sysmaster <<EOD
select * from systabextents where te_partnum =<the partnum from the first
query>
EOD

In my testing I performed the following:
1) executed the prior script to have documentation of the starting point
2) added data to the table until there were extents added (which can be
verified with the prior script).
3) saved a copy of the output of the script showing the new extents and all
pages
4) deleted a range  of records (from the middle of the table), executed and
saved the output
     from the script file
5) add some records

What happed in this test was that Informix reused that pages that were freed
during the delete.  No
new pages or extents were allocated.


Regards,
Barry Lemrow
Hewlett-Packard