Chapter72

Chapter72 - CSIS0278A Introduction to Database Management...

Info iconThis 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”
Background image of page 1

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

View Full DocumentRight Arrow Icon
2DB080:2 Overview l B+-tree Index l Hashing l Index Definition in SQL
Background 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.
Background image of page 3

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

View Full DocumentRight 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 .
Background 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 B+- tree Binar y tree path from root to every leaf has same length
Background image of page 5

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

View Full DocumentRight 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.
Background 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
Background image of page 7

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

View Full DocumentRight 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
Background 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 ….
Background image of page 9

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

View Full DocumentRight Arrow Icon
10DB080:10 Non-Leaf Nodes in B+-Trees (1) Keys < K1 K 1 Keys < K2 K n-1 Keys
Background 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.
Background image of page 11

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

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

Page1 / 62

Chapter72 - CSIS0278A Introduction to Database Management...

This preview shows document pages 1 - 12. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online