Mastering DBMS: A Practical Guide to Indexing, Hashing, and Query Optimization

Introduction
In the world of database management, performance is key. One of the most effective ways to optimize query performance is by using indexes. This blog post will walk you through a practical assignment on creating and using different types of indexes in Oracle Live SQL. We'll cover B-Tree, B+ Tree, and Hash indexes, demonstrating how they speed up data retrieval.

1.Setting Up Our Database: The Students Table
First, let's create a table to work with. We'll design a simple Students table with fields for roll number, name, department, and CGPA. We’ll also insert at least 20 sample records to have enough data for our queries.

Here's the SQL code:

SQL

-- Create the Students table
CREATE TABLE Students (
roll_no NUMBER(5) PRIMARY KEY,
name VARCHAR2(50),
dept VARCHAR2(10),
cgpa NUMBER(4, 2)
);

-- Insert at least 20 sample records
-- Student: Akhil
INSERT INTO Students VALUES (110, 'Akhil', 'CSBS', 9.15);
INSERT INTO Students VALUES (101, 'Aarav', 'CSE', 8.50);
INSERT INTO Students VALUES (102, 'Bhavana', 'ECE', 7.80);
INSERT INTO Students VALUES (103, 'Chetan', 'IT', 9.00);
INSERT INTO Students VALUES (104, 'Divya', 'MECH', 7.25);
INSERT INTO Students VALUES (105, 'Esha', 'CIVIL', 8.90);
INSERT INTO Students VALUES (106, 'Farhan', 'CSE', 6.50);
INSERT INTO Students VALUES (107, 'Gauri', 'ECE', 8.10);
INSERT INTO Students VALUES (108, 'Harish', 'IT', 7.55);
INSERT INTO Students VALUES (109, 'Isha', 'MECH', 9.40);
INSERT INTO Students VALUES (111, 'Jatin', 'CIVIL', 6.80);
INSERT INTO Students VALUES (112, 'Kavya', 'CSE', 8.30);
INSERT INTO Students VALUES (113, 'Lakshya', 'ECE', 7.95);
INSERT INTO Students VALUES (114, 'Meera', 'IT', 9.20);
INSERT INTO Students VALUES (115, 'Naveen', 'MECH', 8.75);
INSERT INTO Students VALUES (116, 'Om', 'CIVIL', 7.00);
INSERT INTO Students VALUES (117, 'Pooja', 'CSE', 9.50);
INSERT INTO Students VALUES (118, 'Qadir', 'ECE', 6.90);
INSERT INTO Students VALUES (119, 'Riya', 'IT', 8.65);
INSERT INTO Students VALUES (120, 'Samar', 'MECH', 7.10);
INSERT INTO Students VALUES (121, 'Tanya', 'CIVIL', 8.45);
INSERT INTO Students VALUES (122, 'Uday', 'CSE', 9.05);

-- Commit the changes
COMMIT;

2.B-Tree Indexing on roll_no
A B-Tree index is a data structure that helps locate a specific row in a table quickly. When you declare a column as a PRIMARY KEY, Oracle automatically creates a B-Tree index on it. This is perfect for single-value lookups, like searching for a student by their roll_no.

Let's fetch the details for the student with roll_no = 110 (Akhil). The B-Tree index on roll_no makes this a very fast operation.

SQL

-- Execute a query to fetch the details of a student with roll_no = 110
SELECT * FROM Students WHERE roll_no = 110;

3.B+ Tree Index for cgpa (Range Queries)
While Oracle's standard index is a B-Tree, it's designed to efficiently handle range queries as well, much like a B+ Tree. The index on cgpa will allow us to quickly find all students whose grades fall within a certain range without scanning the entire table.

We'll create an index on the cgpa column and then run a query to find all students with a CGPA greater than 8.0.

SQL

-- Create a B-Tree index on the cgpa column of the Students table.
CREATE INDEX idx_students_cgpa ON Students(cgpa);

-- Write a query to display all students with cgpa > 8.0.
SELECT * FROM Students WHERE cgpa > 8.0;

4.Hash Index on dept (Equality Queries)
A Hash index is ideal for equality searches, where you are looking for an exact match. In Oracle, a B-Tree index is also highly effective for this, but the concept of a hash index is worth understanding for its direct-addressing nature. For our purpose, we'll create a standard index on the dept column, which Oracle's query optimizer will use efficiently for our exact match query.

Let's find all students from the 'CSBS' department.

SQL

-- Create an index on the dept column
CREATE INDEX idx_students_dept ON Students(dept);

-- Run a query to retrieve all students from the 'CSBS' department.
SELECT * FROM Students WHERE dept = 'CSBS';

Conclusion
As you can see, using indexes is crucial for database performance. Whether you're doing a single-row lookup, a range search, or an exact-match query, a well-placed index can drastically reduce the time it takes to get your results. By understanding and implementing these indexing strategies, you can ensure your database queries are as optimized as possible.