Boosting SQL Performance: Indexing & Query Optimization Using a Students Table – ██FR█████ █INTELL███████████

This content originally appeared on DEV Community and was authored by Ramya

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.