Databases handle massive data efficiently using indexes and hashing. Instead of scanning entire tables, indexes act like the index of a book, making lookups faster.

In this blog, we’ll build a Students table, create B-Tree, B+Tree, and Hash indexes, and run queries to see their effect.

📖 Key Definitions 🔹 Indexing

Indexing is a technique to speed up data retrieval from a database. Instead of scanning the whole table, the database uses an index (like a book index) to locate the rows quickly.

B-Tree Index

A B-Tree (Balanced Tree) index stores keys in a sorted order, allowing logarithmic time searches. It is efficient for:

B+ Tree Index

A B+ Tree is a variation of the B-Tree where all values are stored in the leaf nodes, and internal nodes only store keys for navigation.

Hash Index

A Hash Index uses a hashing function to map keys (like dept) into buckets.

Query Optimization

The process of minimising query execution time by leveraging indexes and writing efficient SQL statements.

Step 1: Create Students Table

CREATE TABLE Students1 ( roll_no INT PRIMARY KEY, name VARCHAR2(50), dept VARCHAR2(20), cgpa NUMBER(3,2) );

Inserting Sample Records

INSERT INTO Students1 VALUES (101, ‘Ana’, ‘CSBS’, 8.5); INSERT INTO Students1 VALUES (102, ‘Paul’, ‘CSBS’, 7.8); INSERT INTO Students1 VALUES (103, ‘Kevin’, ‘ECE’, 9.0); INSERT INTO Students1 VALUES (104, ‘Angelin’, ‘ME’, 8.2); INSERT INTO Students1 VALUES (105, ‘Vanessa’, ‘CSBS’, 8.8); INSERT INTO Students1 VALUES (106, ‘Ria’, ‘ECE’, 7.5); INSERT INTO Students1 VALUES (107, ‘Samuel’, ‘ME’, 8.7); INSERT INTO Students1 VALUES (108, ‘Noah’, ‘CSBS’, 6.9); INSERT INTO Students1 VALUES (109, ‘Marin’, ‘ECE’, 8.0); INSERT INTO Students1 VALUES (110, ‘Joseph’, ‘CSBS’, 9.2); INSERT INTO Students1 VALUES (111, ‘Trinita’, ‘ME’, 7.9); INSERT INTO Students1 VALUES (112, ‘Ryan’, ‘CSBS’, 8.3); INSERT INTO Students1 VALUES (113, ‘Daniel’, ‘ECE’, 9.1); INSERT INTO Students1 VALUES (114, ‘Kane’, ‘ME’, 7.7); INSERT INTO Students1 VALUES (115, ‘Isha’, ‘CSBS’, 8.6); INSERT INTO Students1 VALUES (116, ‘Sarah’, ‘ECE’, 8.4); INSERT INTO Students1 VALUES (117, ‘Merlin’, ‘ME’, 8.0); INSERT INTO Students1 VALUES (118, ‘James’, ‘CSBS’, 7.6); INSERT INTO Students1 VALUES (119, ‘Page’, ‘ECE’, 8.9); INSERT INTO Students1 VALUES (120, ‘Reynolds’, ‘ME’, 8.1);

B-Tree Index on roll_no Most DBMSs use B-Trees to index numeric/ordered columns.

CREATE INDEX idx_roll_no ON Students1(roll_no);

Query with Index This fetches details of roll_no = 110 in O(log n) instead of scanning all rows.

SELECT * FROM Students1 WHERE roll_no = 110;

B+ Tree Index on CGPA B+ Trees are used for range queries, making them perfect for CGPA lookups.

CREATE INDEX idx_cgpa ON Students1(cgpa);

Query Display all students with a CGPA> 8.0

SELECT * FROM Students1 WHERE cgpa > 8.0;

Hash Index on dept Hashing is great for exact matches (not ranges).

CREATE INDEX idx_dept ON Students1(dept);

Query Retrieve all students from the CSBS department

SELECT * FROM Students1 WHERE dept = ‘CSBS’;

⚡ Wrap Up

In this tutorial, we explored:

B-Tree Index → Fast lookup by roll_no

B+Tree Index → Efficient range queries (CGPA > 8.0)

Hash Index → Quick equality checks (dept = CSBS)

Indexes make queries 10x–100x faster, but they also consume storage & slow down inserts/updates. Use them wisely for query optimization!

Thanks to @santhoshnc Sir for guiding me through indexing and query optimization concepts.

SQL #Oracle #Indexing #BTree #BPlusTree #QueryOptimization #DBMS #Database