Avoiding rebuilding of indexes
--openmail-part-036ca9a9-00000002 Content-Type: text/plain; charset=ISO-8859-1; name="BDY.RTF" Content-Disposition: inline; filename="BDY.RTF" Content-Transfer-Encoding: 8bit Hi, We operate on a database of size 22 GB. For operational efficiency and speed it was suggested that the indexes be dropped and recreated on a weekly basis. Now the problem is that this takes 7 hours, and we dont have that much time. So there are two questions ; 1. Does the rebuilding on a weekly basis help. 2. Is there anyway to speed it up or avoid it. Regards Jinu --openmail-part-036ca9a9-00000002 Content-Type: application/rtf; name="BDY.RTF" Content-Disposition: attachment; filename="BDY.RTF" Content-Transfer-Encoding: base64 e1xydGYxXGFuc2lcYW5zaWNwZzEyNTBcZGVmZjBcZGVmbGFuZzEwNDV7XGZvbnR0Ymwge1xm MFxmc3dpc3NcZnBycTdcZmNoYXJzZXQwIEFyaWFsO317XGYxXGZzd2lzc1xmY2hhcnNldDIz OHtcKlxmbmFtZSBBcmlhbDt9QXJpYWwgQ0U7fX0NClx1YzFccGFyZFxsYW5nMTAzM1x1bG5v bmVcZjBcZnMyMCBIaSxccGFyDQpXZSBvcGVyYXRlIG9uIGEgZGF0YWJhc2Ugb2Ygc2l6ZSAy MiBHQi4gRm9yIG9wZXJhdGlvbmFsIGVmZmljaWVuY3kgYW5kIHNwZWVkIGl0IHdhcyBzdWdn ZXN0ZWQgdGhhdCB0aGUgaW5kZXhlcyBiZSBkcm9wcGVkIGFuZCByZWNyZWF0ZWQgb24gYSB3 ZWVrbHkgYmFzaXMuIE5vdyB0aGUgcHJvYmxlbSBpcyB0aGF0IHRoaXMgdGFrZXMgNyBob3Vy cywgYW5kIHdlIGRvbnQgaGF2ZSB0aGF0IG11Y2ggdGltZS4gU28gdGhlcmUgYXJlIHR3byBx dWVzdGlvbnMgO1xwYXINCjEuIERvZXMgdGhlIHJlYnVpbGRpbmcgb24gYSB3ZWVrbHkgYmFz aXMgaGVscC5ccGFyDQoyLiBJcyB0aGVyZSBhbnl3YXkgdG8gc3BlZWQgaXQgdXAgb3IgYXZv aWQgaXQuXHBhcg0KXHBhcg0KUmVnYXJkc1xwYXINCkppbnVcbGFuZzEwNDVcZjFccGFyDQp9 DQoA --openmail-part-036ca9a9-00000002--
<Jinu.Joseph@citicorp.com>
> We operate on a database of size 22 GB. For operational efficiency
and
> speed it was suggested that the indexes be dropped and recreated on
a
> weekly basis. Now the problem is that this takes 7 hours, and we
dont
> have that much time. So there are two questions ;
> 1. Does the rebuilding on a weekly basis help.
> 2. Is there anyway to speed it up or avoid it.
If you have Informix-Online:
You can do an update statistics (low, med or high) to improve
performance without recreating any index.
From time to time, you can do an dbexport and dbimport (see
www.iiug.org for skripts, that do this faster)
You can alter an index to cluster and not cluster. This reorganise
your Table(s). Look, if you have enough freespace in your dbs!
Backup is recomended!
First PLEASE do not post HTML or MIME to this newgroup it is text only.
No, you do NOT have to rebuild the indexe weekly or monthly or at all.
Informix maintains indexes in a balanced tree. IFF a table is subject to
MANY deletes which result in index nodes which are ALMOST empty (empty nodes
are cleaned out by the BTREE Cleaners in the background) there can be some
inefficiencies especially if you are not reinserting rows with similar keys
which will refill those nodes. But I think that weekly and doing this for
all tables is a bit drastic. Note that if there are many deletes there will
also be much unused space in the data pages so a general reorg of that table
is probably even more effective. You can do any of the following to force
a reorg of the entire table:
o ALTER INDEX indexname TO NOT CLUSTER; --assuming it was clustered before
ALTER INDEX indexname TO CLUSTER;
o unload with dbexport or myexport and reload with dbimport or myimport.
o ALTER FRAGMENT ON TABLE tablename INIT IN <dbspace or fragment expression
as appropriate> -- you can reorg into the same dbspace(s) the table
and its fragments alreadly reside if desired.
Art S. Kagel
Jinu.Joseph@citicorp.com wrote:
>
> --openmail-part-036ca9a9-00000002
> Content-Type: text/plain; charset=ISO-8859-1; name="BDY.RTF"
> Content-Disposition: inline; filename="BDY.RTF"
> Content-Transfer-Encoding: 8bit
>
> Hi,
> We operate on a database of size 22 GB. For operational efficiency and
> speed it was suggested that the indexes be dropped and recreated on a
> weekly basis. Now the problem is that this takes 7 hours, and we dont
> have that much time. So there are two questions ;
> 1. Does the rebuilding on a weekly basis help.
> 2. Is there anyway to speed it up or avoid it.
>
> Regards
> Jinu
>
> --openmail-part-036ca9a9-00000002
> Content-Type: application/rtf; name="BDY.RTF"
> Content-Disposition: attachment; filename="BDY.RTF"
> Content-Transfer-Encoding: base64
>
> e1xydGYxXGFuc2lcYW5zaWNwZzEyNTBcZGVmZjBcZGVmbGFuZzEwNDV7XGZvbnR0Ymwge1xm
> MFxmc3dpc3NcZnBycTdcZmNoYXJzZXQwIEFyaWFsO317XGYxXGZzd2lzc1xmY2hhcnNldDIz
> OHtcKlxmbmFtZSBBcmlhbDt9QXJpYWwgQ0U7fX0NClx1YzFccGFyZFxsYW5nMTAzM1x1bG5v
> bmVcZjBcZnMyMCBIaSxccGFyDQpXZSBvcGVyYXRlIG9uIGEgZGF0YWJhc2Ugb2Ygc2l6ZSAy
> MiBHQi4gRm9yIG9wZXJhdGlvbmFsIGVmZmljaWVuY3kgYW5kIHNwZWVkIGl0IHdhcyBzdWdn
> ZXN0ZWQgdGhhdCB0aGUgaW5kZXhlcyBiZSBkcm9wcGVkIGFuZCByZWNyZWF0ZWQgb24gYSB3
> ZWVrbHkgYmFzaXMuIE5vdyB0aGUgcHJvYmxlbSBpcyB0aGF0IHRoaXMgdGFrZXMgNyBob3Vy
> cywgYW5kIHdlIGRvbnQgaGF2ZSB0aGF0IG11Y2ggdGltZS4gU28gdGhlcmUgYXJlIHR3byBx
> dWVzdGlvbnMgO1xwYXINCjEuIERvZXMgdGhlIHJlYnVpbGRpbmcgb24gYSB3ZWVrbHkgYmFz
> aXMgaGVscC5ccGFyDQoyLiBJcyB0aGVyZSBhbnl3YXkgdG8gc3BlZWQgaXQgdXAgb3IgYXZv
> aWQgaXQuXHBhcg0KXHBhcg0KUmVnYXJkc1xwYXINCkppbnVcbGFuZzEwNDVcZjFccGFyDQp9
> DQoA
>
> --openmail-part-036ca9a9-00000002--
Art S. Kagel wrote in message <3A0043AD.746BE5E9@bloomberg.net>...
>First PLEASE do not post HTML or MIME to this newgroup it is text only.
>
Yes!
>No, you do NOT have to rebuild the indexe weekly or monthly or at all.
>Informix maintains indexes in a balanced tree. IFF a table is subject to
>MANY deletes which result in index nodes which are ALMOST empty (empty
nodes
>are cleaned out by the BTREE Cleaners in the background) there can be some
>inefficiencies especially if you are not reinserting rows with similar keys
>which will refill those nodes. But I think that weekly and doing this for
>all tables is a bit drastic. Note that if there are many deletes there
Correct.
will
>also be much unused space in the data pages so a general reorg of that
table
>is probably even more effective. You can do any of the following to force
>a reorg of the entire table:
>
>o ALTER INDEX indexname TO NOT CLUSTER; --assuming it was clustered before
> ALTER INDEX indexname TO CLUSTER;
Slow as this rebuilds the index twice.
>
>o unload with dbexport or myexport and reload with dbimport or myimport.
>
Slow as this create the index and then loads the data!
>o ALTER FRAGMENT ON TABLE tablename INIT IN <dbspace or fragment expression
Fast. Remember to set
PDQPRIORITY=100; export PDQPRIORITY
PSORT_NPROCS= 2 * number of CPUS
PSORT_DBTEMP= colon seperated list of at least 3 directories
> as appropriate> -- you can reorg into the same dbspace(s) the table
> and its fragments alreadly reside if desired.
>
>Art S. Kagel
>
>Jinu.Joseph@citicorp.com wrote:
>>
>> --openmail-part-036ca9a9-00000002
>> Content-Type: text/plain; charset=ISO-8859-1; name="BDY.RTF"
>> Content-Disposition: inline; filename="BDY.RTF"
>> Content-Transfer-Encoding: 8bit
>>
>> Hi,
>> We operate on a database of size 22 GB. For operational efficiency and
>> speed it was suggested that the indexes be dropped and recreated on a
>> weekly basis. Now the problem is that this takes 7 hours, and we dont
>> have that much time. So there are two questions ;
>> 1. Does the rebuilding on a weekly basis help.
>> 2. Is there anyway to speed it up or avoid it.
>>
>> Regards
>> Jinu
>>
>> --openmail-part-036ca9a9-00000002
>> Content-Type: application/rtf; name="BDY.RTF"
>> Content-Disposition: attachment; filename="BDY.RTF"
>> Content-Transfer-Encoding: base64
>>
>> e1xydGYxXGFuc2lcYW5zaWNwZzEyNTBcZGVmZjBcZGVmbGFuZzEwNDV7XGZvbnR0Ymwge1xm
>> MFxmc3dpc3NcZnBycTdcZmNoYXJzZXQwIEFyaWFsO317XGYxXGZzd2lzc1xmY2hhcnNldDIz
>> OHtcKlxmbmFtZSBBcmlhbDt9QXJpYWwgQ0U7fX0NClx1YzFccGFyZFxsYW5nMTAzM1x1bG5v
>> bmVcZjBcZnMyMCBIaSxccGFyDQpXZSBvcGVyYXRlIG9uIGEgZGF0YWJhc2Ugb2Ygc2l6ZSAy
>> MiBHQi4gRm9yIG9wZXJhdGlvbmFsIGVmZmljaWVuY3kgYW5kIHNwZWVkIGl0IHdhcyBzdWdn
>> ZXN0ZWQgdGhhdCB0aGUgaW5kZXhlcyBiZSBkcm9wcGVkIGFuZCByZWNyZWF0ZWQgb24gYSB3
>> ZWVrbHkgYmFzaXMuIE5vdyB0aGUgcHJvYmxlbSBpcyB0aGF0IHRoaXMgdGFrZXMgNyBob3Vy
>> cywgYW5kIHdlIGRvbnQgaGF2ZSB0aGF0IG11Y2ggdGltZS4gU28gdGhlcmUgYXJlIHR3byBx
>> dWVzdGlvbnMgO1xwYXINCjEuIERvZXMgdGhlIHJlYnVpbGRpbmcgb24gYSB3ZWVrbHkgYmFz
>> aXMgaGVscC5ccGFyDQoyLiBJcyB0aGVyZSBhbnl3YXkgdG8gc3BlZWQgaXQgdXAgb3IgYXZv
>> aWQgaXQuXHBhcg0KXHBhcg0KUmVnYXJkc1xwYXINCkppbnVcbGFuZzEwNDVcZjFccGFyDQp9
>> DQoA
>>
>> --openmail-part-036ca9a9-00000002--
ALTER INDEX TO NOT CLUSTER; is instantaneous. It does nothing other thanmark the index type in sysindexes to 'regular'.
Art S. Kagel
smooth1 wrote:
>
> Art S. Kagel wrote in message <3A0043AD.746BE5E9@bloomberg.net>...
> >First PLEASE do not post HTML or MIME to this newgroup it is text only.
> >
> Yes!
>
> >No, you do NOT have to rebuild the indexe weekly or monthly or at all.
> >Informix maintains indexes in a balanced tree. IFF a table is subject to
> >MANY deletes which result in index nodes which are ALMOST empty (empty
> nodes
> >are cleaned out by the BTREE Cleaners in the background) there can be some
> >inefficiencies especially if you are not reinserting rows with similar keys
> >which will refill those nodes. But I think that weekly and doing this for
> >all tables is a bit drastic. Note that if there are many deletes there
>
> Correct.
>
> will
> >also be much unused space in the data pages so a general reorg of that
> table
> >is probably even more effective. You can do any of the following to force
> >a reorg of the entire table:
> >
> >o ALTER INDEX indexname TO NOT CLUSTER; --assuming it was clustered before
> > ALTER INDEX indexname TO CLUSTER;>
> Slow as this rebuilds the index twice.
>
> >
> >o unload with dbexport or myexport and reload with dbimport or myimport.
> >
> Slow as this create the index and then loads the data!
>
> >o ALTER FRAGMENT ON TABLE tablename INIT IN <dbspace or fragment expression
>
> Fast. Remember to set
>
> PDQPRIORITY=100; export PDQPRIORITY
> PSORT_NPROCS= 2 * number of CPUS
> PSORT_DBTEMP= colon seperated list of at least 3 directories
>
> > as appropriate> -- you can reorg into the same dbspace(s) the table
> > and its fragments alreadly reside if desired.
> >
> >Art S. Kagel
> >
> >Jinu.Joseph@citicorp.com wrote:
> >>
> >> --openmail-part-036ca9a9-00000002
> >> Content-Type: text/plain; charset=ISO-8859-1; name="BDY.RTF"
> >> Content-Disposition: inline; filename="BDY.RTF"
> >> Content-Transfer-Encoding: 8bit
> >>
> >> Hi,
> >> We operate on a database of size 22 GB. For operational efficiency and
> >> speed it was suggested that the indexes be dropped and recreated on a
> >> weekly basis. Now the problem is that this takes 7 hours, and we dont
> >> have that much time. So there are two questions ;
> >> 1. Does the rebuilding on a weekly basis help.
> >> 2. Is there anyway to speed it up or avoid it.
> >>
> >> Regards
> >> Jinu
> >>
> >> --openmail-part-036ca9a9-00000002
> >> Content-Type: application/rtf; name="BDY.RTF"
> >> Content-Disposition: attachment; filename="BDY.RTF"
> >> Content-Transfer-Encoding: base64
> >>
> >> e1xydGYxXGFuc2lcYW5zaWNwZzEyNTBcZGVmZjBcZGVmbGFuZzEwNDV7XGZvbnR0Ymwge1xm
> >> MFxmc3dpc3NcZnBycTdcZmNoYXJzZXQwIEFyaWFsO317XGYxXGZzd2lzc1xmY2hhcnNldDIz
> >> OHtcKlxmbmFtZSBBcmlhbDt9QXJpYWwgQ0U7fX0NClx1YzFccGFyZFxsYW5nMTAzM1x1bG5v
> >> bmVcZjBcZnMyMCBIaSxccGFyDQpXZSBvcGVyYXRlIG9uIGEgZGF0YWJhc2Ugb2Ygc2l6ZSAy
> >> MiBHQi4gRm9yIG9wZXJhdGlvbmFsIGVmZmljaWVuY3kgYW5kIHNwZWVkIGl0IHdhcyBzdWdn
> >> ZXN0ZWQgdGhhdCB0aGUgaW5kZXhlcyBiZSBkcm9wcGVkIGFuZCByZWNyZWF0ZWQgb24gYSB3
> >> ZWVrbHkgYmFzaXMuIE5vdyB0aGUgcHJvYmxlbSBpcyB0aGF0IHRoaXMgdGFrZXMgNyBob3Vy
> >> cywgYW5kIHdlIGRvbnQgaGF2ZSB0aGF0IG11Y2ggdGltZS4gU28gdGhlcmUgYXJlIHR3byBx
> >> dWVzdGlvbnMgO1xwYXINCjEuIERvZXMgdGhlIHJlYnVpbGRpbmcgb24gYSB3ZWVrbHkgYmFz
> >> aXMgaGVscC5ccGFyDQoyLiBJcyB0aGVyZSBhbnl3YXkgdG8gc3BlZWQgaXQgdXAgb3IgYXZv
> >> aWQgaXQuXHBhcg0KXHBhcg0KUmVnYXJkc1xwYXINCkppbnVcbGFuZzEwNDVcZjFccGFyDQp9
> >> DQoA
> >>
> >> --openmail-part-036ca9a9-00000002--
Sorry, I wasn't sure...
Art S. Kagel wrote in message <3A0813D6.14E11C65@bloomberg.net>...
>ALTER INDEX TO NOT CLUSTER; is instantaneous. It does nothing other than>mark the index type in sysindexes to 'regular'.
>
>Art S. Kagel
>
>smooth1 wrote:
>>
>> Art S. Kagel wrote in message <3A0043AD.746BE5E9@bloomberg.net>...
>> >First PLEASE do not post HTML or MIME to this newgroup it is text only.
>> >
>> Yes!
>>
>> >No, you do NOT have to rebuild the indexe weekly or monthly or at all.
>> >Informix maintains indexes in a balanced tree. IFF a table is subject
to
>> >MANY deletes which result in index nodes which are ALMOST empty (empty
>> nodes
>> >are cleaned out by the BTREE Cleaners in the background) there can be
some
>> >inefficiencies especially if you are not reinserting rows with similar
keys
>> >which will refill those nodes. But I think that weekly and doing this
for
>> >all tables is a bit drastic. Note that if there are many deletes there
>>
>> Correct.
>>
>> will
>> >also be much unused space in the data pages so a general reorg of that
>> table
>> >is probably even more effective. You can do any of the following to
force
>> >a reorg of the entire table:
>> >
>> >o ALTER INDEX indexname TO NOT CLUSTER; --assuming it was clustered
before
>> > ALTER INDEX indexname TO CLUSTER;>>
>> Slow as this rebuilds the index twice.
>>
>> >
>> >o unload with dbexport or myexport and reload with dbimport or myimport.
>> >
>> Slow as this create the index and then loads the data!
>>
>> >o ALTER FRAGMENT ON TABLE tablename INIT IN <dbspace or fragment
expression
>>
>> Fast. Remember to set
>>
>> PDQPRIORITY=100; export PDQPRIORITY
>> PSORT_NPROCS= 2 * number of CPUS
>> PSORT_DBTEMP= colon seperated list of at least 3 directories
>>
>> > as appropriate> -- you can reorg into the same dbspace(s) the table
>> > and its fragments alreadly reside if desired.
>> >
>> >Art S. Kagel
>> >
>> >Jinu.Joseph@citicorp.com wrote:
>> >>
>> >> --openmail-part-036ca9a9-00000002
>> >> Content-Type: text/plain; charset=ISO-8859-1; name="BDY.RTF"
>> >> Content-Disposition: inline; filename="BDY.RTF"
>> >> Content-Transfer-Encoding: 8bit
>> >>
>> >> Hi,
>> >> We operate on a database of size 22 GB. For operational efficiency and
>> >> speed it was suggested that the indexes be dropped and recreated on a
>> >> weekly basis. Now the problem is that this takes 7 hours, and we dont
>> >> have that much time. So there are two questions ;
>> >> 1. Does the rebuilding on a weekly basis help.
>> >> 2. Is there anyway to speed it up or avoid it.
>> >>
>> >> Regards
>> >> Jinu
>> >>
>> >> --openmail-part-036ca9a9-00000002
>> >> Content-Type: application/rtf; name="BDY.RTF"
>> >> Content-Disposition: attachment; filename="BDY.RTF"
>> >> Content-Transfer-Encoding: base64
>> >>
>> >>
e1xydGYxXGFuc2lcYW5zaWNwZzEyNTBcZGVmZjBcZGVmbGFuZzEwNDV7XGZvbnR0Ymwge1xm
>> >>
MFxmc3dpc3NcZnBycTdcZmNoYXJzZXQwIEFyaWFsO317XGYxXGZzd2lzc1xmY2hhcnNldDIz
>> >>
OHtcKlxmbmFtZSBBcmlhbDt9QXJpYWwgQ0U7fX0NClx1YzFccGFyZFxsYW5nMTAzM1x1bG5v
>> >>
bmVcZjBcZnMyMCBIaSxccGFyDQpXZSBvcGVyYXRlIG9uIGEgZGF0YWJhc2Ugb2Ygc2l6ZSAy
>> >>
MiBHQi4gRm9yIG9wZXJhdGlvbmFsIGVmZmljaWVuY3kgYW5kIHNwZWVkIGl0IHdhcyBzdWdn
>> >>
ZXN0ZWQgdGhhdCB0aGUgaW5kZXhlcyBiZSBkcm9wcGVkIGFuZCByZWNyZWF0ZWQgb24gYSB3
>> >>
ZWVrbHkgYmFzaXMuIE5vdyB0aGUgcHJvYmxlbSBpcyB0aGF0IHRoaXMgdGFrZXMgNyBob3Vy
>> >>
cywgYW5kIHdlIGRvbnQgaGF2ZSB0aGF0IG11Y2ggdGltZS4gU28gdGhlcmUgYXJlIHR3byBx
>> >>
dWVzdGlvbnMgO1xwYXINCjEuIERvZXMgdGhlIHJlYnVpbGRpbmcgb24gYSB3ZWVrbHkgYmFz
>> >>
aXMgaGVscC5ccGFyDQoyLiBJcyB0aGVyZSBhbnl3YXkgdG8gc3BlZWQgaXQgdXAgb3IgYXZv
>> >>
aWQgaXQuXHBhcg0KXHBhcg0KUmVnYXJkc1xwYXINCkppbnVcbGFuZzEwNDVcZjFccGFyDQp9
>> >> DQoA
>> >>
>> >> --openmail-part-036ca9a9-00000002--