This content originally appeared on DEV Community and was authored by Vishnupriya K
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.
This content originally appeared on DEV Community and was authored by Vishnupriya K
Vishnupriya K | Sciencx (2025-10-05T20:59:26+00:00) Indexing, Hashing & Query Optimization in SQL. Retrieved from https://www.scien.cx/2025/10/05/indexing-hashing-query-optimization-in-sql-3/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.