Indexing, Hashing & Query Optimization in DBMS

We will focus on enhancing query performance in Oracle SQL by using indexes. We’ll create a Students table, insert sample records, and run optimized queries utilizing B-Tree and B+ Tree indexes. Definition: Indexing: A database index is a data structure that enhances the speed of data retrieval operations on a table, though it comes with additional costs in terms of writes and storage space. Hash Index: Utilizes a hash function to enable rapid equality searches. (Note: In Oracle, true hash indexes are not directly supported; hash-like performance can be achieved through specific functions or clustering techniques.) B+ Tree Index: An extension of the B-Tree where all values are stored at the leaf nodes, making it more efficient for range queries. B-Tree Index: A balanced tree-based index commonly used for equality and range queries. Query Optimization: The process of minimizing query execution time by leveraging indexes and writing efficient SQL statements. Creating the Students Table CREATE TABLE Students ( roll_no INT PRIMARY KEY, name VARCHAR2(50), dept VARCHAR2(20), cgpa NUMBER(3,2) ); 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); 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; 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; 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'; Steps Summary:- Defined the Students table with columns: roll_no, name, dept, and cgpa. Added 20 sample records into the table. Built a B-Tree index on roll_no to enable faster lookups. Created a B+ Tree index on cgpa to improve range query performance. Indexed the dept column to accelerate equality-based searches. Executed queries to test and confirm indexing benefits in performance. Conclusion:- By using indexes, query execution becomes faster and more efficient. Indexing is a vital part of query optimization in DBMS. Thanks to @santhoshnc Sir for guiding me through indexing and query optimization concepts. SQL #Oracle #Indexing #BTree #BPlusTree #QueryOptimization #DBMS #Database