This content originally appeared on DEV Community and was authored by BHARANIKA D 24CB005
🚀 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
- B-Tree Index → best for primary keys, unique lookups.
- B+ Tree Index → efficient for range queries (e.g., CGPA > 8).
- Hash Cluster → simulates Hash Index in Oracle for exact match lookups.
By combining these, you can optimize queries and improve performance in Oracle SQL.
This content originally appeared on DEV Community and was authored by BHARANIKA D 24CB005
BHARANIKA D 24CB005 | Sciencx (2025-11-01T10:59:25+00:00) 🚀 Understanding Indexing in Oracle (B-Tree, B+ Tree, Hash Cluster) Indexes are one of the most important concepts in databases.. Retrieved from https://www.scien.cx/2025/11/01/%f0%9f%9a%80-understanding-indexing-in-oracle-b-tree-b-tree-hash-cluster-indexes-are-one-of-the-most-important-concepts-in-databases/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.