Is there is any limit for order in B-tree and B+tree?

Question

In a B tree and B+tree , If we specify the order as 5 then we can store the 4 keys in a single node and 5 pointers for that node.

It has any limit for setting the order in the above trees (or) its limit is infinite ?

Answer 1

You can design a system with any order you choose from 1 upwards. If you make the order too big, it becomes difficult to find the key in the node, and the tree will be just 1 or 2 levels deep.

For example, if the order is 1,000,000, then you'd need getting on for a trillion records before you split any nodes to the third level in the tree, and you'd probably never get to the fourth level. And you'd have to search through a million keys at each level to find where to go. Even with a binary search, that's up to 20 probes.

If you choose a smaller order, then your searches are smaller. For example, if the order is 32, you have at most 5 searches per level with a binary search to find the key and where to go next. Against this, each time you move down a level, you have to read a new page from disk (if it is a disk-backed B-tree). If it's in-memory, there's very little cost to that.

Often, you design the B-tree with a fixed page size, and tune the order based on the size of the keys and the size of the pointers. Big keys give you a smaller order; small keys give you a bigger order.