number of extents

I think I remember reading somewhere that with newere versions of IDS you did not need to worry about the number of extents. Is this correct? I have some tables with over 100 extents and I suspect that they are severely fragmented. I am formulating a plan for fixing this situation as well as setting up a good data distribution(fragmentation) Plan. should I be concerned with the extents? Sent via Deja.com http://www.deja.com/ Before you buy.

I think there is still a maximum number of extents, whose precise value is subject to an impentetrable formula, of around 240 extents. MATTHEW H. DEVLIN wrote in message <84040r$bhb$1@nnrp1.deja.com>... >I think I remember reading somewhere that with newere versions of IDS >you did not need to worry about the number of extents. Is this >correct? I have some tables with over 100 extents and I suspect that >they are severely fragmented. I am formulating a plan for fixing this >situation as well as setting up a good data distribution(fragmentation) >Plan. should I be concerned with the extents? > > >Sent via Deja.com http://www.deja.com/ >Before you buy.

If 240 is correct what will happen with a table that reaches that number? In article <8412im$lt1$1@taliesin2.netcom.net.uk>, "Neil Truby" <ntruby@netcomuk.co.uk> wrote: > I think there is still a maximum number of extents, whose precise value is > subject to an impentetrable formula, of around 240 extents. > > MATTHEW H. DEVLIN wrote in message <84040r$bhb$1@nnrp1.deja.com>... > >I think I remember reading somewhere that with newere versions of IDS > >you did not need to worry about the number of extents. Is this > >correct? I have some tables with over 100 extents and I suspect that > >they are severely fragmented. I am formulating a plan for fixing this > >situation as well as setting up a good data distribution (fragmentation) > >Plan. should I be concerned with the extents? > > > > > >Sent via Deja.com http://www.deja.com/ > >Before you buy. > > Sent via Deja.com http://www.deja.com/ Before you buy.

In article <8443m8$pkn$1@nnrp1.deja.com>, MATTHEW H. DEVLIN <the_griffon@my-deja.com> writes >If 240 is correct what will happen with a table that reaches that >number? > You get an error and cannot add more rows to the table.. -- David Williams

It's not that it's impenetrable, it just varies due to the nature of the table in question. All tables have a single page descriptor known as a tablespace tablespace page or partition page (also partnum). Factors such as number of special (read BLOB and VARCHAR) columns, and the number of index keys can influence the maximum number of extents. Since there can be 2K or 4K pages depending on the implementation of IDS employed, this introduces further consideration. When you hit the limit, the appropriate (cannot insert new row) message will appear. My experience has shown that large numbers of extents are very bad for performance. An easy way to remedy the problem is to run an ALTER INDEX ..TO CLUSTER on the unique index for the table. Since this reorders the table it has the pleasant side effect of globbing the entire table into a neat single extent. After careful analysis of future growth patterns, an appropriate ALTER TABLE .. MODIFY NEXT ... should be issued to keep the table in a nice manageable number of extents. Of course, you will have to check to make sure that the DBspace has sufficient space to hold another entire copy of the table you are reorganizing. Hope this helps. Alan Neil Truby wrote: > I think there is still a maximum number of extents, whose precise value is > subject to an impentetrable formula, of around 240 extents. > > MATTHEW H. DEVLIN wrote in message <84040r$bhb$1@nnrp1.deja.com>... > >I think I remember reading somewhere that with newere versions of IDS > >you did not need to worry about the number of extents. Is this > >correct? I have some tables with over 100 extents and I suspect that > >they are severely fragmented. I am formulating a plan for fixing this > >situation as well as setting up a good data distribution(fragmentation) > >Plan. should I be concerned with the extents? > > > > > >Sent via Deja.com http://www.deja.com/ > >Before you buy.

In general the fastest way to defrag a table is with: ALTER FRAGMENT ON TABLE <tabname> INIT IN <dbspacename or fragmentation clause>; You can name the same dbspace the table already resides in and if you ALTER the NEXT SIZE first you should get only one or two fragments afterward if there is sufficient contiguous space available. Art S. Kagel Alan Caldera wrote: > > It's not that it's impenetrable, it just varies due to the nature of the table in question. All > tables have a single page descriptor known as a tablespace tablespace page or partition page (also > partnum). Factors such as number of special (read BLOB and VARCHAR) columns, and the number of index > keys can influence the maximum number of extents. Since there can be 2K or 4K pages depending on the > implementation of IDS employed, this introduces further consideration. When you hit the limit, the > appropriate (cannot insert new row) message will appear. My experience has shown that large numbers > of extents are very bad for performance. An easy way to remedy the problem is to run an ALTER INDEX > ..TO CLUSTER on the unique index for the table. Since this reorders the table it has the pleasant > side effect of globbing the entire table into a neat single extent. After careful analysis of future > growth patterns, an appropriate ALTER TABLE .. MODIFY NEXT ... should be issued to keep the table in > a nice manageable number of extents. Of course, you will have to check to make sure that the DBspace > has sufficient space to hold another entire copy of the table you are reorganizing. Hope this helps. > > Alan > > Neil Truby wrote: > > > I think there is still a maximum number of extents, whose precise value is > > subject to an impentetrable formula, of around 240 extents. > > > > MATTHEW H. DEVLIN wrote in message <84040r$bhb$1@nnrp1.deja.com>... > > >I think I remember reading somewhere that with newere versions of IDS > > >you did not need to worry about the number of extents. Is this > > >correct? I have some tables with over 100 extents and I suspect that > > >they are severely fragmented. I am formulating a plan for fixing this > > >situation as well as setting up a good data distribution(fragmentation) > > >Plan. should I be concerned with the extents? > > > > > > > > >Sent via Deja.com http://www.deja.com/ > > >Before you buy.

I found an unpleasant side-effect of ALTER TABLE ... NEXT EXTENT. the next extent size is applied also to any detached indexes of the table, which is almost certainly not what you want. It wasn't what I wanted! Neil Art S. Kagel wrote in message <38677613.CA69F9D3@bloomberg.net>... >In general the fastest way to defrag a table is with: > ALTER FRAGMENT ON TABLE <tabname>> INIT IN <dbspacename or fragmentation clause>; > >You can name the same dbspace the table already resides in and if you ALTER >the NEXT SIZE first you should get only one or two fragments afterward if >there is sufficient contiguous space available. > >Art S. Kagel > >Alan Caldera wrote: >> >> It's not that it's impenetrable, it just varies due to the nature of the table in question. All >> tables have a single page descriptor known as a tablespace tablespace page or partition page (also >> partnum). Factors such as number of special (read BLOB and VARCHAR) columns, and the number of index >> keys can influence the maximum number of extents. Since there can be 2K or 4K pages depending on the >> implementation of IDS employed, this introduces further consideration. When you hit the limit, the >> appropriate (cannot insert new row) message will appear. My experience has shown that large numbers >> of extents are very bad for performance. An easy way to remedy the problem is to run an ALTER INDEX >> ..TO CLUSTER on the unique index for the table. Since this reorders the table it has the pleasant >> side effect of globbing the entire table into a neat single extent. After careful analysis of future >> growth patterns, an appropriate ALTER TABLE .. MODIFY NEXT ... should be issued to keep the table in >> a nice manageable number of extents. Of course, you will have to check to make sure that the DBspace >> has sufficient space to hold another entire copy of the table you are reorganizing. Hope this helps. >> >> Alan >> >> Neil Truby wrote: >> >> > I think there is still a maximum number of extents, whose precise value is >> > subject to an impentetrable formula, of around 240 extents. >> > >> > MATTHEW H. DEVLIN wrote in message <84040r$bhb$1@nnrp1.deja.com>... >> > >I think I remember reading somewhere that with newere versions of IDS >> > >you did not need to worry about the number of extents. Is this >> > >correct? I have some tables with over 100 extents and I suspect that >> > >they are severely fragmented. I am formulating a plan for fixing this >> > >situation as well as setting up a good data distribution(fragmentation) >> > >Plan. should I be concerned with the extents? >> > > >> > > >> > >Sent via Deja.com http://www.deja.com/ >> > >Before you buy.

Neil Truby wrote: > > I found an unpleasant side-effect of ALTER TABLE ... NEXT EXTENT. the next > extent size is applied also to any detached indexes of the table, which is > almost certainly not what you want. This is understandable since the next size of the detached indexes was originally based on the table's next extent size when it was created. It would be natural to recalculate the next size for the indexes based on the new next size of the table. Question though: Was the next size of the index set to the new next size of the table or that size times the ratio of the keysize to the rowsize (as it is originally calculated)? > It wasn't what I wanted! Hey I want to be able to specify my own extent sizing for detached indexes, we can't always get what we want. Put in a feature request. If you can make a strong enough case, Menlo Park may go for it. Art S. Kagel > Neil > > Art S. Kagel wrote in message <38677613.CA69F9D3@bloomberg.net>... > >In general the fastest way to defrag a table is with: > > ALTER FRAGMENT ON TABLE <tabname>> > INIT IN <dbspacename or fragmentation clause>; > > > >You can name the same dbspace the table already resides in and if you ALTER > >the NEXT SIZE first you should get only one or two fragments afterward if > >there is sufficient contiguous space available. > > > >Art S. Kagel > > > >Alan Caldera wrote: > >> > >> It's not that it's impenetrable, it just varies due to the nature of the > table in question. All > >> tables have a single page descriptor known as a tablespace tablespace > page or partition page (also > >> partnum). Factors such as number of special (read BLOB and VARCHAR) > columns, and the number of index > >> keys can influence the maximum number of extents. Since there can be 2K > or 4K pages depending on the > >> implementation of IDS employed, this introduces further consideration. > When you hit the limit, the > >> appropriate (cannot insert new row) message will appear. My experience > has shown that large numbers > >> of extents are very bad for performance. An easy way to remedy the > problem is to run an ALTER INDEX > >> ..TO CLUSTER on the unique index for the table. Since this reorders the > table it has the pleasant > >> side effect of globbing the entire table into a neat single extent. After > careful analysis of future > >> growth patterns, an appropriate ALTER TABLE .. MODIFY NEXT ... should be > issued to keep the table in > >> a nice manageable number of extents. Of course, you will have to check to > make sure that the DBspace > >> has sufficient space to hold another entire copy of the table you are > reorganizing. Hope this helps. > >> > >> Alan > >> > >> Neil Truby wrote: > >> > >> > I think there is still a maximum number of extents, whose precise value > is > >> > subject to an impentetrable formula, of around 240 extents. > >> > > >> > MATTHEW H. DEVLIN wrote in message <84040r$bhb$1@nnrp1.deja.com>... > >> > >I think I remember reading somewhere that with newere versions of IDS > >> > >you did not need to worry about the number of extents. Is this > >> > >correct? I have some tables with over 100 extents and I suspect that > >> > >they are severely fragmented. I am formulating a plan for fixing this > >> > >situation as well as setting up a good data > distribution(fragmentation) > >> > >Plan. should I be concerned with the extents? > >> > > > >> > > > >> > >Sent via Deja.com http://www.deja.com/ > >> > >Before you buy.