Indexing, Hashing & Query Optimization in DBMS

#programming #database #learning #coding

Databases handle massive data efficiently using indexes and hashing. Instead of scanning entire tables, indexes act like the index of a book, making lookups faster.

In this blog, we’ll build a Students table, create B-Tree, B+Tree, and Hash indexes, and run queries to see their effect.

📖 Key Definitions
🔹 Indexing

Indexing is a technique to speed up data retrieval from a database. Instead of scanning the whole table, the database uses an index (like a book index) to locate the rows quickly.

B-Tree Index

A B-Tree (Balanced Tree) index stores keys in a sorted order, allowing logarithmic time searches. It is efficient for:

B+ Tree Index

A B+ Tree is a variation of the B-Tree where all values are stored in the leaf nodes, and internal nodes only store keys for navigation.

Hash Index

A Hash Index uses a hashing function to map keys (like dept) into buckets.

Query Optimization

The process of minimising query execution time by leveraging indexes and writing efficient SQL statements.

Step 1: Create Students Table

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

Inserting Sample Records

INSERT INTO Students1 VALUES (101, 'Ana', 'CSBS', 8.5);
INSERT INTO Students1 VALUES (102, 'Paul', 'CSBS', 7.8);
INSERT INTO Students1 VALUES (103, 'Kevin', 'ECE', 9.0);
INSERT INTO Students1 VALUES (104, 'Angelin', 'ME', 8.2);
INSERT INTO Students1 VALUES (105, 'Vanessa', 'CSBS', 8.8);
INSERT INTO Students1 VALUES (106, 'Ria', 'ECE', 7.5);
INSERT INTO Students1 VALUES (107, 'Samuel', 'ME', 8.7);
INSERT INTO Students1 VALUES (108, 'Noah', 'CSBS', 6.9);
INSERT INTO Students1 VALUES (109, 'Marin', 'ECE', 8.0);
INSERT INTO Students1 VALUES (110, 'Joseph', 'CSBS', 9.2);
INSERT INTO Students1 VALUES (111, 'Trinita', 'ME', 7.9);
INSERT INTO Students1 VALUES (112, 'Ryan', 'CSBS', 8.3);
INSERT INTO Students1 VALUES (113, 'Daniel', 'ECE', 9.1);
INSERT INTO Students1 VALUES (114, 'Kane', 'ME', 7.7);
INSERT INTO Students1 VALUES (115, 'Isha', 'CSBS', 8.6);
INSERT INTO Students1 VALUES (116, 'Sarah', 'ECE', 8.4);
INSERT INTO Students1 VALUES (117, 'Merlin', 'ME', 8.0);
INSERT INTO Students1 VALUES (118, 'James', 'CSBS', 7.6);
INSERT INTO Students1 VALUES (119, 'Page', 'ECE', 8.9);
INSERT INTO Students1 VALUES (120, 'Reynolds', 'ME', 8.1);