Indexing, Hashing & Query Optimization in DBMS

INTRODUCTION
When working with databases, speed and efficiency matter a lot especially when you have tons of data. That’s where indexing, hashing, and query optimization come into play.

Indexing is like the index in a book; it helps the database find information quickly without scanning every single record.

Hashing is a smart way to organize data so you can jump straight to what you need using a unique key.

And query optimization is the brainpower behind the scenes, figuring out the fastest way to get the data you asked for.

Together, these techniques make databases faster and more efficient, saving time and resources whether you’re searching, sorting, or managing large amounts of data.

Creating the Studentss Table

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

Inserting Sample Records

INSERT INTO Students VALUES (101, 'Arjun', 'CSBS', 8.5);
INSERT INTO Students VALUES (102, 'Priya', 'CSBS', 7.8);
INSERT INTO Students VALUES (103, 'Kiran', 'ECE', 9.0);
INSERT INTO Students VALUES (104, 'Anita', 'ME', 8.2);
INSERT INTO Students VALUES (105, 'Vikram', 'CSBS', 8.8);
INSERT INTO Students VALUES (106, 'Ravi', 'ECE', 7.5);
INSERT INTO Students VALUES (107, 'Sneha', 'ME', 8.7);
INSERT INTO Students VALUES (108, 'Nikhil', 'CSBS', 6.9);
INSERT INTO Students VALUES (109, 'Maya', 'ECE', 8.0);
INSERT INTO Students VALUES (110, 'Aditya', 'CSBS', 9.2);
INSERT INTO Students VALUES (111, 'Tanya', 'ME', 7.9);
INSERT INTO Students VALUES (112, 'Rohan', 'CSBS', 8.3);
INSERT INTO Students VALUES (113, 'Divya', 'ECE', 9.1);
INSERT INTO Students VALUES (114, 'Karthik', 'ME', 7.7);
INSERT INTO Students VALUES (115, 'Isha', 'CSBS', 8.6);
INSERT INTO Students VALUES (116, 'Suresh', 'ECE', 8.4);
INSERT INTO Students VALUES (117, 'Meena', 'ME', 8.0);
INSERT INTO Students VALUES (118, 'Aravind', 'CSBS', 7.6);
INSERT INTO Students VALUES (119, 'Pooja', 'ECE', 8.9);
INSERT INTO Students VALUES (120, 'Rahul', 'ME', 8.1);

Creating a B-Tree Index on roll_no

CREATE INDEX idx_roll_no ON Students(roll_no);

Query: Fetch student with roll_no = 117

SELECT * FROM Students WHERE roll_no = 117;

Creating a B+ Tree Index on cgpa

Oracle automatically uses B+ Tree for numeric indexes.

CREATE INDEX idx_cgpa ON Students(cgpa);

Query: Display all students with cgpa > 8.0

SELECT * FROM Students WHERE cgpa > 8.0;

Query: Retrieve all students from the CSBS department

SELECT * FROM Students WHERE dept = 'CSBS';

summary

Defined the Students table with columns roll_no, name, dept, and cgpa.
Added 20 sample records into the table. Built a B-Tree index on roll_no to enable faster lookups. Created a B+ Tree index on cgpa to improve range query performance. Indexed the dept column to accelerate equality-based searches. Executed queries to test and confirm indexing benefits in performance.
By using indexes, query execution becomes faster and more efficient. Indexing is a vital part of query optimization in DBMS.