DEV Community

Cover image for Indexing, Hashing & Query Optimization
Gangeswara
Gangeswara

Posted on

Indexing, Hashing & Query Optimization

In this assignment, we’ll explore how to improve query performance in Oracle SQL using indexes. We’ll create a Students table, insert sample data, and perform optimized queries using B-Tree and B+ Tree indexes.

1. Definition

Indexing: A database index is a data structure that improves the speed of data retrieval operations on a table at the cost of additional writes and storage space.

B-Tree Index: A balanced tree index used for equality and range searches.

B+ Tree Index: A type of B-Tree that stores all values at leaf nodes, optimized for range queries.

Hash Index: Uses a hash function for fast equality searches. (Note: Oracle only supports hash-like behavior through specific functions or clustering.)

Query Optimization: Using indexes and proper SQL structures to reduce query execution time.

2. Creating the Students Table

CREATE TABLE Students (
    roll_no INT PRIMARY KEY,
    name VARCHAR2(50),
    dept VARCHAR2(20),
    cgpa NUMBER(3,2)
);
Enter fullscreen mode Exit fullscreen mode

3. Inserting Sample Records

INSERT INTO Students VALUES (101, 'Arjun', 'CSBS', 8.5);
INSERT INTO Students VALUES (102, 'Priya', 'CSBS', 7.8);
INSERT INTO Students VALUES (103, 'Kiran', 'ECE', 9.0);
INSERT INTO Students VALUES (104, 'Anita', 'ME', 8.2);
INSERT INTO Students VALUES (105, 'Vikram', 'CSBS', 8.8);
INSERT INTO Students VALUES (106, 'Ravi', 'ECE', 7.5);
INSERT INTO Students VALUES (107, 'Sneha', 'ME', 8.7);
INSERT INTO Students VALUES (108, 'Nikhil', 'CSBS', 6.9);
INSERT INTO Students VALUES (109, 'Maya', 'ECE', 8.0);
INSERT INTO Students VALUES (110, 'Aditya', 'CSBS', 9.2);
INSERT INTO Students VALUES (111, 'Tanya', 'ME', 7.9);
INSERT INTO Students VALUES (112, 'Rohan', 'CSBS', 8.3);
INSERT INTO Students VALUES (113, 'Divya', 'ECE', 9.1);
INSERT INTO Students VALUES (114, 'Karthik', 'ME', 7.7);
INSERT INTO Students VALUES (115, 'Isha', 'CSBS', 8.6);
INSERT INTO Students VALUES (116, 'Suresh', 'ECE', 8.4);
INSERT INTO Students VALUES (117, 'Meena', 'ME', 8.0);
INSERT INTO Students VALUES (118, 'Aravind', 'CSBS', 7.6);
INSERT INTO Students VALUES (119, 'Pooja', 'ECE', 8.9);
INSERT INTO Students VALUES (120, 'Rahul', 'ME', 8.1);

Enter fullscreen mode Exit fullscreen mode

4. Creating a B-Tree Index on roll_no

CREATE INDEX idx_roll_no ON Students(roll_no);
Enter fullscreen mode Exit fullscreen mode


Query: Fetch student with roll_no = 110

SELECT * FROM Students WHERE roll_no = 110;
Enter fullscreen mode Exit fullscreen mode

5. Creating a B+ Tree Index on cgpa

Oracle automatically uses B+ Tree for numeric indexes.

CREATE INDEX idx_cgpa ON Students(cgpa);

Enter fullscreen mode Exit fullscreen mode


Query: Display all students with cgpa > 8.0

SELECT * FROM Students WHERE cgpa > 8.0;

Enter fullscreen mode Exit fullscreen mode

6. Creating an Index on dept for Fast Equality Search

Oracle does not support direct hash indexes for normal tables. Instead, we use a regular index:

CREATE INDEX idx_dept ON Students(dept);

Enter fullscreen mode Exit fullscreen mode


Query: Retrieve all students from the CSBS department

SELECT * FROM Students WHERE dept = 'CSBS';


Enter fullscreen mode Exit fullscreen mode

7. Steps Summary

Created the Students table with fields roll_no, name, dept, cgpa.

Inserted 20 sample records.

Created B-Tree index on roll_no for quick lookups.

Created B+ Tree index on cgpa for optimized range queries.

Created index on dept to speed up equality searches.

Executed queries to verify indexing and performance improvements.

8. Conclusion

By using indexes, query execution becomes faster and more efficient. Indexing is a vital part of query optimization in DBMS.

Special thanks to @santhoshnc Sir for guiding me through indexing and query optimization concepts.

SQL #Oracle #Indexing #BTree #BPlusTree #QueryOptimization #DBMS #Database

Top comments (0)