Chapter72 - CSIS0278A Introduction to Database Management...

Info icon This preview shows pages 1–12. Sign up to view the full content.

View Full Document Right Arrow Icon
Click to edit Master subtitle style CSIS0278A Introduction to Database Management Systems Lecture 7 Indexing Part 2: B+-tree and Extendible Hashing Dr. Reynold Cheng Based on the Ch. 12 notes of “Database System Concepts” by A. Silberschatz, H. Korth & S. Sudarshan and notes by
Image of page 1

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
2DB080:2 Overview l B+-tree Index l Hashing l Index Definition in SQL
Image of page 2
3DB080:3 B+-Tree Index Files l Disadvantages of indexed-sequential files: l Performance degrades as file grows, since many overflow blocks are created. l Periodic reorganization of the entire file is required. l B+-tree index files are an alternative to the index of the indexed-sequential files. l Advantages of B+-tree index files: l No overflow blocks, so performance won’t downgrade much l automatically reorganize itself with small local changes, during insertion & deletion.
Image of page 3

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
4DB080:4 B+-Tree Index Files l Disadvantages of B+-tree index files: l extra actions during insertion & deletion l extra space to store more levels of indices, may have empty space within each block. l Practically, the advantages of B+-tree outweigh the disadvantages. l B+-trees are used extensively in DBMS .
Image of page 4
B+-Tree vs. Binary Tree K 5 K 2 K 1 K 3 K 4 < K1 > K1 < K2 > K2 Internal node Root Leaf node Binary tree minimizes the no. of comparisons for finding a key. Why don’t we use binary tree in DB? We want to minimize no. of block retrieval (i.e., no. of nodes to be accessed) rather than no. of comparisons. We need a tree which is: 1. low in height 2. balanced node size = 1 B+- tree Binar y tree path from root to every leaf has same length
Image of page 5

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
6DB080:6 Properties of B+-Tree l All paths from root to leaf are of the same length (i.e., balanced ) l Each internal node that is NOT a ROOT must be always at least half full , i.e., it has b n/2° to n children ( n , called fanout , is defined next) l A leaf node has between ± (n – 1)/2 ² and (n – 1) values. l Special cases: l If the root is not a leaf, it has at least 2 children. l If the root is a leaf, it can have 0 to (n – 1) values.
Image of page 6
7DB080:7 B+-Tree Node Structure l A typical node: l Ki are search key-values. l Pi are pointers to children (for non-leaf nodes), or pointers to data records (for leaf nodes). l If B+-tree is a secondary index, Pi of a leaf node may point to a bucket containing pointers to data records. l Fanout = n (for non-leaf node), and fanout = n-1 (for leaf node). l Search-keys in a node must be ordered. l i.e., K1< K2< … < Kn-1
Image of page 7

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
8DB080:8 Leaf Nodes in B+-Trees l If Li and Lj are leaf nodes and i < j, Li’s search-key values are less than Lj’s search- key values. l Pn points to the next leaf node in search-key order. P 1 P 2 P 3 leaf node 2 leaf node 3 K 1 K 2 B+-tree as a Primary Index
Image of page 8
9DB080:9 Leaf Nodes in B+-Tree as a Secondary Index P 1 K1 P 2 K2 P 3 K1 …. K2 …. K1 …. K2 ….
Image of page 9

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
10DB080:10 Non-Leaf Nodes in B+-Trees (1) Keys < K1 K 1 Keys < K2 K n-1 Keys
Image of page 10
11DB080:11 Non-Leaf Nodes in B+-Trees (2) l Non-leaf nodes form a multi-level sparse index on the leaf nodes. For a non-leaf node with n pointers: l all search-keys in the subtree to which P1 points are less than K1.
Image of page 11

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 12
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern