Reorg Table daily?

[cmitchell]

Hi,

I am looking for some suggestions as to what i can do to improve the
following situation. If there is more detail you require please ask..

There are users inserting/altering data into a table at a good pace.
Since they have been entering in data i have to perform a reorg daily.
Yesterday the reorgchk utility tells me the F2 and F3 table statistics
are out of their thresholds and places an asterisk under columns F2 and
F3. Also formula F4 had an asterisk for 2 of the indexes on the table.
Last night i did a reorg and it completed successfully. This morning
users are again entering data and when i do a runstats and reorgchk
again the F2 column has an asterisk. And 3 of the indexes also have an
asterisk.

If i bump up the amount of free space associated with the table
(PCTFREE) and with the indexes, will this help? The indexes were
created with a parameter of PCTFREE 10.
If this won't help what else can you suggest i try?
Thanks,
C Mitchell

[Hardy]

if the asterisk always be with you, maybe alter the table append on
will do a little help. that is, reorg is always in need but we optimize
for the insert.

more, is there a cluster index?

[cmitchell]

What do you mean by alter the table append on?
Yes there is 1 clustered index and that is on the primary key.

[Mark A]

"cmitchell" <ch*************@eds.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

Hi,

I am looking for some suggestions as to what i can do to improve the
following situation. If there is more detail you require please ask..

There are users inserting/altering data into a table at a good pace.
Since they have been entering in data i have to perform a reorg daily.
Yesterday the reorgchk utility tells me the F2 and F3 table statistics
are out of their thresholds and places an asterisk under columns F2 and
F3. Also formula F4 had an asterisk for 2 of the indexes on the table.
Last night i did a reorg and it completed successfully. This morning
users are again entering data and when i do a runstats and reorgchk
again the F2 column has an asterisk. And 3 of the indexes also have an
asterisk.

If i bump up the amount of free space associated with the table
(PCTFREE) and with the indexes, will this help? The indexes were
created with a parameter of PCTFREE 10.
If this won't help what else can you suggest i try?
Thanks,
C Mitchell

Increasing PCTFREE will definitely help with indexes (unless an index is the
current timestamp which is always increasing).

For your tables, you need to decide on a clustering sequence (and designate
one index as clustering to determine where the rows will be inserted). If
you do have a clustering index and rows are routinely inserted in the middle
of the table (not always at the end) then increasing PCTFREE may help on
tables also. If PCTFREE is 10 and less than 10% of the table is inserted
weekly, then you only have to reorg weekly.

If you post the DDL for the table and indexes (and provide some explanation
about the table and application if not obvious) I will provide what is
likely the best clustering index to use for the table.

[Private Pyle]

Have you considered making the table an mdc? This should really help
reduce the need for reorg since the data stays clustered based on the
organize by clause.

You can't use organize by for the primary key however. Well, you
could, but you would use one extent per row and waste an amazing amount
of space. You may be able to get away with this if you organize by one
column and the table has a composite key. Never tried this, so I'm not
sure it would work, but I guess it would if the organize by column has
a low cardinality, made unique by the second ( or greater ) column.

Why are you using the primary key for a clustering index? Clustering
makes the most sense when the clustering index is used for range scans.
Most often the pk is used for single row look ups, not range scans.

Adding free space to the table will certainly help.

It's rare that all a tables indexes will all report * free in reorgchk.
It has to do with the cluster being very broken in the data for one
index because the deck is stacked to support the index that is used for
clustering.