Indexes are one of the most effective ways to enhance SQL query performance. In this tutorial, we’ll dive into B-Tree Index, B+ Tree Index, and Hash Index using a simple Students table in Oracle LiveSQL.
🧱 Step 1: Set Up the Students Table
Let’s begin by creating a Students table with columns 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: Add Sample Data
We’ll insert 20 sample student records across different departments with varying 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: Implement a B-Tree Index
B-Tree indexes excel at 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
This index allows the query to run efficiently without scanning the entire table.
⚡ Step 4: Add a B+ Tree Index on CGPA
B+ Tree indexes are perfect for range-based queries:
-- Example: fetch students with CGPA > 8.0
SELECT * FROM Students WHERE CGPA > 8.0;
The database can quickly locate all students that meet the CGPA condition.
⚡ Step 5: Create a Hash Index on Department
Hash indexes are ideal for exact match searches:
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 using the hash index.
💡 Final Thoughts
Indexes are the unsung heroes of SQL performance. Understanding when and how to use B-Tree, B+ Tree, and Hash indexes can make your queries run significantly faster. Efficient indexing is not just a technical optimization—it’s a key skill that sets apart proficient database developers and data engineers. Start experimenting with different index types, and watch your database operations become more streamlined and powerful.
🧠 Key Insights
B-Tree Index: Fast for exact lookups and sorted range queries.
B+ Tree Index: Optimized for scanning ranges; all values are stored in leaf nodes.
Hash Index: Best suited for equality searches (e.g., DEPT = 'CSBS').
Proper indexing can dramatically boost query performance, especially when dealing with large datasets.