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)
);
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);
4. Creating a B-Tree Index on roll_no
CREATE INDEX idx_roll_no ON Students(roll_no);
Query: Fetch student with roll_no = 110
SELECT * FROM Students WHERE roll_no = 110;
5. Creating a B+ Tree Index on cgpa
Oracle automatically uses B+ Tree for numeric indexes.
CREATE INDEX idx_cgpa ON Students(cgpa);
Query: Display all students with cgpa > 8.0
SELECT * FROM Students WHERE cgpa > 8.0;
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);
Query: Retrieve all students from the CSBS department
SELECT * FROM Students WHERE dept = 'CSBS';
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.
Top comments (0)