• C

b-tree with duplicate keys

If multiple identical keys are put into a b-tree, how would it be possible to retrieve all of them?  If for example, you have 5 keys per node, and you insert 15 keys into an empty tree, the root node would be split.  Then during a retrieval, the retrieval function would find all duplicate keys in the root node, and then continue searching in the right node, but the keys in the left node would be lost.

So, how is this problem usually handled?  Do you need to make some kind of separate nodes that store all duplicate keys?
chsalviaAsked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:

When a b-tree allows multiple keys, certain rules must be defined.

For instance, when searching for a key you have the option of allowing duplicates in only one of the branches or both.

Searches are much faster when you only have to search one direction.  Though there is added overhead in maintaining the tree.

- Assume that a page holds 10 keys and we add thse 10 keys to a page:   A B C D E F G H I J
- Now we add a duplicate key B.  The most straight-forward approach is to split the page at the duplicate.  Either before or after the target key.  Let's pick before.

These two pages now contain the keys: A and  B B C D E F G H I J

Now we want to add another B to the page.  It's full and the target key already occupies the first position.  We can force another split after the B and continue adding B keys to the page.   Or we can move right to overflow blocks.

We can create a left node and put the duplicate B keys in it.


Note that balancing a tree can be a bit more of a challenge when duplicate keys are allowed and this technique is used.  The trade off is that the search doesn't have to play "what if".  If there are duplicate keys the search will be able to quickly locate them.


Good Luck,
Kent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.