What is the maximum number of pages for an index in Informix 11.70?

Hi All, I read a post on Informix's Index fragment page limit on Ben's blog. https://informixdba.wordpress.com/2014/04/22/index-fragment-page-limit/ According to the Informix 12.10 knowledge center document, the maximum number of pages per index fragments is 2,147,483,647. The date that the post was created is 2014, but the content of the Informix 11.70 version of the ibm knowledge center still does not have an index fragment page limit. Later I will test against this limit, but does anyone know if the index has a page limit of 2,147,483,647 in 11.70? ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------ #Informix

Hi: Yes, while data partitions have a limit of 2^24 pages index partitions are only limited by the 32bit relative page number (since index pages do not have a slot table taking up 8 bits of the page number) so 2^31-1 ( 2,147,483,647 ). I've spoken to the kernel engineers and the partition page limits are not likely to change. Ever. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. ------Original Message------ Hi All, I read a post on Informix's Index fragment page limit on Ben's blog. https://informixdba.wordpress.com/2014/04/22/index-fragment-page-limit/ According to the Informix 12.10 knowledge center document, the maximum number of pages per index fragments is 2,147,483,647. The date that the post was created is 2014, but the content of the Informix 11.70 version of the ibm knowledge center still does not have an index fragment page limit. Later I will test against this limit, but does anyone know if the index has a page limit of 2,147,483,647 in 11.70? ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------ #Informix

As the author of that blog post I had been told on more than one occasion by IBMers that storage improvements were done in 11.70 and an increase in the index page limit was one of the benefits. The way it was described it was not just a documentation update, although the change to increase the index page limit did not involve a lot of code changes. Normally I try to bust any myths or uncertainty by testing; it is not difficult to write a script to create many GBs of dummy data. However I don't have an 11.50 version around to test and prove this any more and of course when we were running 11.50 we never allowed this limits to be reached! As 11.50 isn't supported any more I am not going to invest the time but please feel free to put a comment on the blog post questioning its accuracy and I will approve the comment. While somewhat understandable given the complexity and risk, I am disappointed to hear that the data page limitation will never be addressed. I find that this, combined with the 255 rows/page limit, greatly restrict options around page sizes and partitioning strategies. For example for large tables, partitioning by month is ruled out if a month is likely to contain more than 2^24 data pages. This reduces the utility of these features. Furthermore if compression is used for tables the 255 rows/page limit still applies and means that 2 kB and 4 kB page sizes are the only realistic options for most tables. I would be glad to see both these limitations gone or limits raised by 2^8. Ben. ------------------------------ Benjamin Thompson ------------------------------

Ben: Interesting. As I said, I never ran into an index page limitation then one time as I was telling someone about the 2^24 limit one of the developers told me that this did not apply to index pages and never did. If that was not true before 11.70, I don't know. I also don't have access to an 11.50 or earlier engine at this time to test it. I agree that the 2^24 page limit and the 255 row limit need to be improved. If the slot entry location were increased from a byte to a 12 bit integer and the page address to 28 bits then the relative page number would only increase by 8 bits, we would be allowed over 264 million pages per partition and even a 16K page would allow the maximum number of 4byte rows (4096). This would involve major code changes not to mention extensive upgrade and reversion code ala the change to bigger chunks in v10. Maybe now with the changes in the development staff someone will tackle it, or at least put it on the v.next roadmap. ------------------------------ Art Kagel ------------------------------

Ben: I did a simple test on 11.50.fc6 and 11.70.fc5 for this problem. For the test scenario I used the schema of the table my customer is using. My client creates and uses a table similar to the schema below each month. This table stores approximately 440 million records each month. The table consists of a round robin schema, but the unique index is not fragmented. So the unique index of this table is nearly 15 million pages at the end of the month. create table log_12 (col1 varchar ( 50 ) , col2 varchar ( 32 ) , col3 varchar ( 5 ) , col4 varchar ( 5 ) , col5 varchar ( 10 ) . . . . . ) fragment by round robin in dbs1 , dbs2 , dbs3 extent size 16000000 next size 10000000 ; ​ In order to save time, I executed the following unique index after inserting 500 million dummy data. create unique index log_12_pk on log_12 (col1 , col2 , col3 , col4 , col5 ) in idx1 ; ​ As a result of testing in version 11.5, when the number of unique index pages reached 2^24 (16,775,134), the create index operation was rolled back with the following message in the online log. 15:22:42 partition 'demo: informix.log_12_pk': no ​​more pages​ In 11.7, the unique index was created, and 35,999,944 pages were allocated to the unique index. (oncheck -pt) Index log_12_pk fragment partition idx1 in DBspace idx1 Physical Address 5 : 5 Creation date 02 / 14 / 2020 16 : 47 : 53 TBLspace Flags 801 Page Locking TBLspace use 4 bit bit - maps Maximum row size 107 Number of special columns 0 Number of keys 1 Number of extents 2 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize ( k ) 2 First extent size 8672897 Next extent size 5420560 Number of pages allocated 35999944 Number of pages used 35705525 Number of data pages 0 Number of rows 0 Partition partnum 5242882 Partition lockid 2097217 Extents Logical Page Physical Page Size Physical Pages 0 5 : 53 23999947 23999947 23999947 9 : 3 11999997 11999997 ​ ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

I guess that answers the question definitively then. Ben was correct, indexes were limited to 2^24 pages per partition prior to v11.70 but no longer. Art ------------------------------ Art Kagel ------------------------------

My IBM contact was correct :) Thanks for testing Sang-Gyu. Ben. ------------------------------ Benjamin Thompson ------------------------------