FanOut of Index

Posted in 2005

PARAMESHWAR PATHRI — Thu, 10 Mar 2005 04:57:36 — source: IIUG Forums & Mailing Lists

Hi, I want to know how the fanout of an index is calculated for Btree index. Bye.

↪ replying to PARAMESHWAR PATHRI

Jack Parker — Thu, 10 Mar 2005 12:51:36 — source: IIUG Forums & Mailing Lists

Check the performance guide. Pulled from my sizing sheet: Number of leaves (G23) is: =ROUND((((((B23+9+E23)*D23)*C23)+(((5+E23)*(1-D23))*C23))/F23)/Page_Use;0) Leaves per branch (N23) is: =ROUND((((((B23+9+E23)*D23)*C23)+(((5+E23)*(1-D23))*C23))/F23)/Page_Use;0) Branch pages and root (O23) is: =ROUND((((((B23+9+E23)*D23)*C23)+(((5+E23)*(1-D23))*C23))/F23)/Page_Use;0) Total pages (P23) is: =ROUND((((((B23+9+E23)*D23)*C23)+(((5+E23)*(1-D23))*C23))/F23)/Page_Use;0) Where B23 is total size of columns in index C23 is numer of rows in table D23 is % uniqueness E23 is either 0 for attached or 4 for detached F23 is fill factor This does not account for the "+" in the b+tree., so you need to subtract an extra 4 bytes per branch page. And yes, there is a circular calculation in here. Not sure what you mean by fanout. Do you mean how deep the index is? Should by in the sysindexes table. j. ----- Original Message ----- From: "PARAMESHWAR...." <pcdudyala@yahoo.com> To: <ids@iiug.org> Sent: Thursday, March 10, 2005 12:57 AM Subject: FanOut of Index [4462] > Hi, > I want to know how the fanout of an index is calculated for Btree index. > > > Bye. > >

↪ replying to Jack Parker

Jack Parker — Fri, 11 Mar 2005 01:56:27 — source: IIUG Forums & Mailing Lists

I forgot. Page_use is the number of bytes left over on your page after header bytes are accounted for. So use page_size - 28 for IDS, page_size - 60 for xps. In other words if you have a 2K page under IDS, your Page_use = 2020. j. ----- Original Message ----- From: "Jack Parker" <vze2qjg5@verizon.net> To: <ids@iiug.org> Sent: Thursday, March 10, 2005 8:51 AM Subject: Re: FanOut of Index [4465] > Check the performance guide. > > Pulled from my sizing sheet: > > Number of leaves (G23) is: > =ROUND((((((B23+9+E23)*D23)*C23)+(((5+E23)*(1-D23))*C23))/F23)/Page_Use;0) > > Leaves per branch (N23) is: > > =ROUND((((((B23+9+E23)*D23)*C23)+(((5+E23)*(1-D23))*C23))/F23)/Page_Use;0) > > Branch pages and root (O23) is: > > =ROUND((((((B23+9+E23)*D23)*C23)+(((5+E23)*(1-D23))*C23))/F23)/Page_Use;0) > > Total pages (P23) is: > > =ROUND((((((B23+9+E23)*D23)*C23)+(((5+E23)*(1-D23))*C23))/F23)/Page_Use;0) > > Where > > B23 is total size of columns in index > > C23 is numer of rows in table > > D23 is % uniqueness > > E23 is either 0 for attached or 4 for detached > > F23 is fill factor > > This does not account for the "+" in the b+tree., so you need to subtract an > extra 4 bytes per branch page. And yes, there is a circular calculation in > here. > > Not sure what you mean by fanout. Do you mean how deep the index is? > Should by in the sysindexes table. > > j. > > ----- Original Message ----- > From: "PARAMESHWAR...." <pcdudyala@yahoo.com> > To: <ids@iiug.org> > Sent: Thursday, March 10, 2005 12:57 AM > Subject: FanOut of Index [4462] > > > > Hi, > > I want to know how the fanout of an index is calculated for Btree index. > > > > > > Bye. > > > > > > > >