Could Cheetah have saved my Sunday?

I've spent most of today fragmenting a table of 184 million rows which has burst the IDS 9/10 limit of 16.7m pages per tablespace. I was disappointed to see this limit still persists in v10, although it wouldn't have helped me anyway as this db is 9.40. Is it removed in v11 ...? regards -- Neil Truby t:01932 724027 Director m:07798 811708 Ardenta Limited e:neil.truby@ardenta.com

> I've spent most of today fragmenting a table of 184 million rows which has > burst the IDS 9/10 limit of 16.7m pages per tablespace. > > I was disappointed to see this limit still persists in v10, although it > wouldn't have helped me anyway as this db is 9.40. Is it removed in v11 > ...? > > regards To my knowledge the page limit per fragment or partition is still there and will remain. In IDS 10 and higher you may use big pages (up to 16K), so the 32GB limit per fragment (some 16.7m 2k pages) is lifted to 256GB per fragment (16.7m 16k pages) . max # of rows per fragment would still be 255 rows per page times 16.7m pages per fragment (depending on row size of course) I guess big pages feature in IDS 10 would have postponed the need for fragmentation and reorg for quite some time. HTH Tilman

On Jul 15, 2:37 pm, "Neil Truby" <neil.tr...@ardenta.com> wrote: > I've spent most of today fragmenting a table of 184 million rows which has > burst the IDS 9/10 limit of 16.7m pages per tablespace. > > I was disappointed to see this limit still persists in v10, although it > wouldn't have helped me anyway as this db is 9.40. Is it removed in v11 > ...? Hey Neil, Not solved directly. Even in 11 you cannot have more than 16MM pages in a partition. However, as of 10 you can have multiple partitions in a single dbspace by naming each partition: FRAGMENT BY EXPRESSION PARTITION dbs1_part1 (id < 16000000) IN dbspace1, PARTITION dbs1_part2 (id >= 16000000 and id < 32000000) IN dbspace1, ... Also, as of 10 you can set up a dbspace with a larger page size and so hold more rows in the same 16MM pages (limited by the 255 slots per page limit). So a 60 byte row table will get 486,539,264 rows per partition with 2K pages but 4,278,190,080 rows per partition in a 16K pagesize dbspace (but a 32byte row won't get any more than that on larger pages). Art S. Kagel

"Art S. Kagel" <art.kagel@gmail.com> wrote in message news:1184602916.890176.21940@d55g2000hsg.googlegroups.com... > On Jul 15, 2:37 pm, "Neil Truby" <neil.tr...@ardenta.com> wrote: >> I've spent most of today fragmenting a table of 184 million rows which >> has >> burst the IDS 9/10 limit of 16.7m pages per tablespace. >> >> I was disappointed to see this limit still persists in v10, although it >> wouldn't have helped me anyway as this db is 9.40. Is it removed in v11 >> ...? > > Hey Neil, > > Not solved directly. Even in 11 you cannot have more than 16MM pages > in a partition. Thanks for all yhe useful information, Art, and to Keith and others who pointed out some of the same things. Is it my imagination, or is a 16g tablespace size soon going to appear to the non-Informix world as a curious and amusing Informix feature like 2g chunks was until recently ...?

informix-list-bounces@iiug.org wrote on 07/16/2007 10:32:51 PM: > "Art S. Kagel" <art.kagel@gmail.com> wrote in message > news:1184602916.890176.21940@d55g2000hsg.googlegroups.com... > > On Jul 15, 2:37 pm, "Neil Truby" <neil.tr...@ardenta.com> wrote: > >> I've spent most of today fragmenting a table of 184 million rows which > >> has > >> burst the IDS 9/10 limit of 16.7m pages per tablespace. > >> > >> I was disappointed to see this limit still persists in v10, although it > >> wouldn't have helped me anyway as this db is 9.40. Is it removed in v11 > >> ...? > > > > Hey Neil, > > > > Not solved directly. Even in 11 you cannot have more than 16MM pages > > in a partition. > > Thanks for all yhe useful information, Art, and to Keith and others who > pointed out some of the same things. > Is it my imagination, or is a 16g tablespace size soon going to appear to > the non-Informix world as a curious and amusing Informix feature like 2g > chunks was until recently ...? > As Art pointed out - you will always be limited to 16,777,215 pages per partition - barring some major redesign of IDS architecture. The reason for that is how IDS addresses rows in a partition/fragment. A row is addressed via its rowid, which has the format 0xLLLLLLSS , where L is the logical page offset into the partition and S is the slot number on that page. 0xFFFFFF = 16,777,215 pages. For 2K pages this is 32GB per partition (or fragment or tablespace) For 16K pages this is 256GB per partition which ain't too bad even nowadays. Combined with the fact that fragmentation (or partitioning) is easy it ain't too bad. Use the 'ALTER FRAGMENT ... ATTACH ' clause to make a round robin fragmented table from two or more non-fragmented tables. Create one or more empty tables with the same schema as the original table but in a different dbspace. Run : ALTER FRAGMENT ON TABLE <orig_table> ATTACH <orig_table>, <new_tab1>,<new_tab2>,...; This will make the table round robin fragmented with one full and several empty fragments. (Indexes on orig table should have been be detached before!) Would this have saved your Sunday? Apart from that: yes, Oracle does not have any limit on size of a partition (not that I know at least). So a DBA has not to worry about limits here. Advantage Oracle in that aspect of a DBA's life. Rgds Tilman > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list