DEV Community

SANCHAYAA S 24CB052
SANCHAYAA S 24CB052

Posted on

⚡ Mastering Indexing, Hashing & Query Optimization in SQL

🎯 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)