Indexing, Hashing & Query Optimization in SQL

Efficient data retrieval is essential for database performance. In this tutorial, we’ll explore B-Tree, B+ Tree, and Hash indexing, and see how they optimize queries.

1. Create Sample Students Table
CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR2(50),
dept VARCHAR2(20),
cgpa NUMBER(3,2)
);

-- Insert 20 sample records
INSERT INTO Students VALUES (101, 'Alice', 'CSBS', 9.1);
INSERT INTO Students VALUES (102, 'Bob', 'MECH', 7.8);
INSERT INTO Students VALUES (103, 'Charlie', 'CSBS', 8.5);
INSERT INTO Students VALUES (104, 'David', 'ECE', 8.2);
INSERT INTO Students VALUES (105, 'Eve', 'CSBS', 9.0);
INSERT INTO Students VALUES (106, 'Frank', 'CIVIL', 7.5);
INSERT INTO Students VALUES (107, 'Grace', 'ECE', 8.9);
INSERT INTO Students VALUES (108, 'Hannah', 'CSBS', 9.2);
INSERT INTO Students VALUES (109, 'Ivy', 'MECH', 7.9);
INSERT INTO Students VALUES (110, 'Jack', 'CSBS', 8.6);
INSERT INTO Students VALUES (111, 'Kevin', 'CIVIL', 7.2);
INSERT INTO Students VALUES (112, 'Laura', 'ECE', 8.0);
INSERT INTO Students VALUES (113, 'Mallory', 'CSBS', 8.8);
INSERT INTO Students VALUES (114, 'Nina', 'MECH', 7.7);
INSERT INTO Students VALUES (115, 'Oscar', 'CSBS', 9.3);
INSERT INTO Students VALUES (116, 'Peggy', 'ECE', 8.1);
INSERT INTO Students VALUES (117, 'Quentin', 'CSBS', 8.9);
INSERT INTO Students VALUES (118, 'Rita', 'CIVIL', 7.6);
INSERT INTO Students VALUES (119, 'Steve', 'CSBS', 9.0);
INSERT INTO Students VALUES (120, 'Trudy', 'MECH', 7.8);

COMMIT;

2. B-Tree Index on roll_no

B-Tree indexes are ideal for exact match queries.

-- Create B-Tree index
CREATE INDEX idx_roll_no ON Students(roll_no);

-- Query using index
SELECT * FROM Students
WHERE roll_no = 110;

✅ Using this index, the database can quickly locate roll_no = 110 without scanning the full table.

3. B+ Tree Index on cgpa

B+ Tree indexes are great for range queries.

-- Create B+ Tree index
CREATE INDEX idx_cgpa ON Students(cgpa);

-- Query all students with cgpa > 8.0
SELECT * FROM Students
WHERE cgpa > 8.0
ORDER BY cgpa DESC;

Efficiently fetches multiple records in a range.
The ORDER BY clause benefits from B+ Tree’s sequential structure.

4. Hash Index on dept

Hash indexes work well for equality lookups on discrete values.

-- Create Hash index
CREATE INDEX idx_dept ON Students(dept) LOCAL; -- Oracle supports HASH-like functionality with GLOBAL or partitioned indexes

-- Query students from 'CSBS' department
SELECT * FROM Students
WHERE dept = 'CSBS';

✅ Quickly retrieves all students from a specific department without scanning the table.

5. Query Optimization Tips

• Always create indexes on columns used in WHERE, JOIN, or ORDER BY clauses.
• Use B-Tree/B+ Tree for range queries.
• Use Hash indexes for equality searches on high-cardinality columns.
• Check execution plans to ensure queries use indexes effectively.
• Avoid over-indexing — it slows INSERT/UPDATE/DELETE operations.

Conclusion

Indexing is a key tool for query optimization. B-Tree, B+ Tree, and Hash indexes reduce table scans and improve performance. Combining indexes with query best practices ensures your database is fast and scalable.