### Deleting a key from a B/B+-tree

• Assumption

• Assumption:

 We assume the search keys in the B-tree are unique I.e.: no duplicate keys stored in the B-tree

• Deleting search key x and its record pointer from a B+-tree

• Given:

 A B+-tree Search key value x

Goal:

 Delete x and its record pointer from the B+-tree

• Delete Algorithm for leaf node:

 ``` /* ======================================================== Delete( x ) from B+-tree ======================================================== */ Delete( x ) { Use the B-tree search algorithm to find the leaf node L where the x belongs if ( x not found ) { return; } else { Shift keys to delete the key x and its record ptr from the node } if ( L has ≥ ⌊(n+1)/2⌋ keys /* At least half full*/ ) { return; // Done } else { /* --------------------------------------------------------- L underflows: fix the size of L with transfer or merge --------------------------------------------------------- */ if ( leftSibling(L) has ≥ ⌊(n+1)/2⌋ + 1 keys ) { 1. transfer last key and ptr from leftSibling(L) into L; 2. update the search key in the parent node; } else if ( rightSibling(L) has ≥ ⌊(n+1)/2⌋ + 1 keys ) { 1. transfer first key and ptr from rightSibling(L) into L; 2. update the search key in the parent node; } else if ( leftSibling(L) exists ) { 1. Merge leftSibling(L) + L + L's last pointer into leftSibling(L); // Node L will be deleted !! 2. Delete key and right subtree in parent node; // discuss next !! } else { 1. Merge L + rightSibling(L) + last pointer into L; // rightSibling(L) will be deleted !!! 2. Delete key and right subtree in parent node; // discuss next !! } } ```

Notice that:

• The delete operation will trigger:

 A deletion of the key x and its right subtree pointer from an internal node

• The delete procedure in an internal node is similar to the delete procedure in a leaf node

But differs in a few minor details.... --- discussed next !!!

• Deleting search key x and its right subtree ptr from an internal node of a B+-tree

• Given:

 An internal node of a B+-tree Search key value x and its right subtree link

Goal:

 Delete x and its right subtree link from internal node of the B+-tree

• Delete Algorithm for internal node:

 ``` /* ======================================================== Delete( x, rx ) from a node N in B+-tree ======================================================== */ Delete( x, rx, N ) { Delete x, rx from node N; /* ==================================== Check for underflow condition... ==================================== */ if ( N has ≥ ⌊(n+1)/2⌋ pointers /* At least half full*/ ) { return; // Done } else { /* --------------------------------------------------------- N underflowed: fix the size of N with transfer or merge --------------------------------------------------------- */ /* ======================================================== Always try transfer first !!! (Merge is only possible if 2 nodes are half filled) ======================================================== */ if ( leftSibling(N) has ≥ ⌊(n+1)/2⌋ + 1 pointers ) { 1. transfer last key from leftSibling(N) through parent into N as the first key; 2. transfer right subtree link into N as the first link } else if ( rightSibling(N) has ≥ ⌊(n+1)/2⌋ + 1 pointers ) { 1. transfer first key from rightSibling(N) through parent into N as the last key; 2. transfer left subtree link into N as the last link } /* ========================================================== Here: can't solve underflow with a transfer Because: BOTH sibling nodes have minimum # keys/pointers (= half filled !!!) Solution: merge the 2 half filled nodes into 1 node ========================================================== */ else if ( leftSibling(N) exists ) { /* =============================================== merge N with left sibling node =============================================== */ 1. Merge (1) leftSibling(N) + (2) key in parent node + (3) N into the leftSibling(N) node; 2. Delete ( transfered key, right subtree ptr, parent(N) ); // Recurse !! } else // Node N must have a right sibling node !!! { /* =============================================== merge N with right sibling node =============================================== */ 1. Merge (1) N + (2) key in parent node + (3) rightSibling(N) into the node N; 2. Delete ( transfered key, right subtree ptr, parent(N) ); // Recurse !! } } ```

• Example 1 of Delete Algorithm: no underflow condition

• Delete the index entry for the search key 31 from the following B+-tree:

• Delete key 31 (and its record pointer) from a leaf node:

• Find the leaf node L that would contain search key 31:

• Delete the index entry for (31, recordPtr(31)) from the node:

Result:

• Notice that:

• The deleted search key 31 can still in the inner nodes of the B+-tree:

Comment:

 The inner nodes of a B+-tree is only an auxilary structure to help you locate an index The actual index file is (only) stored in the leaf nodes of the B+-tree !!

• Example 2 of Delete Algorithm: underflow - solved by transfer in leaf node

• Delete the search key 7 from the following B+-tree:

• Delete key 7 (and its record pointer) from a leaf node:

• (I skipped the step to find the node that would contain the search key 7

This step should be known to you by now....)

• After deleting (7, recordPtr(7)) from the node:

We solve the underflow condition using a leaf transfer from the left sibling node.

• After the leaf transfer operation:

Note:

• We must update the parent's node key to reflect an updated range

If you do not update the parent's node key, the B+-tree will have an error:

You cannot find the index entry for search key 5 with the B+-tree !!!

• Example 3 of Delete Algorithm: underflow - solved by merge in leaf node

• Delete the search key 11 from the following B+-tree:

• Delete key 11 (and its record pointer) from a leaf node:

• (I skipped the step to find the node that would contain the search key 11 )

• After deleting (11, recordPtr(11)) from the node:

Underflow !!!

First check if transfer is possible:

• Transfer a key from its left sibling:

 Not possible:   the left sibling is a minimal B-tree node

• Transfer a key from its right sibling:

 Not possible:   the right sibling is a minimal B-tree node

We cannot use transfer from either siblings to solve the underflow condition

We have to use merge.....

• Merge the node into its left sibling node:

Initial state:

Merge the underflow node with its left sibling node:

Delete (5, rightTree(5)) from the parent node:

• Delete (5, rightTree(5)) from an internal node:

The node is not underflow, because 2 (= at least half) of the 4 pointers are used:

• Example 4 of Delete Algorithm: underflow - solved by merge in leaf node followed by a transfer

• Delete the seearch key 11 from the following B+-tree:

• Delete key 11 (and its record pointer) from a leaf node:

• (I skipped the step to find the node that would contain the search key 11 )

• After deleting (11, recordPtr(11)) from the node:

Transfer options:

• Transfer a key from its left sibling:

 Not possible:   the left sibling is a minimal B-tree node

• Transfer a key from its right sibling:

 Not possible:   there is no right sibling node !!!

We cannot use transfer from either siblings to solve the underflow condition

We have to use merge.....

• Merge the node into its left sibling node:

Initial state:

Result of the merge operation:

The delete algorithm will call InternalNode_Delete( (5, rightTree(5) ) to delete (5, rightTree(5)) from parent node:

The InternalNode_Delete( (5, rightTree(5) ) call will solve the deletion using a transfer operation...

• Processing InternalNode_Delete( (5, rightTree(5) ):

• Initial state:

• After deleting the search key (5, rightTree(5):

• Recall the transfer operation for internal nodes:

In this case:

Result:

Done !

• Example 5 of Delete Algorithm: underflow - solved by merge in leaf node followed by a merge in internal node

• Delete the seearch key 11 from the following B+-tree:

• Delete key 11 (and its record pointer) from a leaf node:

• (I skipped the step to find the node that would contain the search key 11 )

• After deleting (11, recordPtr(11)) from the node:

Underflow !!!

Transfer options:

• Transfer a key from its left sibling:

 Not possible:   the left sibling is a minimal B-tree node

• Transfer a key from its right sibling:

 Not possible:   there is no right sibling node !!!

We cannot use transfer from siblings to solve the underflow condition

We have to use merge.....

• Merge the node into its left sibling node:

Initial state:

Result of the merge operation:

The (leaf node) delete algorithm will call InternalNode_Delete( (5, rightTree(5) ) to delete (5, rightTree(5)) from parent node:

We will see that the InternalNode_Delete( ) algorithm will need to use merge in this case

• Processing InternalNode_Delete(5, rightTree(5)) :

Initial state:

• After deleting the search key (5, rightTree(5)):

Underflow !!!

Transfer options:

• Transfer a key from its left sibling:

 No possible:   there is no left sibling node !!!

• Transfer a key from its right sibling:

 Not possible:   right sibling node is a minimal node !!!

We cannot use transfer from siblings to solve the underflow condition

We have to use merge.....

• Recall the merge operation for internal nodes:

We must "sandwich" the key 15 between the 2 merged node....

In this case:

Result of the merge operation:

The merge is followed by a delete operation in the parent node: InternalNode_Delete( (13, (rightTree(13) ) :

• Processing InternalNode_Delete(13, rightTree(13)) :

• Initial state:

After deleting (13, rightTree(13)):

An empty root node can be removed:

Done !