Indexes are one of the most powerful tools in SQL databases for improving query performance. In this blog, we’ll explore B-Tree Index, B+ Tree Index, and Hash Index using a simple Students table in Oracle LiveSQL.
🧱 Step 1: Create the Students Table
We start by creating a table Students with fields for roll number, name, department, and CGPA.
CREATE TABLE Students (
ROLL_NO NUMBER PRIMARY KEY,
NAME VARCHAR2(50),
DEPT VARCHAR2(20),
CGPA NUMBER(3,2)
);
💡 Step 2: Insert Sample Records
Let’s insert 20 sample students across various departments with different CGPAs.
INSERT INTO Students VALUES (101, ‘Alice’, ‘CSBS’, 8.5);
INSERT INTO Students VALUES (102, ‘Bob’, ‘ECE’, 7.9);
INSERT INTO Students VALUES (103, ‘Charlie’, ‘MECH’, 8.2);
INSERT INTO Students VALUES (104, ‘David’, ‘CIVIL’, 7.0);
INSERT INTO Students VALUES (105, ‘Eva’, ‘CSBS’, 9.0);
INSERT INTO Students VALUES (106, ‘Frank’, ‘EEE’, 6.8);
INSERT INTO Students VALUES (107, ‘Grace’, ‘ECE’, 8.3);
INSERT INTO Students VALUES (108, ‘Hank’, ‘MECH’, 7.2);
INSERT INTO Students VALUES (109, ‘Ivy’, ‘CIVIL’, 8.1);
INSERT INTO Students VALUES (110, ‘Jack’, ‘CSBS’, 9.0);
INSERT INTO Students VALUES (111, ‘Kim’, ‘EEE’, 7.5);
INSERT INTO Students VALUES (112, ‘Leo’, ‘CSBS’, 9.2);
INSERT INTO Students VALUES (113, ‘Mia’, ‘MECH’, 6.9);
INSERT INTO Students VALUES (114, ‘Nina’, ‘ECE’, 8.7);
INSERT INTO Students VALUES (115, ‘Oscar’, ‘CSBS’, 9.4);
INSERT INTO Students VALUES (116, ‘Paul’, ‘EEE’, 7.8);
INSERT INTO Students VALUES (117, ‘Quinn’, ‘MECH’, 8.0);
INSERT INTO Students VALUES (118, ‘Rose’, ‘CIVIL’, 7.3);
INSERT INTO Students VALUES (119, ‘Sam’, ‘ECE’, 8.8);
INSERT INTO Students VALUES (120, ‘Tina’, ‘CSBS’, 9.1);
⚡ Step 3: Create a B-Tree Index
B-Tree indexes are efficient for point queries and range queries.
CREATE INDEX idx_rollno_btree ON Students(ROLL_NO);
— Fetch a student with a specific roll number
SELECT * FROM Students WHERE ROLL_NO = 110;
✅ Output: Jack, CSBS, 9.0
The B-Tree index ensures this query runs efficiently without scanning the entire table.
⚡ Step 4: Create a B+ Tree Index on CGPA
B+ Tree indexes are ideal for range queries.
— Example: fetch students with CGPA > 8.0
SELECT * FROM Students WHERE CGPA > 8.0;
This allows the database to quickly locate all students satisfying the CGPA condition.
⚡ Step 5: Create a Hash Index on Department
Hash indexes are perfect for exact match lookups.
CREATE INDEX idx_dept_hash ON Students(DEPT);
— Fetch all students from the CSBS department
SELECT * FROM Students WHERE DEPT = ‘CSBS’;
✅ Result: All CSBS students are returned efficiently, leveraging the hash index.
🧠 Key Takeaways
B-Tree Index: Fast for exact lookups and sorted range queries.
B+ Tree Index: Optimized for range scans; all values stored at leaf nodes.
Hash Index: Excellent for equality comparisons (e.g., department=”CSBS”).
Proper indexing dramatically improves query performance, especially with large datasets.
💡 Final Thoughts
Understanding and using indexes effectively is crucial for query optimization. By combining B-Tree, B+ Tree, and Hash indexes, you can make your database queries faster and more efficient — a key skill for any data engineer or developer.
Thank you @santhoshnc sir for guiding and supporting me..
