🎯 Objective:
To gain hands-on experience with Indexing, Hashing, and Query Optimization techniques using Oracle SQL.
You’ll learn how different index types (B-Tree, B+ Tree, Hash) improve query performance.
🏗 Step 1: Create the Students Table
Let’s start with our base schema for the lab.
-- Drop old table if exists
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE Students';
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
-- Create Students table
CREATE TABLE Students (
roll_no NUMBER PRIMARY KEY,
name VARCHAR2(50),
dept VARCHAR2(10),
cgpa NUMBER(3,2)
);
📥 Step 2: Insert Sample Data
INSERT INTO Students VALUES (101, 'Alice', 'CSBS', 8.5);
INSERT INTO Students VALUES (102, 'Bob', 'ECE', 7.8);
INSERT INTO Students VALUES (103, 'Charlie', 'ME', 8.2);
INSERT INTO Students VALUES (104, 'David', 'CSBS', 9.0);
INSERT INTO Students VALUES (105, 'Eva', 'ECE', 7.5);
INSERT INTO Students VALUES (106, 'Frank', 'ME', 8.1);
INSERT INTO Students VALUES (107, 'Grace', 'CSBS', 9.2);
INSERT INTO Students VALUES (108, 'Hannah', 'ECE', 6.9);
INSERT INTO Students VALUES (109, 'Ian', 'ME', 7.8);
INSERT INTO Students VALUES (110, 'Jack', 'CSBS', 8.8);
INSERT INTO Students VALUES (111, 'Karen', 'ECE', 8.0);
INSERT INTO Students VALUES (112, 'Leo', 'ME', 7.9);
INSERT INTO Students VALUES (113, 'Mona', 'CSBS', 8.7);
INSERT INTO Students VALUES (114, 'Nina', 'ECE', 8.3);
INSERT INTO Students VALUES (115, 'Oscar', 'ME', 7.6);
INSERT INTO Students VALUES (116, 'Paul', 'CSBS', 9.1);
INSERT INTO Students VALUES (117, 'Quinn', 'ECE', 8.4);
INSERT INTO Students VALUES (118, 'Rachel', 'ME', 7.7);
INSERT INTO Students VALUES (119, 'Steve', 'CSBS', 8.9);
INSERT INTO Students VALUES (120, 'Tina', 'ECE', 8.2);
COMMIT;
✅ Output:
20 student records successfully inserted.
🌳 Step 3: Creating a B-Tree Index
A B-Tree index helps optimize exact match and range queries.
In Oracle, the default index type is B-Tree.
CREATE INDEX idx_roll_no ON Students(roll_no);
Query using the B-Tree index:
SELECT * FROM Students WHERE roll_no = 110;
💬 Result:
Query runs faster — Oracle uses the idx_roll_no index instead of scanning the whole table.
🌲 Step 4: Creating a B+ Tree Index (on CGPA)
B+ Trees are used internally by Oracle for normal indexes.
They’re ideal for range-based queries (e.g., >, <, BETWEEN).
CREATE INDEX idx_cgpa ON Students(cgpa);
Query using B+ Tree index:
SELECT * FROM Students WHERE cgpa > 8.0 ORDER BY cgpa DESC;
✅ Why it’s faster:
Oracle reads index pages sequentially, avoiding full table scans.
Perfect for range conditions and sorting queries.
💠 Step 5: Simulating a Hash Index (on dept)
Oracle doesn’t allow direct USING HASH indexes on regular tables,
but we can use normal indexing to simulate hash-based performance for exact matches.
CREATE INDEX idx_dept ON Students(dept);
Query example:
SELECT * FROM Students WHERE dept = 'CSBS';
💬 Explanation:
Hashing is best for equality lookups (like dept = 'CSBS').
B-Tree is still used internally but acts similarly for single-value queries.
🔍 Step 6: Query Optimization with EXPLAIN PLAN
Let’s verify that Oracle uses our indexes.
EXPLAIN PLAN FOR
SELECT * FROM Students WHERE roll_no = 110;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
These confirm that your query is optimized using indexes.
📊 Step 7: Understanding Index Types
Index Type Use Case Example Query Advantage
B-Tree Equality or range lookup
WHERE roll_no = 110 Balanced tree → fast lookup
B+ Tree Range or sorting queries
WHERE cgpa > 8 Optimized for sequential access
Top comments (0)