Indexes are the secret sauce behind fast database queries. In this post, we’ll create a Students table, populate it with sample data, and explore three powerful indexing strategies:
B-Tree Index
B+ Tree Index
Hash Index
Step 1: Create the Students Table
sql
CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(10),
cgpa DECIMAL(3,2)
);
Step 2: Insert Sample Records
sql
INSERT INTO Students VALUES (101, 'Aarav', 'CSBS', 8.5);
INSERT INTO Students VALUES (102, 'Meera', 'ECE', 7.8);
INSERT INTO Students VALUES (103, 'Ravi', 'MECH', 6.9);
INSERT INTO Students VALUES (104, 'Sneha', 'CSBS', 9.1);
INSERT INTO Students VALUES (105, 'Kiran', 'EEE', 7.2);
INSERT INTO Students VALUES (106, 'Divya', 'CSBS', 8.7);
INSERT INTO Students VALUES (107, 'Aditya', 'CIVIL', 6.5);
INSERT INTO Students VALUES (108, 'Neha', 'ECE', 8.0);
INSERT INTO Students VALUES (109, 'Rahul', 'MECH', 7.4);
INSERT INTO Students VALUES (110, 'Priya', 'CSBS', 9.3);
INSERT INTO Students VALUES (111, 'Varun', 'EEE', 7.6);
INSERT INTO Students VALUES (112, 'Isha', 'CIVIL', 8.2);
INSERT INTO Students VALUES (113, 'Manav', 'CSBS', 8.9);
INSERT INTO Students VALUES (114, 'Tanya', 'ECE', 7.0);
INSERT INTO Students VALUES (115, 'Rohit', 'MECH', 6.8);
INSERT INTO Students VALUES (116, 'Anjali', 'CSBS', 9.0);
INSERT INTO Students VALUES (117, 'Siddharth', 'EEE', 7.3);
INSERT INTO Students VALUES (118, 'Pooja', 'CIVIL', 8.1);
INSERT INTO Students VALUES (119, 'Nikhil', 'CSBS', 8.6);
INSERT INTO Students VALUES (120, 'Simran', 'ECE', 7.9);
Step 3: B-Tree Index on roll_no
B-Tree indexes are the default in most databases and great for range and equality queries.
sql
CREATE INDEX idx_rollno_btree ON Students(roll_no);
Query: Fetch Student with roll_no = 110
sql
SELECT * FROM Students WHERE roll_no = 110;
Step 4: B+ Tree Index on cgpa
While Oracle uses B-Tree by default, some systems like PostgreSQL simulate B+ Tree behavior. For educational purposes, we’ll treat this as a logical B+ Tree index.
sql
CREATE INDEX idx_cgpa_bplus ON Students(cgpa);
Students with cgpa > 8.0
sql
SELECT * FROM Students WHERE cgpa > 8.0
thank you @santhoshnc sir
Top comments (0)