Hi

I thought I would check our DB after reading this thread. We have a
number of index depths of 3 (none above) our DB is using a page size of
8192. I show the details of a couple of the more commonly used tables
that have a high index depth. Are these bad, what sort of issues
(performance wise) will this cause. Is the best way to deal with it to
increase the page size further?

Sorry about all the questions. I seems like the bigger the table the
greater the index depth. Even if I increase the index depth, will I end
up in the same position again when the data size triples (for example).


A couple of the tables look like this....

Index FUNDSMOVEMENT_FUNDMOVREF (0)
Depth: 3, leaf buckets: 1099, nodes: 743285
Average data length: 6.05, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 1098

Index FUNDSMOVEMENT_TRANSREF (1)
Depth: 3, leaf buckets: 647, nodes: 743285
Average data length: 1.82, total dup: 520879, max dup: 154
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 646



STOCKMOVEMENT (304)

Index STOCKMOVEMENT_ITEMREF (2)
Depth: 3, leaf buckets: 582, nodes: 880824
Average data length: 0.32, total dup: 836283, max dup: 22476
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 10
60 - 79% = 0
80 - 99% = 572

Index STOCKMOVEMENT_REGIONREF (3)
Depth: 2, leaf buckets: 535, nodes: 880824
Average data length: 0.01, total dup: 880821, max dup: 574123
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 2
60 - 79% = 0
80 - 99% = 532

Index STOCKMOVEMENT_STKMOVREF (0)
Depth: 3, leaf buckets: 1303, nodes: 880824
Average data length: 6.04, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 1302

Index STOCKMOVEMENT_TRANSREF (1)
Depth: 3, leaf buckets: 770, nodes: 880824
Average data length: 1.84, total dup: 614822, max dup: 13769
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 769


Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd



Leyne, Sean wrote:

> Anderson,
>
>
>> Ok, so... as I understood, as far as the max index depths I have is 3,
>> there is no need to use a page size above 4k.
>>
>
> Actually, you SHOULD increase the page size. A depth of 3 is not good.
>
>
> Sean
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>