Indexing, Hashing & Query Optimization in DBMS

We will focus on enhancing query performance in Oracle SQL by using indexes. We’ll create a Students table, insert sample records, and run optimized queries utilizing B-Tree and B+ Tree indexes.

Definition:

Indexing: A database index is a data structure that enhances the speed of data retrieval operations on a table, though it comes with additional costs in terms of writes and storage space.

Hash Index: Utilizes a hash function to enable rapid equality searches. (Note: In Oracle, true hash indexes are not directly supported; hash-like performance can be achieved through specific functions or clustering techniques.)

B+ Tree Index: An extension of the B-Tree where all values are stored at the leaf nodes, making it more efficient for range queries.

B-Tree Index: A balanced tree-based index commonly used for equality and range queries.

Query Optimization: The process of minimizing query execution time by leveraging indexes and writing efficient SQL statements.

Creating the Students Table

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

Inserting Sample Records
INSERT INTO Student VALUES (101, 'Aarav', 'CSBS', 8.5);
INSERT INTO Student VALUES (102, 'Rhea', 'CSBS', 7.8);
INSERT INTO Student VALUES (103, 'Varun', 'ECE', 9.0);
INSERT INTO Student VALUES (104, 'Simran', 'ME', 8.2);
INSERT INTO Student VALUES (105, 'Kunal', 'CSBS', 8.8);
INSERT INTO Student VALUES (106, 'Devansh', 'ECE', 7.5);
INSERT INTO Student VALUES (107, 'Ishita', 'ME', 8.7);
INSERT INTO Student VALUES (108, 'Aryan', 'CSBS', 6.9);
INSERT INTO Student VALUES (109, 'Neha', 'ECE', 8.0);
INSERT INTO Student VALUES (110, 'Rudra', 'CSBS', 9.2);
INSERT INTO Student VALUES (111, 'Aanya', 'ME', 7.9);
INSERT INTO Student VALUES (112, 'Samar', 'CSBS', 8.3);
INSERT INTO Student VALUES (113, 'Kavya', 'ECE', 9.1);
INSERT INTO Student VALUES (114, 'Harsh', 'ME', 7.7);
INSERT INTO Student VALUES (115, 'Lavanya', 'CSBS', 8.6);
INSERT INTO Student VALUES (116, 'Naveen', 'ECE', 8.4);
INSERT INTO Student VALUES (117, 'Tanvi', 'ME', 8.0);
INSERT INTO Student VALUES (118, 'Yash', 'CSBS', 7.6);
INSERT INTO Student VALUES (119, 'Pallavi', 'ECE', 8.9);
INSERT INTO Student VALUES (120, 'Abhinav', '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 = 110

SELECT * FROM Students WHERE roll_no = 110;

Creating a B+ Tree Index on cgpa

Oracle automatically uses B+ Tree for numeric indexes.

CREATE INDEX idx_cgpa ON Students(cgpa);

SELECT * FROM Student WHERE cgpa > 8.0;

Creating an Index on dept for Fast Equality Search

Oracle does not support direct hash indexes for normal tables.
Instead, we use a regular index:

CREATE INDEX idx_dept ON Students(dept);

Query: Retrieve all students from the CSBS department

SELECT * FROM Students WHERE dept = 'CSBS';

Steps 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.
Conclusion:-

By using indexes, query execution becomes faster and more efficient. Indexing is a vital part of query optimization in DBMS.

Thanks to @santhoshnc Sir for guiding me through indexing and query optimization concepts.

SQL #Oracle #Indexing #BTree #BPlusTree #QueryOptimization #DBMS #Database