High Extent Tables.
Quick question:
1. If I create a clustered index and drop it on a table with high extents,
will that reorganize it and reduce the extents or
2. Do I have to unload the data, drop the table, re-create the table, and
re-load the data or
3. Should I rename the original table to another name and re-create the table
under the original name and copy the data from the renamed table to the
re-created table?
Which is the better way to reduce extents on a table? I don't want to loose
table connections to synonyms, views, triggers, or stored procedures by
dropping the table.
I can run a dbschema for the above objects, but I would have to separate them
from the full list, which is possible.
Thanks,
**************************************
Ernie Knox
Sears Holding Co.
IT Database Administrator Specialist
IT Service Management, Strategy & Architecture
3333 Beverly Rd., B4-266A
Hoffman Estates, IL. 60179
Office: (847) 286-5735
Fax: (847) 645-3874
Pager: (800) 759-8352 Pin#: 7271042
Email: eknox@sears.com
" It's always a great day to watch Football ! "
**************************************
Ernie,
Please post your informix version, o/s, and table sizes.
Any of your options would work as you know, but perhaps the experts out
here will vary their suggestions based on your table size, version,
outage window limitations, etc..
For example, I had been playing with table fragmentation, so I could
also see running an "init frag" on the table to move it to new space
thereby achieving the extent reorg/reduction you desire. Fragmentation
was just my latest "fun thing"...=20
It's informix, it's all fun.
Thanks,
Norma Jean
=20
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On=20
> Behalf Of Knox, Ernest
> Sent: Thursday, February 14, 2008 4:05 PM
> To: ids@iiug.org
> Subject: High Extent Tables. [11315]
>=20
> Quick question:=20
>=20
> 1. If I create a clustered index and drop it on a table with=20
> high extents,=20
> will that reorganize it and reduce the extents or=20
>=20
> 2. Do I have to unload the data, drop the table, re-create=20
> the table, and=20
> re-load the data or=20
>=20
> 3. Should I rename the original table to another name and=20
> re-create the table=20
> under the original name and copy the data from the renamed=20
> table to the=20
> re-created table?=20
>=20
> Which is the better way to reduce extents on a table? I don't=20
> want to loose=20
> table connections to synonyms, views, triggers, or stored=20
> procedures by=20
> dropping the table.=20
>=20
> I can run a dbschema for the above objects, but I would have=20
> to separate them=20
> from the full list, which is possible.=20
>=20
> Thanks,=20
>=20
> **************************************=20
> Ernie Knox=20
> Sears Holding Co.=20
> IT Database Administrator Specialist=20
> IT Service Management, Strategy & Architecture=20
> 3333 Beverly Rd., B4-266A=20
> Hoffman Estates, IL. 60179=20
> Office: (847) 286-5735=20
> Fax: (847) 645-3874=20
> Pager: (800) 759-8352 Pin#: 7271042=20
> Email: eknox@sears.com=20
>=20
> " It's always a great day to watch Football ! "=20
> **************************************=20
>=20
>=20
> **************************************************************
> *****************=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>=20
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!=20
>=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
The information contained in this message may be privileged
and confidential and protected from disclosure. If the reader
of this message is not the intended recipient, or an employee
or agent responsible for delivering this message to the
intended recipient, you are hereby notified that any reproduction,
dissemination or distribution of this communication is strictly
prohibited. If you have received this communication in error,
please notify us immediately by replying to the message and
deleting it from your computer. Thank you. Tellabs
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Sorry,
This is for OSLEVEL: SUN 5.8, IDS 7.31.UD8, using HDR. 9 tables have up 300
thousand rows, while 13 tables have less than 100 thousand rows. Extremely
small application data.
Nothing is fragmented.
I can increase the first extent size if I re-create the table, but wanted to
avoid that method, if necessary.
I wanted some thoughts from others who are performing high extent reorgs, such
as yourself.
Thanks,
**************************************
Ernie Knox
Sears Holding Co.
IT Database Administrator Specialist
IT Service Management, Strategy & Architecture
3333 Beverly Rd., B4-266A
Hoffman Estates, IL. 60179
Office: (847) 286-5735
Fax: (847) 645-3874
Pager: (800) 759-8352 Pin#: 7271042
Email: eknox@sears.com
" It's always a great day to watch Football ! "
**************************************
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Sebastian, Norma J.
Sent: Thursday, February 14, 2008 4:46 PM
To: ids@iiug.org
Subject: RE: High Extent Tables. [11317]
Ernie,
Please post your informix version, o/s, and table sizes.
Any of your options would work as you know, but perhaps the experts out
here will vary their suggestions based on your table size, version,
outage window limitations, etc..
For example, I had been playing with table fragmentation, so I could
also see running an "init frag" on the table to move it to new space
thereby achieving the extent reorg/reduction you desire. Fragmentation
was just my latest "fun thing"...=20
It's informix, it's all fun.
Thanks,
Norma Jean
=20
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On=20
> Behalf Of Knox, Ernest
> Sent: Thursday, February 14, 2008 4:05 PM
> To: ids@iiug.org
> Subject: High Extent Tables. [11315]
>=20
> Quick question:=20
>=20
> 1. If I create a clustered index and drop it on a table with=20
> high extents,=20
> will that reorganize it and reduce the extents or=20
>=20
> 2. Do I have to unload the data, drop the table, re-create=20
> the table, and=20
> re-load the data or=20
>=20
> 3. Should I rename the original table to another name and=20
> re-create the table=20
> under the original name and copy the data from the renamed=20
> table to the=20
> re-created table?=20
>=20
> Which is the better way to reduce extents on a table? I don't=20
> want to loose=20
> table connections to synonyms, views, triggers, or stored=20
> procedures by=20
> dropping the table.=20
>=20
> I can run a dbschema for the above objects, but I would have=20
> to separate them=20
> from the full list, which is possible.=20
>=20
> Thanks,=20
>=20
> **************************************=20
> Ernie Knox=20
> Sears Holding Co.=20
> IT Database Administrator Specialist=20
> IT Service Management, Strategy & Architecture=20
> 3333 Beverly Rd., B4-266A=20
> Hoffman Estates, IL. 60179=20
> Office: (847) 286-5735=20
> Fax: (847) 645-3874=20
> Pager: (800) 759-8352 Pin#: 7271042=20
> Email: eknox@sears.com=20
>=20
> " It's always a great day to watch Football ! "=20
> **************************************=20
>=20
>=20
> **************************************************************
> *****************=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>=20
> See you at the IIUG Informix 2008 Conference
> The Power Conference for Informix Professionals
> April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
> http://www.iiug.org/conf
> Registration Now Open!!=20
>=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
The information contained in this message may be privileged
and confidential and protected from disclosure. If the reader
of this message is not the intended recipient, or an employee
or agent responsible for delivering this message to the
intended recipient, you are hereby notified that any reproduction,
dissemination or distribution of this communication is strictly
prohibited. If you have received this communication in error,
please notify us immediately by replying to the message and
deleting it from your computer. Thank you. Tellabs
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!
Knox, Ernest wrote:
Ernest,
There are four basic methods for reorganizing a table to compress the
extents, you've listed three. Personally I prefer #4 when it's practical:
1- Cluster any index on the table (or create a clustered index just for
this and drop it). For performance clustering on a main key will help
performance anyway. Adjust the NEXT SIZE prior to the reorg.
2- Unload the table, drop it, recreate it with a larger initial and next
extent sizing, reload the data.
3- Create a new table with the proper extent sizing, copy the data,
rename the old table, rename the new table, drop the original table.
4- ALTER FRAGMENT FOR mytable INIT IN <dbspacename or fragmentation
expression>; This will work whether or not the original or new location
of the table involves fragmenting it and whether you are moving the
table to another dbspace or just reorging it into the same dbspace where
it already resides. Adjust the NEXT SIZE prior to the reorg.
Only methods 1 & 4 will preserve VIEWS, SYNONYMs, etc. based on the
table and neither of these methods incur the overhead of re-verifying
any foreign keys in the table or that reference it. On the down side it
is more likely that methods 1 & 4 may result in a small number of
extents instead of a single large one than the other methods.
Art S. Kagel
Oninit
> Sorry,
>
> This is for OSLEVEL: SUN 5.8, IDS 7.31.UD8, using HDR. 9 tables have up 300
> thousand rows, while 13 tables have less than 100 thousand rows. Extremely
> small application data.
>
> Nothing is fragmented.
>
> I can increase the first extent size if I re-create the table, but wanted to
> avoid that method, if necessary.
>
> I wanted some thoughts from others who are performing high extent reorgs,
such
> as yourself.
>
> Thanks,
>
> **************************************
> Ernie Knox
> Sears Holding Co.
> IT Database Administrator Specialist
> IT Service Management, Strategy & Architecture
> 3333 Beverly Rd., B4-266A
> Hoffman Estates, IL. 60179
> Office: (847) 286-5735
> Fax: (847) 645-3874
> Pager: (800) 759-8352 Pin#: 7271042
> Email: eknox@sears.com
>
> " It's always a great day to watch Football ! "
> **************************************
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Sebastian, Norma J.
> Sent: Thursday, February 14, 2008 4:46 PM
> To: ids@iiug.org
> Subject: RE: High Extent Tables. [11317]
>
> Ernie,
> Please post your informix version, o/s, and table sizes.
> Any of your options would work as you know, but perhaps the experts out
> here will vary their suggestions based on your table size, version,
> outage window limitations, etc..
> For example, I had been playing with table fragmentation, so I could
> also see running an "init frag" on the table to move it to new space
> thereby achieving the extent reorg/reduction you desire. Fragmentation
> was just my latest "fun thing"...=20
> It's informix, it's all fun.
> Thanks,
> Norma Jean
> =20
>
>
>> -----Original Message-----
>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On=20
>> Behalf Of Knox, Ernest
>> Sent: Thursday, February 14, 2008 4:05 PM
>> To: ids@iiug.org
>> Subject: High Extent Tables. [11315]
>> =20
>> Quick question:=20
>> =20
>> 1. If I create a clustered index and drop it on a table with=20
>> high extents,=20
>> will that reorganize it and reduce the extents or=20
>> =20
>> 2. Do I have to unload the data, drop the table, re-create=20
>> the table, and=20
>> re-load the data or=20
>> =20
>> 3. Should I rename the original table to another name and=20
>> re-create the table=20
>> under the original name and copy the data from the renamed=20
>> table to the=20
>> re-created table?=20
>> =20
>> Which is the better way to reduce extents on a table? I don't=20
>> want to loose=20
>> table connections to synonyms, views, triggers, or stored=20
>> procedures by=20
>> dropping the table.=20
>> =20
>> I can run a dbschema for the above objects, but I would have=20
>> to separate them=20
>> from the full list, which is possible.=20
>> =20
>> Thanks,=20
>> =20
>> **************************************=20
>> Ernie Knox=20
>> Sears Holding Co.=20
>> IT Database Administrator Specialist=20
>> IT Service Management, Strategy & Architecture=20
>> 3333 Beverly Rd., B4-266A=20
>> Hoffman Estates, IL. 60179=20
>> Office: (847) 286-5735=20
>> Fax: (847) 645-3874=20
>> Pager: (800) 759-8352 Pin#: 7271042=20
>> Email: eknox@sears.com=20
>
================================================================================
===========
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.oninit.com/home/disclaimer.php
================================================================================
===========
I believe this is written in a manual somewhere:
If you are doing ALTER FRAGMENT ... INIT, perform an oncheck -cD on the
table first to check for any issues. If you run the alter fragment ... init
and it aborts, your (perhaps only) recovery method is to restore from
archive.
Take care.
Clifton M. Bean
Informix DBA / AIX System Admin
Currency Technics & Metrics
Phone: (972) 812-1411 x244
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art S.
Kagel (Oninit LLC)
Sent: Thursday, February 14, 2008 5:27 PM
To: ids@iiug.org
Subject: Re: High Extent Tables. [11319]
Knox, Ernest wrote:
Ernest,
There are four basic methods for reorganizing a table to compress the
extents, you've listed three. Personally I prefer #4 when it's practical:
1- Cluster any index on the table (or create a clustered index just for
this and drop it). For performance clustering on a main key will help
performance anyway. Adjust the NEXT SIZE prior to the reorg.
2- Unload the table, drop it, recreate it with a larger initial and next
extent sizing, reload the data.
3- Create a new table with the proper extent sizing, copy the data,
rename the old table, rename the new table, drop the original table.
4- ALTER FRAGMENT FOR mytable INIT IN <dbspacename or fragmentation
expression>; This will work whether or not the original or new location
of the table involves fragmenting it and whether you are moving the
table to another dbspace or just reorging it into the same dbspace where
it already resides. Adjust the NEXT SIZE prior to the reorg.
Only methods 1 & 4 will preserve VIEWS, SYNONYMs, etc. based on the
table and neither of these methods incur the overhead of re-verifying
any foreign keys in the table or that reference it. On the down side it
is more likely that methods 1 & 4 may result in a small number of
extents instead of a single large one than the other methods.
Art S. Kagel
Oninit
> Sorry,
>
> This is for OSLEVEL: SUN 5.8, IDS 7.31.UD8, using HDR. 9 tables have up
300
> thousand rows, while 13 tables have less than 100 thousand rows. Extremely
> small application data.
>
> Nothing is fragmented.
>
> I can increase the first extent size if I re-create the table, but wanted
to
> avoid that method, if necessary.
>
> I wanted some thoughts from others who are performing high extent reorgs,
such
> as yourself.
>
> Thanks,
>
> **************************************
> Ernie Knox
> Sears Holding Co.
> IT Database Administrator Specialist
> IT Service Management, Strategy & Architecture
> 3333 Beverly Rd., B4-266A
> Hoffman Estates, IL. 60179
> Office: (847) 286-5735
> Fax: (847) 645-3874
> Pager: (800) 759-8352 Pin#: 7271042
> Email: eknox@sears.com
>
> " It's always a great day to watch Football ! "
> **************************************
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Sebastian, Norma J.
> Sent: Thursday, February 14, 2008 4:46 PM
> To: ids@iiug.org
> Subject: RE: High Extent Tables. [11317]
>
> Ernie,
> Please post your informix version, o/s, and table sizes.
> Any of your options would work as you know, but perhaps the experts out
> here will vary their suggestions based on your table size, version,
> outage window limitations, etc..
> For example, I had been playing with table fragmentation, so I could
> also see running an "init frag" on the table to move it to new space
> thereby achieving the extent reorg/reduction you desire. Fragmentation
> was just my latest "fun thing"...=20
> It's informix, it's all fun.
> Thanks,
> Norma Jean
> =20
>
>
>> -----Original Message-----
>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On=20
>> Behalf Of Knox, Ernest
>> Sent: Thursday, February 14, 2008 4:05 PM
>> To: ids@iiug.org
>> Subject: High Extent Tables. [11315]
>> =20
>> Quick question:=20
>> =20
>> 1. If I create a clustered index and drop it on a table with=20
>> high extents,=20
>> will that reorganize it and reduce the extents or=20
>> =20
>> 2. Do I have to unload the data, drop the table, re-create=20
>> the table, and=20
>> re-load the data or=20
>> =20
>> 3. Should I rename the original table to another name and=20
>> re-create the table=20
>> under the original name and copy the data from the renamed=20
>> table to the=20
>> re-created table?=20
>> =20
>> Which is the better way to reduce extents on a table? I don't=20
>> want to loose=20
>> table connections to synonyms, views, triggers, or stored=20
>> procedures by=20
>> dropping the table.=20
>> =20
>> I can run a dbschema for the above objects, but I would have=20
>> to separate them=20
>> from the full list, which is possible.=20
>> =20
>> Thanks,=20
>> =20
>> **************************************=20
>> Ernie Knox=20
>> Sears Holding Co.=20
>> IT Database Administrator Specialist=20
>> IT Service Management, Strategy & Architecture=20
>> 3333 Beverly Rd., B4-266A=20
>> Hoffman Estates, IL. 60179=20
>> Office: (847) 286-5735=20
>> Fax: (847) 645-3874=20
>> Pager: (800) 759-8352 Pin#: 7271042=20
>> Email: eknox@sears.com=20
>
============================================================================
===============
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.oninit.com/home/disclaimer.php
============================================================================
===============
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
See you at the IIUG Informix 2008 Conference
The Power Conference for Informix Professionals
April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas
http://www.iiug.org/conf
Registration Now Open!!