DEV Community

Cover image for Indexing, Hashing & Query Optimization
Kamwemba Tinashe C
Kamwemba Tinashe C

Posted on

Indexing, Hashing & Query Optimization

#sql

Question to Indexing, Hashing & Query Optimization

Setup the Schema and Data

Create the Table:

CREATE TABLE Students (
    roll_no NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    dept VARCHAR2(50),
    cgpa NUMBER
);
Enter fullscreen mode Exit fullscreen mode

Insert at Least 20 Sample Records:

INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (101, 'Alice', 'CSBS', 8.5);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (102, 'Bob', 'ECE', 7.8);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (103, 'Charlie', 'CSBS', 9.0);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (104, 'David', 'MECH', 6.5);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (105, 'Eve', 'CSBS', 8.2);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (106, 'Frank', 'ECE', 7.9);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (107, 'Grace', 'CSBS', 8.7);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (108, 'Hank', 'MECH', 6.8);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (109, 'Ivy', 'CSBS', 9.1);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (110, 'Jack', 'ECE', 7.6);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (111, 'Kate', 'CSBS', 8.4);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (112, 'Leo', 'MECH', 6.9);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (113, 'Mia', 'CSBS', 8.9);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (114, 'Noah', 'ECE', 7.7);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (115, 'Olivia', 'CSBS', 8.3);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (116, 'Peter', 'MECH', 6.6);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (117, 'Quinn', 'CSBS', 9.2);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (118, 'Rose', 'ECE', 7.5);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (119, 'Sam', 'CSBS', 8.6);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (120, 'Tom', 'MECH', 6.7);
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Verify Data

SELECT * FROM Students;
Enter fullscreen mode Exit fullscreen mode

Table Of All Students

Task 1: Create a B-Tree Index on the roll_no Column

CREATE INDEX idx_roll_no ON Students(roll_no);
Enter fullscreen mode Exit fullscreen mode

Task 2: Execute a Query to Fetch Details of a Student with roll_no = 110

SELECT * FROM Students WHERE roll_no = 110;
Enter fullscreen mode Exit fullscreen mode

Output For Jack

Task 3: Create a B+ Tree Index on the cgpa Column

  • Oracle uses B-Tree indexes by default, and B+ Tree is a variant not explicitly created but functionally similar. Create a standard B-Tree index: 
CREATE INDEX idx_cgpa ON Students(cgpa);
Enter fullscreen mode Exit fullscreen mode

Task 4: Write a Query to Display All Students with cgpa > 8.0

SELECT * FROM Students WHERE cgpa > 8.0 ORDER BY cgpa;
Enter fullscreen mode Exit fullscreen mode

Students WHERE cgpa > 8.0

Task 5: Create a Hash Index on the dept Column

  • Oracle doesn’t support direct hash indexes, but you can simulate this with a function-based index using a hash function (e.g., STANDARD_HASH): 
CREATE INDEX idx_dept_hash ON Students(STANDARD_HASH(dept));
Enter fullscreen mode Exit fullscreen mode

Task 6: Run a Query to Retrieve All Students from the 'CSBS' Department

SELECT * FROM Students WHERE dept = 'CSBS' ORDER BY roll_no;
Enter fullscreen mode Exit fullscreen mode

Students WHERE dept = 'CSBS' ORDER BY roll_no

Top comments (0)