B-tree height ✓ Answered: green (solid confidence) — John Miller III and Khaled Bentebal give the authoritative direct answer (hard limit of 20 B-tree levels; 'bad' is subjective, judged via oncheck -pT and typical level counts for a given row count); the thread then thoroughly applies this to the asker's own oversized 7-level index (2K pages, varchar(255) key), with the asker expressing clear understanding and a concrete remediation plan.

Advisory only.

Folks, IDS11.50FC8, Linux. What is the limitation of B-tree index levels? Any recommendation on the level mark of BAD? Thanks, Frank --f46d043c089a8768d004b64596d9

The current limitations on B-tree levels is 20. Bad is very subjective. If they are not causing a problem do not worry about them. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) (Embedded image moved to file: pic34398.gif) ids-bounces@iiug.org wrote on 01/11/2012 11:25:04 AM: > From: "FRANK" <yunyaoqu@gmail.com> > To: ids@iiug.org > Date: 01/11/2012 11:26 AM > Subject: B-tree height [25886] > Sent by: ids-bounces@iiug.org > > Folks, > > IDS11.50FC8, Linux. > > What is the limitation of B-tree index levels? Any recommendation on the > level mark of BAD? > > Thanks, > Frank > > --f46d043c089a8768d004b64596d9 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

?? I don't know of any limitations on the structure of a btree index. I also don't know what you mean by 'level mark of BAD'? Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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. On Wed, Jan 11, 2012 at 2:25 PM, FRANK <yunyaoqu@gmail.com> wrote: > Folks, > > IDS11.50FC8, Linux. > > What is the limitation of B-tree index levels? Any recommendation on the > level mark of BAD? > > Thanks, > Frank > > --f46d043c089a8768d004b64596d9 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3bb08dd3868704b645e74a

Thanks, John!! Frank On Wed, Jan 11, 2012 at 2:43 PM, John Miller iii <miller3@us.ibm.com> wrote: > The current limitations on B-tree levels is 20. > > Bad is very subjective. If they are not causing a problem do > not worry about them. > > John F. Miller III > STSM, Embedability Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > (Embedded image moved to file: pic34398.gif) > > ids-bounces@iiug.org wrote on 01/11/2012 11:25:04 AM: > > > From: "FRANK" <yunyaoqu@gmail.com> > > To: ids@iiug.org > > Date: 01/11/2012 11:26 AM > > Subject: B-tree height [25886] > > Sent by: ids-bounces@iiug.org > > > > Folks, > > > > IDS11.50FC8, Linux. > > > > What is the limitation of B-tree index levels? Any recommendation on the > > level mark of BAD? > > > > Thanks, > > Frank > > > > --f46d043c089a8768d004b64596d9 > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e6d975b8b2c0fb04b645e9ab

Art, I used to be able to find this info in Info-center. I searched it with key words: b-tree, index, level.... It was not only slow, but also did not tell me the truth :-(. The good thing is our John Miller knows the truth, I am happy now :-) Thanks, Frank On Wed, Jan 11, 2012 at 2:47 PM, Art Kagel <art.kagel@gmail.com> wrote: > ?? I don't know of any limitations on the structure of a btree index. I > also don't know what you mean by 'level mark of BAD'? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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. > > On Wed, Jan 11, 2012 at 2:25 PM, FRANK <yunyaoqu@gmail.com> wrote: > > > Folks, > > > > IDS11.50FC8, Linux. > > > > What is the limitation of B-tree index levels? Any recommendation on the > > level mark of BAD? > > > > Thanks, > > Frank > > > > --f46d043c089a8768d004b64596d9 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --e89a8f3bb08dd3868704b645e74a > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e6dd8d3a7c99b504b64602bf

HI Frank, The B-TREE index max level is 20. This limit is in the source code of the product. You cannot play around with it. It is not a parameter in a config file. Remember, the BTREE index level is exponential. The lower the level the better. That is why , we suggest to drop and recreate the indexes that are related to very volatile tables. evry so often to make them more compact and faster. There is no mark of bad or good for indexes as far as index levels. The lower the better. The BTREE (Balanced TREE) is a tree that has to be balanced at all times. That is why BTREEs can go down a level; done implicitely but the engine for you when you do an insert, delete or eben an update. That is why a insert can very fast sometimes when no rebalancing is needed and can be slower if you happen to be at the time when the engine has to rebalance the tree. For your info (depending on the size of the key), an index for table with a 300 million rows goes down to 6 levels. To go to 7 levels, your table has to be huge. To go to 8 levels, it is even bigger. I do not think that people using Informix have seen indexes with 9 levels. If so, we would like to know. If you run oncheck -pT on a table you can see how pages at an index level are filled. That light give you an idea if it needs to be recreated or not. Cordialement, Regards, Khaled Bentebal Directeur Général - ConsultiX Président UGIF - User Group Informix France IIUG - Board of Directors Tél: 33 (0) 1 39 12 18 00 Fax: 33 (0) 1 39 12 18 18 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 11/01/12 20:25, FRANK a écrit : > Folks, > > IDS11.50FC8, Linux. > > What is the limitation of B-tree index levels? Any recommendation on the > level mark of BAD? > > Thanks, > Frank > > --f46d043c089a8768d004b64596d9 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Thanks lot, Khaled! We have a table, it has only 5.5 millions rows, You know what, its index tree level is 7! Close to break your record! Why?The reason is: we have 2k page size, and the idex key size is varchar(255)! I am going to move this index to a larger page size dbspace.... Thanks, Frank On Wed, Jan 11, 2012 at 5:03 PM, Khaled Bentebal < khaled.bentebal@consult-ix.fr> wrote: > HI Frank, > > The B-TREE index max level is 20. This limit is in the source code of > the product. You cannot play around with it. It is not a parameter in a > config file. > > Remember, the BTREE index level is exponential. The lower the level the > better. That is why , we suggest to drop and recreate the indexes that > are related to very volatile tables. evry so often to make them more > compact and faster. > > There is no mark of bad or good for indexes as far as index levels. The > lower the better. The BTREE (Balanced TREE) is a tree that has to be > balanced at all times. That is why BTREEs can go down a level; done > implicitely but the engine for you when you do an insert, delete or eben > an update. That is why a insert can very fast sometimes when no > rebalancing is needed and can be slower if you happen to be at the time > when the engine has to rebalance the tree. > > For your info (depending on the size of the key), an index for table > with a 300 million rows goes down to 6 levels. To go to 7 levels, your > table has to be huge. To go to 8 levels, it is even bigger. I do not > think that people using Informix have seen indexes with 9 levels. If so, > we would like to know. > > If you run oncheck -pT on a table you can see how pages at an index > level are filled. That light give you an idea if it needs to be > recreated or not. > > Cordialement, Regards, > > Khaled Bentebal > Directeur Général - ConsultiX > Président UGIF - User Group Informix France > IIUG - Board of Directors > Tél: 33 (0) 1 39 12 18 00 > Fax: 33 (0) 1 39 12 18 18 > Mobile: 33 (0) 6 07 78 41 97 > Email: khaled.bentebal@consult-ix.fr > Site Web: www.consult-ix.fr > > Le 11/01/12 20:25, FRANK a écrit : > > Folks, > > > > IDS11.50FC8, Linux. > > > > What is the limitation of B-tree index levels? Any recommendation on the > > level mark of BAD? > > > > Thanks, > > Frank > > > > --f46d043c089a8768d004b64596d9 > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d0444e9f127adbc04b648218c

I would check to ensure the btree cleaners are running and configured properly as they will help to keep the index balanced and remove free space from the indexes. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) (Embedded image moved to file: pic30413.gif) ids-bounces@iiug.org wrote on 01/11/2012 02:27:02 PM: > From: "FRANK" <yunyaoqu@gmail.com> > To: ids@iiug.org > Date: 01/11/2012 02:28 PM > Subject: Re: B-tree height [25893] > Sent by: ids-bounces@iiug.org > > Thanks lot, Khaled! > > We have a table, it has only 5.5 millions rows, You know what, its in= dex > tree level is 7! Close to break your record! > > Why?The reason is: we have 2k page size, and the idex key size is > varchar(255)! > > I am going to move this index to a larger page size dbspace.... > > Thanks, > Frank > > On Wed, Jan 11, 2012 at 5:03 PM, Khaled Bentebal < > khaled.bentebal@consult-ix.fr> wrote: > > > HI Frank, > > > > The B-TREE index max level is 20. This limit is in the source code = of > > the product. You cannot play around with it. It is not a parameter = in a > > config file. > > > > Remember, the BTREE index level is exponential. The lower the level= the > > better. That is why , we suggest to drop and recreate the indexes t= hat > > are related to very volatile tables. evry so often to make them mor= e > > compact and faster. > > > > There is no mark of bad or good for indexes as far as index levels.= The > > lower the better. The BTREE (Balanced TREE) is a tree that has to b= e > > balanced at all times. That is why BTREEs can go down a level; done= > > implicitely but the engine for you when you do an insert, delete or= eben > > an update. That is why a insert can very fast sometimes when no > > rebalancing is needed and can be slower if you happen to be at the = time > > when the engine has to rebalance the tree. > > > > For your info (depending on the size of the key), an index for tabl= e > > with a 300 million rows goes down to 6 levels. To go to 7 levels, y= our > > table has to be huge. To go to 8 levels, it is even bigger. I do no= t > > think that people using Informix have seen indexes with 9 levels. I= f so, > > we would like to know. > > > > If you run oncheck -pT on a table you can see how pages at an index= > > level are filled. That light give you an idea if it needs to be > > recreated or not. > > > > Cordialement, Regards, > > > > Khaled Bentebal > > Directeur G=E9n=E9ral - ConsultiX > > Pr=E9sident UGIF - User Group Informix France > > IIUG - Board of Directors > > T=E9l: 33 (0) 1 39 12 18 00 > > Fax: 33 (0) 1 39 12 18 18 > > Mobile: 33 (0) 6 07 78 41 97 > > Email: khaled.bentebal@consult-ix.fr > > Site Web: www.consult-ix.fr > > > > Le 11/01/12 20:25, FRANK a =E9crit : > > > Folks, > > > > > > IDS11.50FC8, Linux. > > > > > > What is the limitation of B-tree index levels? Any recommendation= on the > > > level mark of BAD? > > > > > > Thanks, > > > Frank > > > > > > --f46d043c089a8768d004b64596d9 > > > > > > > > > > > > > > ***********************************************************************= ******** > > > Forum Note: Use "Reply" to post a response in the discussion foru= m. > > > > > > > > > > > > > > > ***********************************************************************= ******** > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > > > > > --f46d0444e9f127adbc04b648218c > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum.= >=

Frank, Well indexing a column of type varchar(255) is not the greatest thing but if you have too ... Before you go ahead and move the index to another dbspace based on a 4K, 8K or 16K page, try to copy the source table to a raw table with the same structure, create the same index on the raw table. This will give you an idea how many levels you really need and how many pages it occupies. By the way, when you create the idex, do not gorget to set PSORT_NPROCS et PDQPRIORITY to 100 if you use the Ultimate edition; otherwise you do not have access to PDQ nor fragmentation. This will make the index creation faster. You can also consider fragenting the index to lower the levels since the levels are per fragment. On the side, I would recommand to have a char instead of a varchar for the column, and you have to use the varchar, specify a max and also a min value (ex: col1 varchar(255,30)) with the min value being the most common min value . This will lake updates faster since no movement of rows will take place within a page unless the varchar groes beyong the min value. Cordialement, Regards, Khaled Bentebal Directeur Général - ConsultiX Président UGIF - User Group Informix France IIUG - Board of Directors Tél: 33 (0) 1 39 12 18 00 Fax: 33 (0) 1 39 12 18 18 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 11/01/12 23:27, FRANK a écrit : > Thanks lot, Khaled! > > We have a table, it has only 5.5 millions rows, You know what, its index > tree level is 7! Close to break your record! > > Why?The reason is: we have 2k page size, and the idex key size is > varchar(255)! > > I am going to move this index to a larger page size dbspace.... > > Thanks, > Frank > > On Wed, Jan 11, 2012 at 5:03 PM, Khaled Bentebal< > khaled.bentebal@consult-ix.fr> wrote: > >> HI Frank, >> >> The B-TREE index max level is 20. This limit is in the source code of >> the product. You cannot play around with it. It is not a parameter in a >> config file. >> >> Remember, the BTREE index level is exponential. The lower the level the >> better. That is why , we suggest to drop and recreate the indexes that >> are related to very volatile tables. evry so often to make them more >> compact and faster. >> >> There is no mark of bad or good for indexes as far as index levels. The >> lower the better. The BTREE (Balanced TREE) is a tree that has to be >> balanced at all times. That is why BTREEs can go down a level; done >> implicitely but the engine for you when you do an insert, delete or eben >> an update. That is why a insert can very fast sometimes when no >> rebalancing is needed and can be slower if you happen to be at the time >> when the engine has to rebalance the tree. >> >> For your info (depending on the size of the key), an index for table >> with a 300 million rows goes down to 6 levels. To go to 7 levels, your >> table has to be huge. To go to 8 levels, it is even bigger. I do not >> think that people using Informix have seen indexes with 9 levels. If so, >> we would like to know. >> >> If you run oncheck -pT on a table you can see how pages at an index >> level are filled. That light give you an idea if it needs to be >> recreated or not. >> >> Cordialement, Regards, >> >> Khaled Bentebal >> Directeur Général - ConsultiX >> Président UGIF - User Group Informix France >> IIUG - Board of Directors >> Tél: 33 (0) 1 39 12 18 00 >> Fax: 33 (0) 1 39 12 18 18 >> Mobile: 33 (0) 6 07 78 41 97 >> Email: khaled.bentebal@consult-ix.fr >> Site Web: www.consult-ix.fr >> >> Le 11/01/12 20:25, FRANK a écrit : >>> Folks, >>> >>> IDS11.50FC8, Linux. >>> >>> What is the limitation of B-tree index levels? Any recommendation on the >>> level mark of BAD? >>> >>> Thanks, >>> Frank >>> >>> --f46d043c089a8768d004b64596d9 >>> >>> >>> >> > ******************************************************************************* >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >>> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --f46d0444e9f127adbc04b648218c > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

John, The table is insert Only, no update or delete. Do you think the btree cleaner will impact ? Thanks, Frank On Wed, Jan 11, 2012 at 6:03 PM, John Miller iii <miller3@us.ibm.com> wrote: > I would check to ensure the btree cleaners are running > and configured properly as they will help to keep > the index balanced and remove free space from > the indexes. > > John F. Miller III > STSM, Embedability Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > (Embedded image moved to file: pic30413.gif) > > ids-bounces@iiug.org wrote on 01/11/2012 02:27:02 PM: > > > From: "FRANK" <yunyaoqu@gmail.com> > > To: ids@iiug.org > > Date: 01/11/2012 02:28 PM > > Subject: Re: B-tree height [25893] > > Sent by: ids-bounces@iiug.org > > > > Thanks lot, Khaled! > > > > We have a table, it has only 5.5 millions rows, You know what, its in= > dex > > tree level is 7! Close to break your record! > > > > Why?The reason is: we have 2k page size, and the idex key size is > > varchar(255)! > > > > I am going to move this index to a larger page size dbspace.... > > > > Thanks, > > Frank > > > > On Wed, Jan 11, 2012 at 5:03 PM, Khaled Bentebal < > > khaled.bentebal@consult-ix.fr> wrote: > > > > > HI Frank, > > > > > > The B-TREE index max level is 20. This limit is in the source code = > of > > > the product. You cannot play around with it. It is not a parameter = > in a > > > > config file. > > > > > > Remember, the BTREE index level is exponential. The lower the level= > the > > > > better. That is why , we suggest to drop and recreate the indexes t= > hat > > > are related to very volatile tables. evry so often to make them mor= > e > > > compact and faster. > > > > > > There is no mark of bad or good for indexes as far as index levels.= > The > > > > lower the better. The BTREE (Balanced TREE) is a tree that has to b= > e > > > balanced at all times. That is why BTREEs can go down a level; done= > > > > implicitely but the engine for you when you do an insert, delete or= > > eben > > > an update. That is why a insert can very fast sometimes when no > > > rebalancing is needed and can be slower if you happen to be at the = > time > > > > when the engine has to rebalance the tree. > > > > > > For your info (depending on the size of the key), an index for tabl= > e > > > with a 300 million rows goes down to 6 levels. To go to 7 levels, y= > our > > > table has to be huge. To go to 8 levels, it is even bigger. I do no= > t > > > think that people using Informix have seen indexes with 9 levels. I= > f > so, > > > we would like to know. > > > > > > If you run oncheck -pT on a table you can see how pages at an index= > > > > level are filled. That light give you an idea if it needs to be > > > recreated or not. > > > > > > Cordialement, Regards, > > > > > > Khaled Bentebal > > > Directeur G=E9n=E9ral - ConsultiX > > > Pr=E9sident UGIF - User Group Informix France > > > IIUG - Board of Directors > > > T=E9l: 33 (0) 1 39 12 18 00 > > > Fax: 33 (0) 1 39 12 18 18 > > > Mobile: 33 (0) 6 07 78 41 97 > > > Email: khaled.bentebal@consult-ix.fr > > > Site Web: www.consult-ix.fr > > > > > > Le 11/01/12 20:25, FRANK a =E9crit : > > > > Folks, > > > > > > > > IDS11.50FC8, Linux. > > > > > > > > What is the limitation of B-tree index levels? Any recommendation= > on > the > > > > level mark of BAD? > > > > > > > > Thanks, > > > > Frank > > > > > > > > --f46d043c089a8768d004b64596d9 > > > > > > > > > > > > > > > > > > > > > ***********************************************************************= > ******** > > > > > Forum Note: Use "Reply" to post a response in the discussion foru= > m. > > > > > > > > > > > > > > > > > > > > > > > ***********************************************************************= > ******** > > > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > > > > > > > > > > --f46d0444e9f127adbc04b648218c > > > > > > > ***********************************************************************= > ******** > > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > >= > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e6dd8d3ab3f53d04b64a27c8

Yes, it is the btree scanner that maintains the index's balance. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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. On Wed, Jan 11, 2012 at 7:52 PM, FRANK <yunyaoqu@gmail.com> wrote: > John, > The table is insert Only, no update or delete. > Do you think the btree cleaner will impact ? > > Thanks, > Frank > > On Wed, Jan 11, 2012 at 6:03 PM, John Miller iii <miller3@us.ibm.com> > wrote: > > > I would check to ensure the btree cleaners are running > > and configured properly as they will help to keep > > the index balanced and remove free space from > > the indexes. > > > > John F. Miller III > > STSM, Embedability Architect > > miller3@us.ibm.com > > 503-578-5645 > > IBM Informix Dynamic Server (IDS) > > (Embedded image moved to file: pic30413.gif) > > > > ids-bounces@iiug.org wrote on 01/11/2012 02:27:02 PM: > > > > > From: "FRANK" <yunyaoqu@gmail.com> > > > To: ids@iiug.org > > > Date: 01/11/2012 02:28 PM > > > Subject: Re: B-tree height [25893] > > > Sent by: ids-bounces@iiug.org > > > > > > Thanks lot, Khaled! > > > > > > We have a table, it has only 5.5 millions rows, You know what, its in= > > dex > > > tree level is 7! Close to break your record! > > > > > > Why?The reason is: we have 2k page size, and the idex key size is > > > varchar(255)! > > > > > > I am going to move this index to a larger page size dbspace.... > > > > > > Thanks, > > > Frank > > > > > > On Wed, Jan 11, 2012 at 5:03 PM, Khaled Bentebal < > > > khaled.bentebal@consult-ix.fr> wrote: > > > > > > > HI Frank, > > > > > > > > The B-TREE index max level is 20. This limit is in the source code = > > of > > > > the product. You cannot play around with it. It is not a parameter = > > in a > > > > > > config file. > > > > > > > > Remember, the BTREE index level is exponential. The lower the level= > > the > > > > > > better. That is why , we suggest to drop and recreate the indexes t= > > hat > > > > are related to very volatile tables. evry so often to make them mor= > > e > > > > compact and faster. > > > > > > > > There is no mark of bad or good for indexes as far as index levels.= > > The > > > > > > lower the better. The BTREE (Balanced TREE) is a tree that has to b= > > e > > > > balanced at all times. That is why BTREEs can go down a level; done= > > > > > > implicitely but the engine for you when you do an insert, delete or= > > > > eben > > > > an update. That is why a insert can very fast sometimes when no > > > > rebalancing is needed and can be slower if you happen to be at the = > > time > > > > > > when the engine has to rebalance the tree. > > > > > > > > For your info (depending on the size of the key), an index for tabl= > > e > > > > with a 300 million rows goes down to 6 levels. To go to 7 levels, y= > > our > > > > table has to be huge. To go to 8 levels, it is even bigger. I do no= > > t > > > > think that people using Informix have seen indexes with 9 levels. I= > > f > > so, > > > > we would like to know. > > > > > > > > If you run oncheck -pT on a table you can see how pages at an index= > > > > > > level are filled. That light give you an idea if it needs to be > > > > recreated or not. > > > > > > > > Cordialement, Regards, > > > > > > > > Khaled Bentebal > > > > Directeur G=E9n=E9ral - ConsultiX > > > > Pr=E9sident UGIF - User Group Informix France > > > > IIUG - Board of Directors > > > > T=E9l: 33 (0) 1 39 12 18 00 > > > > Fax: 33 (0) 1 39 12 18 18 > > > > Mobile: 33 (0) 6 07 78 41 97 > > > > Email: khaled.bentebal@consult-ix.fr > > > > Site Web: www.consult-ix.fr > > > > > > > > Le 11/01/12 20:25, FRANK a =E9crit : > > > > > Folks, > > > > > > > > > > IDS11.50FC8, Linux. > > > > > > > > > > What is the limitation of B-tree index levels? Any recommendation= > > on > > the > > > > > level mark of BAD? > > > > > > > > > > Thanks, > > > > > Frank > > > > > > > > > > --f46d043c089a8768d004b64596d9 > > > > > > > > > > > > > > > > > > > > > > > > > > > > ***********************************************************************= > > ******** > > > > > > > Forum Note: Use "Reply" to post a response in the discussion foru= > > m. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ***********************************************************************= > > ******** > > > > > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > > > > > > > > > > > > > > > --f46d0444e9f127adbc04b648218c > > > > > > > > > > > ***********************************************************************= > > ******** > > > > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > > > >= > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --0016e6dd8d3ab3f53d04b64a27c8 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e89143a3e8904b64c0eca

Hello Fank, Am 12.01.2012 01:52, schrieb FRANK: > John, > The table is insert Only, no update or delete. > Do you think the btree cleaner will impact ? yes. You still will have (many) page split operations, depending on the way and sequence you inserting data. To help reduce the number of datapages in this table, and if you on version 11 look into $ONCONFIG Parameter MAX_FILL_DATA_PAGES. Best would be to redesign, though. Have an sequence number, type BIGSERIAL for the primary key, and the first 5-10 characters of your VARCHAR as a column to build a non unique index replacing your varchar index. The result may be comparable or even better than putting all data onto SSD storage ;) HTH dic_k > > Thanks, > Frank > > On Wed, Jan 11, 2012 at 6:03 PM, John Miller iii<miller3@us.ibm.com> wrote: > >> I would check to ensure the btree cleaners are running >> and configured properly as they will help to keep >> the index balanced and remove free space from >> the indexes. >> >> John F. Miller III >> STSM, Embedability Architect >> miller3@us.ibm.com >> 503-578-5645 >> IBM Informix Dynamic Server (IDS) >> (Embedded image moved to file: pic30413.gif) >> >> ids-bounces@iiug.org wrote on 01/11/2012 02:27:02 PM: >> >>> From: "FRANK"<yunyaoqu@gmail.com> >>> To: ids@iiug.org >>> Date: 01/11/2012 02:28 PM >>> Subject: Re: B-tree height [25893] >>> Sent by: ids-bounces@iiug.org >>> >>> Thanks lot, Khaled! >>> >>> We have a table, it has only 5.5 millions rows, You know what, its in= >> dex >>> tree level is 7! Close to break your record! >>> >>> Why?The reason is: we have 2k page size, and the idex key size is >>> varchar(255)! >>> >>> I am going to move this index to a larger page size dbspace.... >>> >>> Thanks, >>> Frank >>> >>> On Wed, Jan 11, 2012 at 5:03 PM, Khaled Bentebal< >>> khaled.bentebal@consult-ix.fr> wrote: >>> >>>> HI Frank, >>>> >>>> The B-TREE index max level is 20. This limit is in the source code = >> of >>>> the product. You cannot play around with it. It is not a parameter = >> in a >> >>>> config file. >>>> >>>> Remember, the BTREE index level is exponential. The lower the level= >> the >> >>>> better. That is why , we suggest to drop and recreate the indexes t= >> hat >>>> are related to very volatile tables. evry so often to make them mor= >> e >>>> compact and faster. >>>> >>>> There is no mark of bad or good for indexes as far as index levels.= >> The >> >>>> lower the better. The BTREE (Balanced TREE) is a tree that has to b= >> e >>>> balanced at all times. That is why BTREEs can go down a level; done= >> >>>> implicitely but the engine for you when you do an insert, delete or= >> >> eben >>>> an update. That is why a insert can very fast sometimes when no >>>> rebalancing is needed and can be slower if you happen to be at the = >> time >> >>>> when the engine has to rebalance the tree. >>>> >>>> For your info (depending on the size of the key), an index for tabl= >> e >>>> with a 300 million rows goes down to 6 levels. To go to 7 levels, y= >> our >>>> table has to be huge. To go to 8 levels, it is even bigger. I do no= >> t >>>> think that people using Informix have seen indexes with 9 levels. I= >> f >> so, >>>> we would like to know. >>>> >>>> If you run oncheck -pT on a table you can see how pages at an index= >> >>>> level are filled. That light give you an idea if it needs to be >>>> recreated or not. >>>> >>>> Cordialement, Regards, >>>> >>>> Khaled Bentebal >>>> Directeur G=E9n=E9ral - ConsultiX >>>> Pr=E9sident UGIF - User Group Informix France >>>> IIUG - Board of Directors >>>> T=E9l: 33 (0) 1 39 12 18 00 >>>> Fax: 33 (0) 1 39 12 18 18 >>>> Mobile: 33 (0) 6 07 78 41 97 >>>> Email: khaled.bentebal@consult-ix.fr >>>> Site Web: www.consult-ix.fr >>>> >>>> Le 11/01/12 20:25, FRANK a =E9crit : >>>>> Folks, >>>>> >>>>> IDS11.50FC8, Linux. >>>>> >>>>> What is the limitation of B-tree index levels? Any recommendation= >> on >> the >>>>> level mark of BAD? >>>>> >>>>> Thanks, >>>>> Frank >>>>> >>>>> --f46d043c089a8768d004b64596d9 >>>>> >>>>> >>>>> >>>> >>>> >>> >> ***********************************************************************= >> ******** >> >>>>> Forum Note: Use "Reply" to post a response in the discussion foru= >> m. >>>>> >>>>> >>>> >>>> >>>> >>>> >>> >> ***********************************************************************= >> ******** >> >>>> Forum Note: Use "Reply" to post a response in the discussion forum.= >> >>>> >>>> >>> >>> --f46d0444e9f127adbc04b648218c >>> >>> >>> >> ***********************************************************************= >> ******** >> >>> Forum Note: Use "Reply" to post a response in the discussion forum.= >> >>> = >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --0016e6dd8d3ab3f53d04b64a27c8 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe

Hi Khaled, Am 11.01.2012 23:03, schrieb Khaled Bentebal: [ ... snip ... ] > > For your info (depending on the size of the key), an index for table > with a 300 million rows goes down to 6 levels. To go to 7 levels, your > table has to be huge. To go to 8 levels, it is even bigger. I do not > think that people using Informix have seen indexes with 9 levels. If so, > we would like to know. > [ ... snip ... ] Our customers tend to have some very large tables (nrows). Page size is 2K. With a 23 Byte index item, a non unique index having 4 fragments of approximately the same number of rows a table having nrows > 3 * 10 ** 9 (american billions) in total had 7 index levels. Changing the page size to 6K they now have 6 index levels. Why 6K and not 8K? 255*(23+4) > 6K! [ on a sytem with 4K default page size this is not possible ] The number of update operations on this table is very small compared to deletes. No of deletes is small compared to no of inserts. The ratio used_pages vs free_pages in the index is better than 5000:1. Another customer has a data entry / data collecting system and data comes from material quality testing devices. There a unique index of type BIGINT and nrows > 500 * 10 ** 9 had 7 index levels. After fragmentation and switching the index to 4K page size it now has 5 index levels. We keep it there, by refragmentaion and have to do this 2 times a year. No updates but some deletes are occuring. Up to 250K inserts per minute. Used_pages vs free_pages is better than 40000:1 After more than 20 years in huge databases, I still have to see a very huge table having a high number of updates occurring..... dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe

The impact would be minor. To view how well the indexes are packed on a index pages run oncheck -pT. Index Usage Report for index mon_prof_idx1 on sysadmin:informix.mon_prof Average Average Average Level Total No. Keys Free Bytes Del Keys ----- -------- -------- ---------- -------- 1 1 37 1436 2 37 62 1024 3 2303 116 32 1 ----- -------- -------- ---------- -------- Total 2341 116 48 4030 John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) (Embedded image moved to file: pic09561.gif) ids-bounces@iiug.org wrote on 01/11/2012 04:52:07 PM: > From: "FRANK" <yunyaoqu@gmail.com> > To: ids@iiug.org > Date: 01/11/2012 04:53 PM > Subject: Re: B-tree height [25896] > Sent by: ids-bounces@iiug.org > > John, > The table is insert Only, no update or delete. > Do you think the btree cleaner will impact ? > > Thanks, > Frank > > On Wed, Jan 11, 2012 at 6:03 PM, John Miller iii <miller3@us.ibm.com> wrote: > > > I would check to ensure the btree cleaners are running > > and configured properly as they will help to keep > > the index balanced and remove free space from > > the indexes. > > > > John F. Miller III > > STSM, Embedability Architect > > miller3@us.ibm.com > > 503-578-5645 > > IBM Informix Dynamic Server (IDS) > > (Embedded image moved to file: pic30413.gif) > > > > ids-bounces@iiug.org wrote on 01/11/2012 02:27:02 PM: > > > > > From: "FRANK" <yunyaoqu@gmail.com> > > > To: ids@iiug.org > > > Date: 01/11/2012 02:28 PM > > > Subject: Re: B-tree height [25893] > > > Sent by: ids-bounces@iiug.org > > > > > > Thanks lot, Khaled! > > > > > > We have a table, it has only 5.5 millions rows, You know what, its in= > > dex > > > tree level is 7! Close to break your record! > > > > > > Why?The reason is: we have 2k page size, and the idex key size is > > > varchar(255)! > > > > > > I am going to move this index to a larger page size dbspace.... > > > > > > Thanks, > > > Frank > > > > > > On Wed, Jan 11, 2012 at 5:03 PM, Khaled Bentebal < > > > khaled.bentebal@consult-ix.fr> wrote: > > > > > > > HI Frank, > > > > > > > > The B-TREE index max level is 20. This limit is in the source code = > > of > > > > the product. You cannot play around with it. It is not a parameter = > > in a > > > > > > config file. > > > > > > > > Remember, the BTREE index level is exponential. The lower the level= > > the > > > > > > better. That is why , we suggest to drop and recreate the indexes t= > > hat > > > > are related to very volatile tables. evry so often to make them mor= > > e > > > > compact and faster. > > > > > > > > There is no mark of bad or good for indexes as far as index levels.= > > The > > > > > > lower the better. The BTREE (Balanced TREE) is a tree that has to b= > > e > > > > balanced at all times. That is why BTREEs can go down a level; done= > > > > > > implicitely but the engine for you when you do an insert, delete or= > > > > eben > > > > an update. That is why a insert can very fast sometimes when no > > > > rebalancing is needed and can be slower if you happen to be at the = > > time > > > > > > when the engine has to rebalance the tree. > > > > > > > > For your info (depending on the size of the key), an index for tabl= > > e > > > > with a 300 million rows goes down to 6 levels. To go to 7 levels, y= > > our > > > > table has to be huge. To go to 8 levels, it is even bigger. I do no= > > t > > > > think that people using Informix have seen indexes with 9 levels. I= > > f > > so, > > > > we would like to know. > > > > > > > > If you run oncheck -pT on a table you can see how pages at an index= > > > > > > level are filled. That light give you an idea if it needs to be > > > > recreated or not. > > > > > > > > Cordialement, Regards, > > > > > > > > Khaled Bentebal > > > > Directeur G=E9n=E9ral - ConsultiX > > > > Pr=E9sident UGIF - User Group Informix France > > > > IIUG - Board of Directors > > > > T=E9l: 33 (0) 1 39 12 18 00 > > > > Fax: 33 (0) 1 39 12 18 18 > > > > Mobile: 33 (0) 6 07 78 41 97 > > > > Email: khaled.bentebal@consult-ix.fr > > > > Site Web: www.consult-ix.fr > > > > > > > > Le 11/01/12 20:25, FRANK a =E9crit : > > > > > Folks, > > > > > > > > > > IDS11.50FC8, Linux. > > > > > > > > > > What is the limitation of B-tree index levels? Any recommendation= > > on > > the > > > > > level mark of BAD? > > > > > > > > > > Thanks, > > > > > Frank > > > > > > > > > > --f46d043c089a8768d004b64596d9 > > > > > > > > > > > > > > > > > > > > > > > > > > > > ***********************************************************************= > > ******** > > > > > > > Forum Note: Use "Reply" to post a response in the discussion foru= > > m. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ***********************************************************************= > > ******** > > > > > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > > > > > > > > > > > > > > > --f46d0444e9f127adbc04b648218c > > > > > > > > > > > ***********************************************************************= > > ******** > > > > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > > > >= > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --0016e6dd8d3ab3f53d04b64a27c8 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

On Thu, Jan 12, 2012 at 03:04, Richard Kofler <richard.kofler@chello.at>wrote: > > Page size is 2K. > With a 23 Byte index item, a non unique index having 4 fragments of > approximately the same number of rows a table having > nrows > 3 * 10 ** 9 (american billions) in total had 7 index levels. > > Changing the page size to 6K they now have 6 index levels. > Why 6K and not 8K? 255*(23+4) > 6K! > [ on a sytem with 4K default page size this is not possible ] > Within the last couple of years, I was told that indexes are not limited to 255 keys per page, so you could use 8K or larger pages with a 23 byte key and still get full use of the pages. This might allow you to decrease your tree height from 6 levels. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --14dae9340b39278cf904b662d016

Jonathan is correct. Maybe, someday, you won't have to drop and recreate your indexes to compact them any more. You'll should be able to reorg them (and compress the keys like we compress data) as well. Stay tuned!!!!