Indexing, Hashing & Query Optimization in DBMS

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.

STEPS:

Creating the Students Table

Inserting Sample Records

Creating a B-Tree Index on roll_no

SELECT * FROM Students WHERE roll_no = 110;

Creating a B+ Tree Index on cgpa

SELECT * FROM Students WHERE cgpa > 8.0;

Creating an Index on dept for Fast Equality Search

SELECT * FROM Students WHERE dept = 'CSBS';