FILLFACTOR 100 only 99 ?

Hi ! I was experimenting with IUS Btree physical storage layout. I created an unique index on a char(8) column with fill factor 100. A branch node should be filled with 126 slots, as each index entry uses 8 + 4 + 4 (column, page no, slot entry) bytes and there is a total auf 2020 bytes free on a page. However, looking at the index pages using oncheck -pp reveals that addr stamp nslots flag type frptr frcnt next prev 303b8a 2416107 125 50 BTREE 1524 20 82 0 slot ptr len flg 1 24 12 0 2 36 12 0 ... There is space for one more slot (20 bytes free) but the slot is not used, i.e. the page is not 100& filled but only 99% Any good reasons for that ?? --Chris

You forgot the 'delete flag'. That would make each entry 21 bytes long. Christoph Liebig wrote: > Hi ! > > I was experimenting with IUS Btree physical storage layout. > I created an unique index on a char(8) column with fill factor 100. > A branch node should be filled with 126 slots, as each index entry > uses 8 + 4 + 4 (column, page no, slot entry) bytes and there is a total > auf 2020 bytes free on a page. > However, looking at the index pages using oncheck -pp reveals that > addr stamp nslots flag type frptr frcnt next prev > 303b8a 2416107 125 50 BTREE 1524 20 82 0 > slot ptr len flg > 1 24 12 0 > 2 36 12 0 > ... > > There is space for one more slot (20 bytes free) but the slot is not > used, i.e. the page is not 100& filled but only 99% > > Any good reasons for that ?? > > --Chris

Please correct me if I am wrong: A pageno is only 3 bytes in informix - that is why a rowid takes 4 bytes: 3 for the page no and 1 byte for the slot entry index. That is, my 4 byte count for the pageno already includes space for a delete flag (are there delete flags in branch nodes, anyway ?). Furthermore, the output of oncheck revelas that i) the index entry is 12 bytes (see column len) ii) there are still 20 bytes left, enough to carry another 12 byte index entry (see column frcnt) --Chris Madison Pruet wrote: > You forgot the 'delete flag'. That would make each entry 21 bytes long. > > > > > I was experimenting with IUS Btree physical storage layout. > > I created an unique index on a char(8) column with fill factor 100. > > A branch node should be filled with 126 slots, as each index entry > > uses 8 + 4 + 4 (column, page no, slot entry) bytes and there is a total > > auf 2020 bytes free on a page. > > However, looking at the index pages using oncheck -pp reveals that > > addr stamp nslots flag type frptr frcnt next prev > > 303b8a 2416107 125 50 BTREE 1524 20 82 0 > > slot ptr len flg > > 1 24 12 0 > > 2 36 12 0 > > ... > > > > There is space for one more slot (20 bytes free) but the slot is not > > used, i.e. the page is not 100& filled but only 99% > > > > Any good reasons for that ?? > > > > --Chris

The rowid in the index is three bytes for the page number and one byte for the slot number. The delete flag is not contained within those 4 bytes. A branch node will not necessarly be filled, even with 100 percent fill factor. This is because one of the rules of a balanced btree is that the depth to the leaf pages must be the same. The 100 percent fill factor has to do with how full the leaf pages are. Basically, the only way that the branch node can be guarenteed to be full is no have more leaf pages and that would require partially filling the loaf pages. Christoph Liebig wrote: > Please correct me if I am wrong: > A pageno is only 3 bytes in informix - that is why a rowid takes 4 bytes: 3 > for the page no and 1 byte for the slot entry index. > That is, my 4 byte count for the pageno already includes space for a delete > flag (are there delete flags in branch nodes, anyway ?). > Furthermore, the output of oncheck revelas that > i) the index entry is 12 bytes (see column len) > ii) there are still 20 bytes left, enough to carry another 12 byte index > entry (see column frcnt) > > --Chris > > Madison Pruet wrote: > > > You forgot the 'delete flag'. That would make each entry 21 bytes long. > > > > > > > > I was experimenting with IUS Btree physical storage layout. > > > I created an unique index on a char(8) column with fill factor 100. > > > A branch node should be filled with 126 slots, as each index entry > > > uses 8 + 4 + 4 (column, page no, slot entry) bytes and there is a total > > > auf 2020 bytes free on a page. > > > However, looking at the index pages using oncheck -pp reveals that > > > addr stamp nslots flag type frptr frcnt next prev > > > 303b8a 2416107 125 50 BTREE 1524 20 82 0 > > > slot ptr len flg > > > 1 24 12 0 > > > 2 36 12 0 > > > ... > > > > > > There is space for one more slot (20 bytes free) but the slot is not > > > used, i.e. the page is not 100& filled but only 99% > > > > > > Any good reasons for that ?? > > > > > > --Chris -- Madison Pruet =========================================== Enterprise Replication Product Developement Dallas, Texas Informix Software ===========================================

Maybe you got me wrong: I am looking at inner B+ tree nodes, called branch nodes. They do not contain rowids. I have been looking at the branch nodes using oncheck -pp and found that my calculation of branch-node entriy size is correct ! That is, the size of an entry in the branch node definitely is 12 bytes. An entry consists of key+pageno (unique index), where the pageno uses 3+1 byte. The extra 1 byte might be a delete flag - however, I do not thinkt that there are delete flags in branch nodes, anyway. Looking at a branch node (page) in the table space I found that there are 20 bytes left that could carry another index entry. It does not matter in practice very much, if that one sparse entry is not used. I thought there would be some good reason to not fill up branch nodes 100% (however, I do not find any :( I also took a look at leaf nodes, they indeed get filled 100%. Another test I carried out, was looking at FILLFACTORS < 100%. In that case, everything works out as expected. It is only in the 100% case that theory and practice do not match. PS: I am giving a course an DBS, looking at IUS as a show case (you are right: we are not teaching Oracle :). It is really nice to be able to get in depth knowledge about IUS works that easy ! Madison Pruet wrote: > The rowid in the index is three bytes for the page number and one byte for the > slot number. The delete flag is not contained within those 4 bytes. A branch > node will not necessarly be filled, even with 100 percent fill factor. This is > because one of the rules of a balanced btree is that the depth to the leaf > pages must be the same. The 100 percent fill factor has to do with how full the > leaf pages are. Basically, the only way that the branch node can be guarenteed > to be full is no have more leaf pages and that would require partially filling > the loaf pages. > > > > > > > > > > > > > I was experimenting with IUS Btree physical storage layout. > > > > I created an unique index on a char(8) column with fill factor 100. > > > > A branch node should be filled with 126 slots, as each index entry > > > > uses 8 + 4 + 4 (column, page no, slot entry) bytes and there is a total > > > > auf 2020 bytes free on a page. > > > > However, looking at the index pages using oncheck -pp reveals that > > > > addr stamp nslots flag type frptr frcnt next prev > > > > 303b8a 2416107 125 50 BTREE 1524 20 82 0 > > > > slot ptr len flg > > > > 1 24 12 0 > > > > 2 36 12 0 > > > > ... >

Maybe you got me wrong: I am looking at inner B+ tree nodes, called branch nodes. They do not contain rowids. I have been looking at the branch nodes using oncheck -pp and found that my calculation of branch-node entriy size is correct ! That is, the size of an entry in the branch node definitely is 12 bytes. An entry consists of key+pageno (unique index), where the pageno uses 3+1 byte. The extra 1 byte might be a delete flag - however, I do not thinkt that there are delete flags in branch nodes, anyway. Looking at a branch node (page) in the table space I found that there are 20 bytes left that could carry another index entry. It does not matter in practice very much, if that one sparse entry is not used. I thought there would be some good reason to not fill up branch nodes 100% (however, I do not find any :( I also took a look at leaf nodes, they indeed get filled 100%. Another test I carried out, was looking at FILLFACTORS < 100%. In that case, everything works out as expected. It is only in the 100% case that theory and practice do not match. PS: I am giving a course an DBS, looking at IUS as a show case (you are right: we are not teaching Oracle :). It is really nice to be able to get in depth knowledge about IUS works that easy ! Madison Pruet wrote: > The rowid in the index is three bytes for the page number and one byte for the > slot number. The delete flag is not contained within those 4 bytes. A branch > node will not necessarly be filled, even with 100 percent fill factor. This is > because one of the rules of a balanced btree is that the depth to the leaf > pages must be the same. The 100 percent fill factor has to do with how full the > leaf pages are. Basically, the only way that the branch node can be guarenteed > to be full is no have more leaf pages and that would require partially filling > the loaf pages. > > > > > > > > > > > > > I was experimenting with IUS Btree physical storage layout. > > > > I created an unique index on a char(8) column with fill factor 100. > > > > A branch node should be filled with 126 slots, as each index entry > > > > uses 8 + 4 + 4 (column, page no, slot entry) bytes and there is a total > > > > auf 2020 bytes free on a page. > > > > However, looking at the index pages using oncheck -pp reveals that > > > > addr stamp nslots flag type frptr frcnt next prev > > > > 303b8a 2416107 125 50 BTREE 1524 20 82 0 > > > > slot ptr len flg > > > > 1 24 12 0 > > > > 2 36 12 0 > > > > ... >

The FILLFACTOR has to do with how full the LEAF nodes should be before splitting them at creation time. It has little to do with the fullness of the branch nodes except indirectly as the engine tries to keep the tree balanced. Art S. Kagel Christoph Liebig wrote: > > Hi ! > > I was experimenting with IUS Btree physical storage layout. > I created an unique index on a char(8) column with fill factor 100. > A branch node should be filled with 126 slots, as each index entry > uses 8 + 4 + 4 (column, page no, slot entry) bytes and there is a total > auf 2020 bytes free on a page. > However, looking at the index pages using oncheck -pp reveals that > addr stamp nslots flag type frptr frcnt next prev > 303b8a 2416107 125 50 BTREE 1524 20 82 0 > slot ptr len flg > 1 24 12 0 > 2 36 12 0 > ... > > There is space for one more slot (20 bytes free) but the slot is not > used, i.e. the page is not 100& filled but only 99% > > Any good reasons for that ?? > > --Chris