Indexing, Hashing & Query Optimization in liveSQL

Indexes are one of the most powerful tools in SQL databases for improving query performance. In this blog, we’ll explore B-Tree Index, B+ Tree Index, and Hash Index using a simple Students table in Oracle LiveSQL.

Step 1: Create the Students Table
We start by creating a table Students with roll number, name, department, and CGPA fields.

CREATE TABLE Students (
ROLL_NO NUMBER PRIMARY KEY,
NAME VARCHAR2(50),
DEPT VARCHAR2(20),
CGPA NUMBER(3,2)
);

Step 2: Insert Sample Records
Let’s add 20 students across different departments with varying CGPAs.
INSERT INTO Students VALUES (101, 'Alice', 'CSBS', 8.5);
INSERT INTO Students VALUES (102, 'Bob', 'ECE', 7.9);
INSERT INTO Students VALUES (103, 'Charlie', 'MECH', 8.2);
INSERT INTO Students VALUES (104, 'David', 'CIVIL', 7.0);
INSERT INTO Students VALUES (105, 'Eva', 'CSBS', 9.0);
INSERT INTO Students VALUES (106, 'Frank', 'EEE', 6.8);
INSERT INTO Students VALUES (107, 'Grace', 'ECE', 8.3);
INSERT INTO Students VALUES (108, 'Hank', 'MECH', 7.2);
INSERT INTO Students VALUES (109, 'Ivy', 'CIVIL', 8.1);
INSERT INTO Students VALUES (110, 'Jack', 'CSBS', 9.0);
INSERT INTO Students VALUES (111, 'Kim', 'EEE', 7.5);
INSERT INTO Students VALUES (112, 'Leo', 'CSBS', 9.2);
INSERT INTO Students VALUES (113, 'Mia', 'MECH', 6.9);
INSERT INTO Students VALUES (114, 'Nina', 'ECE', 8.7);
INSERT INTO Students VALUES (115, 'Oscar', 'CSBS', 9.4);
INSERT INTO Students VALUES (116, 'Paul', 'EEE', 7.8);
INSERT INTO Students VALUES (117, 'Quinn', 'MECH', 8.0);
INSERT INTO Students VALUES (118, 'Rose', 'CIVIL', 7.3);
INSERT INTO Students VALUES (119, 'Sam', 'ECE', 8.8);
INSERT INTO Students VALUES (120, 'Tina', 'CSBS', 9.1);

Step 3: Create a B-Tree Index on ROLL_NO
In Oracle, normal indexes are B-Tree indexes by default. They work best for equality lookups and range queries.
CREATE INDEX idx_rollno_btree ON Students(ROLL_NO);
SELECT * FROM Students WHERE ROLL_NO = 110;
Output → Jack, CSBS, 9.0

Step 4: Create a B+ Tree Index on CGPA
Oracle internally uses B-Trees, but when scanning ranges, they behave like B+ Trees.
CREATE INDEX idx_cgpa_bplus ON Students(CGPA);

SELECT * FROM Students WHERE CGPA > 8.0;
This retrieves all students who scored above 8.0 CGPA

Step 5: Create a Hash Index on DEPT
Oracle does not directly support USING HASH like PostgreSQL, but we can still create a normal index on the department column. This behaves like a hash lookup for equality searches.
CREATE INDEX idx_dept_hash ON Students(DEPT);
SELECT * FROM Students WHERE DEPT = 'CSBS';

Summary:
B-Tree Index → Great for ROLL_NO = 110 or ROLL_NO BETWEEN 101 AND 110.
B+ Tree Index → Works best for range queries (CGPA > 8.0).
Hash Index (simulated) → Best for equality checks (DEPT = 'CSBS').

THANK YOU @santhoshnc sir for guiding mee!!!