compressing indexes

Posted in 2004

Floyd Wellershaus — Wed, 29 Dec 2004 11:45:33 — source: IIUG Forums & Mailing Lists

Does anybody know how to lower the number of levels in a btree index ? We have some that have 4 and 5 levels, and I read that is a performance hit. Thanks, ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Work: 703-733-4126 Pager: 703-705-9241 Email Pager: 7037059241@my2way.com Home: 703-430-0805 Cell: 703-477-6045 ========================

↪ replying to Floyd Wellershaus

Khaled Bentebal — Wed, 29 Dec 2004 15:15:47 — source: IIUG Forums & Mailing Lists

Hi, The numbre of levels in a B-TREE depends on the number of items in the index, the size of the key, and the life of the index (B-TREE). There is no specific parameter except may be for the FILLFACTOR onconfig parameter. If you want to have the most optimized B-TREE structure, set FILLFACTOR to 100 (but this won't be great for indexes that are very volatile), use the minimum size fields for your index, and recreate (drop the index and create it again) the index whenever you can. As the index lives, its depth might increase to keep it balanced at all times. So the best thing you can do it to recreate the index whenever possible. You should realize that for a single access through an index that is 4 or 5, you won't notice the difference, however for a batch that does a lot (thousands to millions ) of accesses, you will notice a difference. I hope that this helps. Khaled Bentebal Tél: 33 (0) 1 39 72 17 00 Fax: 33 (0) 1 39 72 17 01 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: http://www.consult-ix.fr ----- Original Message ----- From: "Floyd Welle...." <fwellers@yahoo.com> To: <ids@iiug.org> Sent: Wednesday, December 29, 2004 12:45 PM Subject: compressing indexes [3929] > Does anybody know how to lower the number of levels in a btree index ? > We have some that have 4 and 5 levels, and I read that is a performance hit. > > Thanks, > > > > ======================== > -<<Floyd Wellershaus>>- > Database Administrator > Unix Administrator > > email: fwellers@yahoo.com > Work: 703-733-4126 > Pager: 703-705-9241 > Email Pager: 7037059241@my2way.com > Home: 703-430-0805 > Cell: 703-477-6045 > ======================== > > > >

↪ replying to Khaled Bentebal

Uri Haham — Thu, 30 Dec 2004 08:51:58 — source: IIUG Forums & Mailing Lists

Hi You can consider to partition the index, use range or expression on the first keys of the index. A good expression function for numeric and date can be mod function. By partition the index you divide the index into several smaller indexes. If you partition using the first keys of the index the optimizer use only the relevant partition of the index. Uri -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of Khaled Bentebal Sent: Wednesday, December 29, 2004 5:16 PM To: ids@iiug.org Subject: Re: compressing indexes [3935] Hi, The numbre of levels in a B-TREE depends on the number of items in the index, the size of the key, and the life of the index (B-TREE). There is no specific parameter except may be for the FILLFACTOR onconfig parameter. If you want to have the most optimized B-TREE structure, set FILLFACTOR to 100 (but this won't be great for indexes that are very volatile), use the minimum size fields for your index, and recreate (drop the index and create it again) the index whenever you can. As the index lives, its depth might increase to keep it balanced at all times. So the best thing you can do it to recreate the index whenever possible. You should realize that for a single access through an index that is 4 or 5, you won't notice the difference, however for a batch that does a lot (thousands to millions ) of accesses, you will notice a difference. I hope that this helps. Khaled Bentebal Tél: 33 (0) 1 39 72 17 00 Fax: 33 (0) 1 39 72 17 01 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: http://www.consult-ix.fr ----- Original Message ----- From: "Floyd Welle...." <fwellers@yahoo.com> To: <ids@iiug.org> Sent: Wednesday, December 29, 2004 12:45 PM Subject: compressing indexes [3929] > Does anybody know how to lower the number of levels in a btree index ? > We have some that have 4 and 5 levels, and I read that is a performance hit. > > Thanks, > > > > ======================== > -<<Floyd Wellershaus>>- > Database Administrator > Unix Administrator > > email: fwellers@yahoo.com > Work: 703-733-4126 > Pager: 703-705-9241 > Email Pager: 7037059241@my2way.com > Home: 703-430-0805 > Cell: 703-477-6045 > ======================== > > > > *********************************************** This Mail Was Scanned By Mail-seCure System in Matrix Herzeliya ***********************************************