Definiton:
Indexing: Indexing is a data structure technique used in databases to quickly locate and access the data in a table without having to search every row.
Hash Index: A hash index uses a hash function to convert a search key into a hash value, which points directly to the location of the data record.
B+ Tree Index: A B+ tree index is a balanced tree data structure where all data records are stored at the leaf nodes, and internal nodes only store keys for navigation.
B- Tree Index: A B-tree index is a balanced tree where keys and data pointers can appear in both internal and leaf nodes.
Query Optimization: Query optimization is the process of choosing the most efficient way to execute a database query.
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';
Conclusion:
Indexing is a crucial technique in databases that enhances the speed and efficiency of data retrieval. Different types of indexes—such as Hash Index, B-Tree Index, and B+ Tree Index—serve different purposes depending on the query type.
Hash Indexes are best for exact match queries.
B-Tree and B+ Tree Indexes are ideal for both range and equality searches, with B+ Trees being the most widely used in modern databases due to their efficiency in sequential access.
Finally, Query Optimization ensures that the database executes queries in the most efficient way possible, making full use of indexes and other optimization strategies. Together, indexing and query optimization greatly improve database performance and responsiveness.
Thanks to @santhoshnc Sir for guiding me through indexing and query optimization concepts.
SQL #Oracle #Indexing #BTree #BPlusTree #QueryOptimization #DBMS #Database
Top comments (0)