Indexing and B+ Trees Notes for GATE DA: Concepts, Formulas, Worked Examples & Practice

Indexing and B+ Trees notes for GATE DA: 18 study cards covering concepts, formulas, shortcuts and exam traps, plus solved practice questions.

Chapter Roadmap: Indexing and B+ Trees

Chapter Journey

1. B+ Tree Structure, Insertion and Fanout Calculation

Current Topic • High Importance • Foundation for all indexing and disk I/O questions

2. Index Selection for SQL Query Processing

Upcoming • Moderate Importance • Applies B+ tree mechanics to query execution plans

What you will master: Deriving node capacity limits from block sizes, executing step-by-step B+ tree insertions, and avoiding classic split traps.

Why B+ Trees? The Core Intuition

The Disk I/O Problem

Database systems minimize slow disk accesses by reading data in fixed-size blocks (or pages). A good index must minimize the number of blocks read to find a record.

Why Not Binary Search Trees?

A binary tree has a maximum of two children per node. For a million records, the height is around 20, meaning up to 20 disk reads. This is inefficient.

The B+ Tree Solution

High Fanout: Each node can hold hundreds of keys, drastically reducing the tree height (typically to 3 or 4 levels for millions of records).
Balanced: All leaf nodes are at the exact same depth, guaranteeing predictable, worst-case $O (lo g_{p} N)$ search time.
Data at Leaves Only: Internal nodes act purely as a "map" or routing guide. Only leaf nodes contain Data Record Pointers.
Linked Leaves: Leaf nodes are connected via sequential pointers, making range queries highly efficient without traversing back up the tree.

B+ Tree Structural Rules

Let $p$ be the maximum number of tree pointers (fanout) in a non-leaf node, and $p_{l e a f}$ be the maximum number of data entries in a leaf node.

Node Type	Pointer/Key Constraints	Data Pointers?
Root	Min 2 pointers, Max $p$ pointers (if not a leaf).	Only if it is the sole node.
Internal	Min $⌈ p /2 ⌉$ pointers, Max $p$ pointers.	Never. Contains only keys and block pointers.
Leaf	Min $⌈ p_{l e a f} /2 ⌉$ entries, Max $p_{l e a f}$ entries.	Yes. Contains keys, data record pointers, and one next-leaf pointer.

Critical Property: Every key value that appears in an internal node also appears in the leaf level. Internal keys act as "separators" or "guideposts", not as the actual data location.

Fanout Calculation: Non-Leaf Nodes

A non-leaf node contains $p$ block pointers and $p - 1$ search keys. The total size must not exceed the fixed block (node) size.

p \times (Node Pointer Size) + (p - 1) \times (Search Key Size) \leq Node Size

Step-by-step derivation:

1. Let

B

= Node Size (in bytes)

2. Let

P_{t r}

= Tree/Node Pointer Size (in bytes)

3. Let

V

= Search Key Field Size (in bytes)

4. Inequality:

p \cdot P_{t r} + (p - 1) \cdot V \leq B

5. Simplify:

p (P_{t r} + V) - V \leq B

6. Solve for

p

p \leq \frac{B + V}{P _{t r} + V}

7. Maximum fanout

p = ⌊ \frac{B + V}{P _{t r} + V} ⌋

More notes in this unit

chapter

Indexing and B+ Trees Notes for GATE DA: Concepts, Formulas, Worked Examples & Practice

Indexing and B+ Trees notes for GATE DA: 18 study cards covering concepts, formulas, shortcuts and exam traps, plus solved practice questions.

Free preview ends here

Login to view the complete notes

Creating an account is free. You get the rest of this chapter, step-by-step solutions, and a study plan built around the topics you are actually weak at.

Why MastersUp

Personalised first. High quality throughout.

Most platforms hand everyone the same content. Here the content moves with your performance, topic by topic.

Built around you, not around a syllabus PDF

Every answer you give moves your topic-level intelligence rate. The next question, the next revision card and tomorrow's plan all change with it.

Revision that hits your weak spots

We only revise topics you have actually attempted and are still below the safe bar on — never the same chapter on repeat.

Questions calibrated to the real exam

Each question carries a measured toughness. You are served a rung above your current level, so practice keeps stretching you.

Notes written for recall, not for volume

Full lesson cards for first study, curated short-note cards for the last mile — with derivations, traps and exam patterns marked.

One place for everything

Notes, chapter practice, previous-year questions, test series and full-length papers — all feeding one picture of your preparation.

Honest progress

No vanity streaks. Progress here means chapters mastered and accuracy that held up on harder questions.

Unlock the whole course

Full notes and short notes, the complete question bank with worked solutions, mock tests, full-length papers, and an adaptive plan that rebuilds itself as you improve.

View all plans Start free