Boosting SQL Performance: Indexing & Query Optimization Using a Students Table

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.