Indexing, Hashing & Query Optimization

Introduction

Efficient data retrieval is critical in database management. Indexing and query optimization help reduce query execution time, especially for large datasets. In this blog, I demonstrate the use of B-Tree, B+ Tree, and Hash-based indexing in Oracle SQL through a simple Students table.
I also showcase queries that benefit from these indexes. This is part of my database assignments, and I want to acknowledge my mentor Santhosh Sir for guidance and encouragement.

Step 1: Creating the Students Table

We start by creating a Students table with the following fields:

• roll_no (Primary Key)
• name (Student name)
• dept (Department)
• cgpa (Cumulative GPA)

CREATE TABLE Students (
roll_no NUMBER PRIMARY KEY,
name VARCHAR2(50),
dept VARCHAR2(20),
cgpa NUMBER(3,2)
);

Step 2: Inserting Sample Data

We insert 20 student records into the table:

INSERT INTO Students VALUES (101, 'Alice', 'CSBS', 9.1);
INSERT INTO Students VALUES (102, 'Bob', 'ECE', 7.8);
INSERT INTO Students VALUES (103, 'Charlie', 'MECH', 8.5);
INSERT INTO Students VALUES (104, 'David', 'CSBS', 6.9);
INSERT INTO Students VALUES (105, 'Eva', 'CIVIL', 8.2);
INSERT INTO Students VALUES (106, 'Frank', 'ECE', 7.5);
INSERT INTO Students VALUES (107, 'Grace', 'CSBS', 9.0);
INSERT INTO Students VALUES (108, 'Hannah', 'MECH', 8.1);
INSERT INTO Students VALUES (109, 'Ian', 'CIVIL', 7.6);
INSERT INTO Students VALUES (110, 'Jack', 'CSBS', 8.8);
INSERT INTO Students VALUES (111, 'Kiran', 'ECE', 7.9);
INSERT INTO Students VALUES (112, 'Lina', 'MECH', 8.4);
INSERT INTO Students VALUES (113, 'Mona', 'CSBS', 9.2);
INSERT INTO Students VALUES (114, 'Nina', 'CIVIL', 8.0);
INSERT INTO Students VALUES (115, 'Oscar', 'ECE', 6.8);
INSERT INTO Students VALUES (116, 'Paul', 'MECH', 7.7);
INSERT INTO Students VALUES (117, 'Quinn', 'CSBS', 8.9);
INSERT INTO Students VALUES (118, 'Rita', 'CIVIL', 7.5);
INSERT INTO Students VALUES (119, 'Steve', 'ECE', 8.3);
INSERT INTO Students VALUES (120, 'Tina', 'MECH', 8.6);

Step 3: B-Tree Index on roll_no

Since roll_no is the primary key, Oracle automatically creates a B-Tree index on this column. This ensures efficient retrieval for queries like:

SELECT * FROM Students
WHERE roll_no = 110;

Step 4: B+ Tree Index on cgpa

For range queries like cgpa > 8.0, a B+ Tree index is ideal. Oracle automatically uses B+ Tree indexing for normal indexes, so we create:

CREATE INDEX idx_cgpa ON Students(cgpa);

SELECT * FROM Students
WHERE cgpa > 8.0
ORDER BY cgpa DESC;

Step 5: Hash Index on dept (Simulated)

Oracle does not support direct hash indexes like some other databases, but we can simulate it using a normal index:

CREATE INDEX idx_dept ON Students(dept);

SELECT * FROM Students
WHERE dept = 'CSBS';

Conclusion

Through this assignment, I observed:
Primary Key automatically creates a B-Tree index → no need to manually create.

B+ Tree index is perfect for range queries (e.g., cgpa > 8.0).
Hash indexes (or equality-based indexes) optimize retrieval for categorical fields like dept.
Indexing and query optimization are crucial for efficient data retrieval, especially in large databases.

I sincerely thank @santhoshnc Sir for his guidance, assignments, and encouragement, which helped me understand indexing and query optimization in Oracle SQL.