Subject Re: [IB-Architect] Next ODS change (was: System table change)
Author Dmitry Kuzmenko
Hello, Ivan!

Ivan Prenosil wrote:

> But increasing max index key size from 255 to 64K
> seems like proper/long term solution to me.
> Does it have any serious disadvantages ?

I think the main disadvangage will be index depth. I've made some calculations
for some company's database.
If page_size is 8K, and there is and index with average key length = 42, there will be
about 6 million leaf pages on the index third level. ~6 million only for condition that
pages does not have free spaces. It is not true in reality, and if key pages are filled
only 50%, there will be only 2 million leaf pages on the third level.

Let's do simple calculation for average key lenght = 256 characters (bytes).
8K / 256 = 32 keys on page. I.e. index root page can point to 32 second level index pointer pages.
32 index pointer pages can point to 32 * 32 = 1024 index leaf pages.
(1024 * 8K)/256 = 32768 keys on leaf pages.
This means depth equal to 3 will be for 32K keys (records). Depth = 4 - after 32K and until there are 1 million keys
(recods). And these are "high water mark" numbers (remember about page fill percentage).
After million keys (256 byte sized key, 100% filled key pages) index will get depth = 5.
Performance will be completely down.

So, who asked about 64K key size? :-)

Dmitry Kuzmenko, Epsylon Technologies.