I think that this information should be recorded somewhere in the
sysmaster database. When I get the chance I will look into the
sysextents table. If the information is not there maybe we should make
this a priority request for a future release.

I am currently planning a very tricky migration that is primarily
intended to correct mistakes from a previous installation, and one of
those mistakes was that every table has default extent sizes. Some of
those tables have more than 150 extents. One table has extent sizes
that get bigger to begin with but as the chunks get fuller the extents
seem to get smaller. I understand why as the site does a lot of
deleting tables and then recreating them which leaves a very fragmented
dbspace. I intend to keep a much tighter rein on creating tables with
this migration and will definitely set extent sizes for each table.

Have any of you realised why - no matter what you do about indexes -
tables that have a unique column always use multiple extents. It's
because the unique column uses an index and the index is detached by
default. And it's mostly those tables that have large numbers of
extents.

Regards

Malcolm

-----Original Message-----
From: owner-informix-list@iiug.org [mailtowner-informix-list@iiug.org]
On Behalf Of Dirk Moolman
Sent: 08 November 2004 11:08
To: informix-list@iiug.org
Subject: When table extent was added


Is it possible to see from the system tables when a table extent was
added ?

I have a couple of tables with large extents, and want to be able to see
when the extents were added to them.


IDS 7.31 FD3
Solaris 9





Dirk Moolman
Database and Unix Administrator
MXGROUP


"People demand freedom of speech as a compensation for the freedom of
thought which they seldom use."
-Kierkegaard


sending to informix-list

sending to informix-list

malcolm weallans wrote:
>

[ ... snip ... ]

>
> Have any of you realised why - no matter what you do about indexes -
> tables that have a unique column always use multiple extents. It's
> because the unique column uses an index and the index is detached by
> default. And it's mostly those tables that have large numbers of
> extents.

Hi Malcolm,

This is what we do to avoid having >1 extent per fragment
on reloading during a table reorg:

Instead of the primary key constraint inside the
create table statement, we create a respective unique index
and use
ALTER TABLE ... ADD CONSTRAINT sentence.

If you keep the sequence so, that all other indexes are
created afterwards, ALTER TABLE uses near to no time.

CREATE TABLE ..... (without primary constraint word)
CREATE UNIQUE INDEX .....
ALTER TABLE ... ADD CONSTRAINT ....
CREATE INDEX for all other indexes

Another advantage of this method is that the underlying
unique index has a real name now.

dic_k

--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe