🚀 Understanding Indexing in Oracle (B-Tree, B+ Tree, Hash Cluster)

Indexes are one of the most important concepts in databases. They speed up queries, reduce table scans, and optimize performance. In this post, we’ll explore B-Tree, B+ Tree, and Hash Indexing (via Hash Cluster) in Oracle using Students table.

📌 Step 1: Create the Students Table

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

📌 Step 2: Insert 20 Sample Records

INSERT INTO Students VALUES (101, 'Alice', 'CSBS', 9.1);
INSERT INTO Students VALUES (102, 'Bob', 'IT', 7.5);
INSERT INTO Students VALUES (103, 'Charlie', 'CSE', 8.6);
INSERT INTO Students VALUES (104, 'David', 'ECE', 7.9);
INSERT INTO Students VALUES (105, 'Eva', 'CSBS', 8.3);
INSERT INTO Students VALUES (106, 'Frank', 'MECH', 6.5);
INSERT INTO Students VALUES (107, 'Grace', 'EEE', 8.8);
INSERT INTO Students VALUES (108, 'Helen', 'CIVIL', 7.0);
INSERT INTO Students VALUES (109, 'Ivy', 'CSE', 8.9);
INSERT INTO Students VALUES (110, 'Jack', 'CSBS', 9.0);
INSERT INTO Students VALUES (111, 'Karan', 'IT', 7.8);
INSERT INTO Students VALUES (112, 'Leo', 'CSE', 8.1);
INSERT INTO Students VALUES (113, 'Maya', 'ECE', 8.5);
INSERT INTO Students VALUES (114, 'Nina', 'MECH', 6.8);
INSERT INTO Students VALUES (115, 'Oscar', 'EEE', 8.7);
INSERT INTO Students VALUES (116, 'Paul', 'CIVIL', 7.2);
INSERT INTO Students VALUES (117, 'Queen', 'CSBS', 9.3);
INSERT INTO Students VALUES (118, 'Raj', 'CSE', 8.0);
INSERT INTO Students VALUES (119, 'Sara', 'IT', 7.6);
INSERT INTO Students VALUES (120, 'Tom', 'CSBS', 9.2);

📌 Step 3: B-Tree Index (on Roll Number)

Oracle’s default index type is a B-Tree index.

CREATE INDEX idx_rollno ON Students(roll_no);

Now query with the index:

SELECT * FROM Students WHERE roll_no = 110;

📌 Step 4: B+ Tree Index (on CGPA)

B+ Trees are used internally by Oracle when you create a normal index on numeric columns.

CREATE INDEX idx_cgpa ON Students(cgpa);

SELECT * FROM Students WHERE cgpa > 8.0;

📌 Step 5: Hash Index via Hash Cluster (on Department)
Oracle does not allow USING HASH in CREATE INDEX.
Instead, we use a Hash Cluster.

CREATE CLUSTER student_cluster (dept VARCHAR2(10))
SIZE 512
HASHKEYS 20;

CREATE TABLE Students_Hash (
    roll_no INT PRIMARY KEY,
    name VARCHAR2(50),
    dept VARCHAR2(10),
    cgpa NUMBER(3,2)
) CLUSTER student_cluster(dept);

INSERT INTO Students_Hash VALUES (101, 'Alice', 'CSBS', 9.1);
INSERT INTO Students_Hash VALUES (102, 'Bob', 'IT', 7.5);
INSERT INTO Students_Hash VALUES (103, 'Charlie', 'CSE', 8.6);
INSERT INTO Students_Hash VALUES (104, 'David', 'ECE', 7.9);
INSERT INTO Students_Hash VALUES (105, 'Eva', 'CSBS', 8.3);
INSERT INTO Students_Hash VALUES (106, 'Frank', 'MECH', 6.5);
INSERT INTO Students_Hash VALUES (107, 'Grace', 'EEE', 8.8);
INSERT INTO Students_Hash VALUES (108, 'Helen', 'CIVIL', 7.0);
INSERT INTO Students_Hash VALUES (109, 'Ivy', 'CSE', 8.9);
INSERT INTO Students_Hash VALUES (110, 'Jack', 'CSBS', 9.0);
INSERT INTO Students_Hash VALUES (111, 'Karan', 'IT', 7.8);
INSERT INTO Students_Hash VALUES (112, 'Leo', 'CSE', 8.1);
INSERT INTO Students_Hash VALUES (113, 'Maya', 'ECE', 8.5);
INSERT INTO Students_Hash VALUES (114, 'Nina', 'MECH', 6.8);
INSERT INTO Students_Hash VALUES (115, 'Oscar', 'EEE', 8.7);
INSERT INTO Students_Hash VALUES (116, 'Paul', 'CIVIL', 7.2);
INSERT INTO Students_Hash VALUES (117, 'Queen', 'CSBS', 9.3);
INSERT INTO Students_Hash VALUES (118, 'Raj', 'CSE', 8.0);
INSERT INTO Students_Hash VALUES (119, 'Sara', 'IT', 7.6);
INSERT INTO Students_Hash VALUES (120, 'Tom', 'CSBS', 9.2);

Query with Hash Access

SELECT * FROM Students_Hash WHERE dept = 'CSBS';

🔥 Conclusion

1. B-Tree Index → best for primary keys, unique lookups.
2. B+ Tree Index → efficient for range queries (e.g., CGPA > 8).
3. Hash Cluster → simulates Hash Index in Oracle for exact match lookups.

By combining these, you can optimize queries and improve performance in Oracle SQL.