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.
Top comments (0)