Indexing, Hashing & Query Optimization

Create a table Students

Create a table Students with fields (roll_no, name, dept, cgpa)

CREATE TABLE Students (
  roll_no INT PRIMARY KEY,
  name    VARCHAR2(50),
  dept    VARCHAR2(10),
  cgpa    NUMBER(3,2)
);

Insert at least 20 sample records.

BEGIN
  INSERT INTO Students VALUES (101, 'Aarav', 'CSBS', 8.5);
  INSERT INTO Students VALUES (102, 'Meera', 'ECE', 7.8);
  INSERT INTO Students VALUES (103, 'Ravi', 'MECH', 6.9);
  INSERT INTO Students VALUES (104, 'Lakshmi', 'CSBS', 9.1);
  INSERT INTO Students VALUES (105, 'Kiran', 'EEE', 7.5);
  INSERT INTO Students VALUES (106, 'Divya', 'CIVIL', 8.2);
  INSERT INTO Students VALUES (107, 'Vishal', 'CSBS', 8.9);
  INSERT INTO Students VALUES (108, 'Sneha', 'ECE', 7.2);
  INSERT INTO Students VALUES (109, 'Rahul', 'MECH', 6.8);
  INSERT INTO Students VALUES (110, 'Priya', 'CSBS', 9.3);
  INSERT INTO Students VALUES (111, 'Anjali', 'EEE', 7.4);
  INSERT INTO Students VALUES (112, 'Manoj', 'CIVIL', 8.0);
  INSERT INTO Students VALUES (113, 'Neha', 'CSBS', 8.7);
  INSERT INTO Students VALUES (114, 'Arjun', 'ECE', 7.6);
  INSERT INTO Students VALUES (115, 'Pooja', 'MECH', 6.5);
  INSERT INTO Students VALUES (116, 'Suresh', 'EEE', 7.9);
  INSERT INTO Students VALUES (117, 'Geeta', 'CIVIL', 8.3);
  INSERT INTO Students VALUES (118, 'Tarun', 'CSBS', 9.0);
  INSERT INTO Students VALUES (119, 'Bhavna', 'ECE', 7.1);
  INSERT INTO Students VALUES (120, 'Nikhil', 'MECH', 6.7);
  COMMIT;
END;

B-Tree index

Create a B-Tree index on the roll_no column of the Students table.

• Oracle automatically creates a unique B-Tree index on any column declared as PRIMARY KEY

fetch the details

Execute a query to fetch the details of a student with roll_no = 110.

SELECT * FROM Students WHERE roll_no = 110;

B+ Tree index

Create a B+ Tree index on the cgpa column of the Students table.

CREATE INDEX idx_cgpa ON Students(cgpa);

display

Write a query to display all students with cgpa > 8.0.

Hash index

Create a Hash index on the dept column of the Students table.

CREATE INDEX idx_dept_hash ON Students(UPPER(dept));

display

Run a query to retrieve all students from the 'CSBS' department.

SELECT * FROM Students WHERE UPPER(dept) = 'CSBS';